Re: BUG #5258: Unique and foreign key constraints fail on columns with reserved names, but not check constraints

On 2010-01-04, Ben Woosley <ben.woosley@gmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5258
> Logged by:          Ben Woosley
> Email address:      ben.woosley@gmail.com
> PostgreSQL version: 8.4.2
> Operating system:   Mac OS 10.6
> Description:        Unique and foreign key constraints fail on columns with
> reserved names, but not check constraints
> Details:
>
> Check constraints successfully accept columns named with reserved words when
> they are qualified by table using the . syntax, e.g. "check (mod(table.as,
> 2) = 0)"
>
> However, unique and foreign key constraints added using the "alter table add
> constraint" syntax fail on the column name.  At this point the statement has
> enough information (the host table name) to properly identify the column
> despite the unorthodox name.  Alternatively, you could allow the . syntax
> qualification inside the argument to the constraint.
>
> Now, you may say that this is a reserved word and should never be used, but
> coming from the Ruby world, where reserved words are only reserved when
> they're truly ambiguous, I very much appreciate the freedom of using these
> names when it's unambiguous.  This particularly so given that keywords are
> often chosen for their terseness and overlap with the most appropriate
> column name.

have you tried quoting the infringing identifier.

create table ben ( "as" integer );
alter table ben add constraint ben_ident_bug unique("as");

http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS