Thread: Potential bug in ALTER TABLE?

Hi,

just want to verify first with you guys before dumping it on the bugs
list.  Most likely I am just being silly here or something.

Take this:

create table blah (name TEXT CHECK (name IN ('blah', 'bleh')));
test=# \d blah   Table "public.blah"Column | Type | Modifiers 
--------+------+-----------name   | text | 
Check constraints: "blah_name" ((name = 'blah'::text) OR (name = 'bleh'::text))

As we would expect PostgreSQL to do.  The constraint has an
automatically assigned name.

Now, to continue:

ALTER TABLE blah DROP CONSTRAINT blah_name;
ALTER TABLE blah ADD CHECK (name IN ('blah', 'bleh'));
test=# \d blah   Table "public.blah"Column | Type | Modifiers 
--------+------+-----------name   | text | 
Check constraints: "$1" ((name = 'blah'::text) OR (name = 'bleh'::text))

And this time around PostgreSQL doesn't assign an automatic name.
Well, it depends on what you call a name, but $1, $2, and so on isn't
quite descriptive.  Is this an oversight or am I missing some subtle
thing here?

-- 
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7  9D88 97E6 839B 2EAC 625B
http://www.tendra.org/   | http://www.in-nomine.org/~asmodai/diary/
Happiness is the absence of the striving for happiness...

Jeroen Ruigrok/asmodai <asmodai@wxs.nl> writes:
> just want to verify first with you guys before dumping it on the bugs
> list.  Most likely I am just being silly here or something.

The ALTER ADD CONSTRAINT form creates a table constraint, ie, one that's
not attached to any particular column.  If you write the constraint in
the CREATE TABLE as a table constraint, then you get the same result as
with ALTER ADD CONSTRAINT.

regression=# create table blah (name TEXT, CHECK (name IN ('blah', 'bleh')));
CREATE TABLE
regression=# \d blah   Table "public.blah"Column | Type | Modifiers
--------+------+-----------name   | text |
Check constraints:   "$1" CHECK ((name = 'blah'::text) OR (name = 'bleh'::text))


If you don't like the automatically generated name, assign your own...

regression=# ALTER TABLE blah ADD CONSTRAINT fooey CHECK (name IN ('blah', 'bleh'));
ALTER TABLE
regression=# \d blah   Table "public.blah"Column | Type | Modifiers
--------+------+-----------name   | text |
Check constraints:   "$1" CHECK ((name = 'blah'::text) OR (name = 'bleh'::text))   "fooey" CHECK ((name = 'blah'::text)
OR(name = 'bleh'::text))
 

        regards, tom lane

Jeroen Ruigrok/asmodai wrote:

>Hi,
>
>just want to verify first with you guys before dumping it on the bugs
>list.  Most likely I am just being silly here or something.
>
>Take this:
>
>create table blah (name TEXT CHECK (name IN ('blah', 'bleh')));
>test=# \d blah
>    Table "public.blah"
> Column | Type | Modifiers 
>--------+------+-----------
> name   | text | 
>Check constraints: "blah_name" ((name = 'blah'::text) OR (name = 'bleh'::text))
>
>As we would expect PostgreSQL to do.  The constraint has an
>automatically assigned name.
>
>Now, to continue:
>
>ALTER TABLE blah DROP CONSTRAINT blah_name;
>ALTER TABLE blah ADD CHECK (name IN ('blah', 'bleh'));
>test=# \d blah
>    Table "public.blah"
> Column | Type | Modifiers 
>--------+------+-----------
> name   | text | 
>Check constraints: "$1" ((name = 'blah'::text) OR (name = 'bleh'::text))
>
>And this time around PostgreSQL doesn't assign an automatic name.
>Well, it depends on what you call a name, but $1, $2, and so on isn't
>quite descriptive.  Is this an oversight or am I missing some subtle
>thing here?
>
>  
>
You can name it yourself:
 ALTER TABLE blah ADD CONSTRAINT blurfl CHECK (name IN ('blah', 'bleh'));

I do this a lot.

I agree the autogenerated names are less than pretty.

cheers

andrew