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

Ben Woosley <ben(dot)woosley(at)gmail(dot)com> writes:
> These work:
> alter table books add constraint books_from_within check (books.from >= 5
> and books.from < 11);
> alter table books add constraint books_as_whitelist check (books.as in
> ('whitelisted1','whitelisted2','whitelisted3'));

> These don't:
> alter table books add constraint books_from_reference foreign key (from)
> references authors (id) ;
> alter table books add constraint books_as_unique unique (as);

The point is that in a qualified name, we allow ColLabel for the second
and subsequent names, whereas the first name (or only name for an
unqualified name) has to be ColId. The odds that we are going to relax
that in general are not distinguishable from zero --- it's already as
loose as we can practically make it.

Ben is correct that we could probably allow an unqualified ColLabel in
the restricted context of foreign key or unique constraint column name
lists, but I don't really see the point. There would still be other
contexts where you'd have to double-quote the name in order to reference
it without qualification. And as Robert points out, doing that could
come back to haunt us later if we ever wanted to allow any other syntax
in that area. (This is not something that's totally under our control,
either; the SQL committee owns that syntax not us.)

The long and the short of it is: use double quotes if you want to use a
reserved word as a column name.

regards, tom lane