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

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.

On Thu, Jun 13, 2013 at 07:20:05PM -0700, David Johnston wrote:
> 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.

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

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +