Thread: "Named" column default expression

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
namein 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
thereis a way to retrieve that information once the table is created. 

Thanks
Thomas

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

Thom Brown, 28.10.2011 10:10:
> 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.

Thanks, makes somewhat sense.

I'm wondering why this doesn't throw an error then.

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
>>> I just noticed that Postgres allows the following syntax:
>>> create table foo
>>> (
>>> id integer constraint id_default_value default 42
>>> );

> I'm wondering why this doesn't throw an error then.

It's an implementation artifact --- our grammar regards everything after
a column's type name as a list of column constraints.  So "DEFAULT foo"
has to be considered as one variant of column constraint.  We could
probably tweak the grammar enough so it didn't allow "CONSTRAINT name"
to be prefixed to that one case, but there seems little point in adding
complexity for that.  The most it would accomplish is to break
applications that are expecting this particular deviation from spec to
work.

            regards, tom lane

Tom Lane wrote on 28.10.2011 16:21:
>>>> I just noticed that Postgres allows the following syntax:
>>>> create table foo
>>>> (
>>>> id integer constraint id_default_value default 42
>>>> );
>
>> I'm wondering why this doesn't throw an error then.
>
> It's an implementation artifact --- our grammar regards everything after
> a column's type name as a list of column constraints.  So "DEFAULT foo"
> has to be considered as one variant of column constraint.  We could
> probably tweak the grammar enough so it didn't allow "CONSTRAINT name"
> to be prefixed to that one case, but there seems little point in adding
> complexity for that.  The most it would accomplish is to break
> applications that are expecting this particular deviation from spec to
> work.

Thanks for the explanation.

I have seen the syntax on SQL Server and there it does give the default definition a name. I was somewhat surprised
aboutthe syntax as I have never considered a default to be a constraint. So I checked the PostgreSQL manual just to
findthat the same syntax works there as well.... 

Regards
Thomas