Обсуждение: "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