Subject: Create Table ... Like Including Default & Sequences

From: David Johnston <[email-masked]>
Date: Thu, 13 Jun 2013 19:20:05 -0700 (PDT)

In the documentation for "Create Table" the following paragraph appears under
the definition for LIKE:

"Default expressions for the copied column definitions will only be copied
if INCLUDING DEFAULTS is specified. The default behavior is to exclude
default expressions, resulting in the copied columns in the new table having
null defaults."

I would like to suggest adding a sentence between the first and second:

Defaults created via the "serial" and "bigserial" construct - or that use
"nextval(...)" generally - are copied as-is resulting in both tables sharing
the same underlying sequence.

Note that this is technically contrary to the statement:

Unlike INHERITS, the new table and original table are completely decoupled
after creation is complete.

I'm working in 9.0 and noticed this incidentally when a FK constraint failed
but the expected ID was somewhere in the 10s while the actual was somewhere
upward of 80,000 which clued me into the fact that the original sequence was
being used.

As an aside it may be worth noting (or linking to) a quick way to change the
default on the new table if this is done in error and a correction is
desired.  For my purposes I'll likely just leave it alone.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Create-Table-Like-Including-Default-Sequences-tp5759108.html
Sent from the PostgreSQL - docs mailing list archive at Nabble.com.

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 25 Jan 2014 14:33:21 -0500

> desired.  For my purposes I'll likely just leave it alone.

Oh, that is something I had not considered.  I have applied the attached
doc patch for 9.4.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
new file mode 100644
index a422edd..e0b8a4e
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 329,335 ****
       </para>
       <para>
        Default expressions for the copied column definitions will only be
!       copied if <literal>INCLUDING DEFAULTS</literal> is specified.  The
        default behavior is to exclude default expressions, resulting in the
        copied columns in the new table having null defaults.
       </para>
--- 329,338 ----
       </para>
       <para>
        Default expressions for the copied column definitions will only be
!       copied if <literal>INCLUDING DEFAULTS</literal> is specified. 
!       Defaults that call database-modification functions, like
!       <function>nextval</>, create a linkage between the original and
!       new tables.  The
        default behavior is to exclude default expressions, resulting in the
        copied columns in the new table having null defaults.
       </para>