From: | Ben Woosley <ben(dot)woosley(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5258: Unique and foreign key constraints fail on columns with reserved names, but not check constraints |
Date: | 2010-01-04 18:38:54 |
Message-ID: | 627a64321001041038p5dc79fe3pa8d17fd3182034b3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-01-04 19:28:29 | Re: BUG #5261: Invalide page header |
Previous Message | Tom Lane | 2010-01-04 18:30:50 | Re: BUG #5258: Unique and foreign key constraints fail on columns with reserved names, but not check constraints |