Thread: CREATE TABLE LIKE, regarding constraints
www.postgresql.org/docs/9.0/static/sql-createtable.html == == == == == LIKE parent_table [ like_option ... ] . . . Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. . . . == == == == == But I do see PK and UNIQUE constraints CONSTRAINT blah_pkey PRIMARY KEY (id), CONSTRAINT blah_host_id_key UNIQUE (host_id) in the def of the new table. Also, why is there no discussion of what "EXCLUDING CONSTRAINTS" will result in ? Thanks, -dvs-
On Fri, Dec 30, 2011 at 22:27, <david.sahagian@emc.com> wrote: > www.postgresql.org/docs/9.0/static/sql-createtable.html > == == == == == > LIKE parent_table [ like_option ... ] > . . . > Not-null constraints are always copied to the new table. > CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. > . . . > == == == == == > > But I do see PK and UNIQUE constraints > CONSTRAINT blah_pkey PRIMARY KEY (id), > CONSTRAINT blah_host_id_key UNIQUE (host_id) > in the def of the new table. Can you provide the commands you ran to make that happen? It doesn't happen for me in a trivial test. > Also, why is there no discussion of what "EXCLUDING CONSTRAINTS" will result in ? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
-----Original Message----- From: Magnus Hagander [mailto:magnus@hagander.net] Sent: Saturday, December 31, 2011 7:42 AM To: Sahagian, David Cc: pgsql-docs@postgresql.org Subject: Re: [DOCS] CREATE TABLE LIKE, regarding constraints On Fri, Dec 30, 2011 at 22:27, <david.sahagian@emc.com> wrote: > www.postgresql.org/docs/9.0/static/sql-createtable.html > == == == == == > LIKE parent_table [ like_option ... ] > . . . > Not-null constraints are always copied to the new table. > CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. > . . . > == == == == == > > But I do see PK and UNIQUE constraints > CONSTRAINT blah_pkey PRIMARY KEY (id), > CONSTRAINT blah_host_id_key UNIQUE (host_id) > in the def of the new table. Can you provide the commands you ran to make that happen? It doesn't happen for me in a trivial test. > Also, why is there no discussion of what "EXCLUDING CONSTRAINTS" will result in ? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ Magnus, I did some more "testing" of CREATE TABLE LIKE, and now see that [INCLUDING INDEXES] also can cause PRIMARY KEY and UNIQUE constraints to become part of the new table. ++++++++++++++ CREATE TABLE yesConstr_noIndex_tbl ( like mytbl INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE ); ++++++++++++++ no PRIMARY KEY no UNIQUE no indexes ++++++++++++++ CREATE TABLE noConstr_yesIndex_tbl ( like mytbl INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING STORAGE ); ++++++++++++++ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mytbl_pkey" for table "mytbl" NOTICE: CREATE TABLE / UNIQUE will create implicit index "mytbl_host_id_key" for table "mytbl" CONSTRAINT mytbl_pkey PRIMARY KEY (id), CONSTRAINT mytbl_host_id_key UNIQUE (host_id) and 2 unrelated indexes: (a_diff_col) (yet_a_diff_col) I have no problem with this behavior, but the doc probably deserves some clarification on the "relationship" between [INCLUDING CONSTRAINTS] and [INCLUDING INDEXES]. Thanks, -dvs-
On Mon, Jan 2, 2012 at 15:32, <david.sahagian@emc.com> wrote: > On Fri, Dec 30, 2011 at 22:27, <david.sahagian@emc.com> wrote: >> www.postgresql.org/docs/9.0/static/sql-createtable.html >> == == == == == >> LIKE parent_table [ like_option ... ] >> . . . >> Not-null constraints are always copied to the new table. >> CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never becopied. >> . . . >> == == == == == >> >> But I do see PK and UNIQUE constraints >> CONSTRAINT blah_pkey PRIMARY KEY (id), >> CONSTRAINT blah_host_id_key UNIQUE (host_id) >> in the def of the new table. > > Can you provide the commands you ran to make that happen? It doesn't > happen for me in a trivial test. > >> Also, why is there no discussion of what "EXCLUDING CONSTRAINTS" will result in ? > > > Magnus, > I did some more "testing" of CREATE TABLE LIKE, > and now see that [INCLUDING INDEXES] also can cause PRIMARY KEY and UNIQUE constraints to become part of the new table. Ah, that explains why I couldn't reproduce it. > I have no problem with this behavior, > but the doc probably deserves some clarification on the "relationship" between > [INCLUDING CONSTRAINTS] and [INCLUDING INDEXES]. That might be a good idea, yes. Feel like cooking up a patch? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Mon, Jan 2, 2012 at 08:31:43PM +0100, Magnus Hagander wrote: > On Mon, Jan 2, 2012 at 15:32, <david.sahagian@emc.com> wrote: > > On Fri, Dec 30, 2011 at 22:27, <david.sahagian@emc.com> wrote: > >> www.postgresql.org/docs/9.0/static/sql-createtable.html > >> == == == == == > >> LIKE parent_table [ like_option ... ] > >> . . . > >> Not-null constraints are always copied to the new table. > >> CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never becopied. > >> . . . > >> == == == == == > >> > >> But I do see PK and UNIQUE constraints > >> CONSTRAINT blah_pkey PRIMARY KEY (id), > >> CONSTRAINT blah_host_id_key UNIQUE (host_id) > >> in the def of the new table. > > > > Can you provide the commands you ran to make that happen? It doesn't > > happen for me in a trivial test. > > > >> Also, why is there no discussion of what "EXCLUDING CONSTRAINTS" will result in ? > > > > > > Magnus, > > I did some more "testing" of CREATE TABLE LIKE, > > and now see that [INCLUDING INDEXES] also can cause PRIMARY KEY and UNIQUE constraints to become part of the new table. > > Ah, that explains why I couldn't reproduce it. > > > > I have no problem with this behavior, > > but the doc probably deserves some clarification on the "relationship" between > > [INCLUDING CONSTRAINTS] and [INCLUDING INDEXES]. > > That might be a good idea, yes. Feel like cooking up a patch? I have applied the attached patch based on this report. It is confusing that INCLUDING CONSTRAINTS only copies CHECK constraints, and INCLUDING INDEXES is required for PRIMARY KEY and UNIQUE constraints. Is there a reason our logic is so odd here? The SQL standard? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +