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(dot)woosley(at)gmail(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5258
> Logged by: Ben Woosley
> Email address: ben(dot)woosley(at)gmail(dot)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