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

Thanks Tom,

I hadn't tried double-quoting, which worked fine. I'm only so well
acquainted with SQL and had only tried single quotes and backticks.

So, there is a way, and I'm satisfied with it. "Bug" retracted - thanks!

-Ben

On Mon, Jan 4, 2010 at 1:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>