Subject: CREATE TABLE LIKE, regarding constraints

From: <[email-masked]>
Date: Fri, 30 Dec 2011 16:27:21 -0500

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-

---

From: Magnus Hagander <[email-masked]>
Date: Sat, 31 Dec 2011 13:42:12 +0100

> www.postgresql.org/docs/9.0/static/sql-createtable.html

Can you provide the commands you ran to make that happen? It doesn't
happen for me in a trivial test.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

---

From: <[email-masked]>
Date: Mon, 2 Jan 2012 09:32:39 -0500

-----Original Message-----
From: Magnus Hagander [mailto:[email-masked]] 
Sent: Saturday, December 31, 2011 7:42 AM
To: Sahagian, David
Cc: [email-masked]
Subject: Re: [DOCS] CREATE TABLE LIKE, regarding constraints

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-

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 2 Jan 2012 20:31:43 +0100

> 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.

> [INCLUDING CONSTRAINTS] and [INCLUDING INDEXES].

That might be a good idea, yes. Feel like cooking up a patch?

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 26 Aug 2012 16:35:19 -0400

> 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  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
new file mode 100644
index 19e6f8e..445ca40
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 336,351 ****
       </para>
       <para>
        Not-null constraints are always copied to the new table.
!       <literal>CHECK</literal> constraints will only be copied if
!       <literal>INCLUDING CONSTRAINTS</literal> is specified; other types of
!       constraints will never be copied. Also, no distinction is made between
!       column constraints and table constraints &mdash; when constraints are
!       requested, all check constraints are copied.
!      </para>
!      <para>
!       Any indexes on the original table will not be created on the new
!       table, unless the <literal>INCLUDING INDEXES</literal> clause is
!       specified.
       </para>
       <para><literal>STORAGE</> settings for the copied column definitions will only
        be copied if <literal>INCLUDING STORAGE</literal> is specified.  The
--- 336,348 ----
       </para>
       <para>
        Not-null constraints are always copied to the new table.
!       <literal>CHECK</literal> constraints will be copied only if
!       <literal>INCLUDING CONSTRAINTS</literal> is specified. 
!       Indexes, <literal>PRIMARY KEY</>, and <literal>UNIQUE</> constraints
!       on the original table will be created on the new table only if the
!       <literal>INCLUDING INDEXES</literal> clause is specified.
!       No distinction is made between column constraints and table
!       constraints.
       </para>
       <para><literal>STORAGE</> settings for the copied column definitions will only
        be copied if <literal>INCLUDING STORAGE</literal> is specified.  The