Re: "Named" column default expression

On 28 October 2011 08:29, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Hello,
>
> I just noticed that Postgres allows the following syntax:
>
> create table foo
> (
>    id integer constraint id_default_value default 42
> );
>
> But as far as I can tell the "constraint id_default_value" part seems to be
> only syntactical sugar as this is stored nowhere. At least I couldn't find
> it going through the catalog tables and neither pg_dump -s or pgAdmin are
> showing that name in the generated SQL source for the table.
>
> It's not important, I'm just curious why the syntax is accepted (I never saw
> a default value as a constraint) and if there is a way to retrieve that
> information once the table is created.

It would do something with it if you actually defined a constraint
after it, but since you didn't, it throws it away since there's
nothing to enforce.  So if you adjust it to:

create table foo
(
   id integer constraint id_default_value check (id > 4) default 42
);

a constraint for that column will be created with the specified name.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company