Re: CREATE TABLE LIKE and SERIAL

2009/10/30 Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk>:
>> On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd
>> <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk> wrote:
>>>
>>> What is the "correct" behavior of a serial column when a table is created
>>> with LIKE? The manual is silent on this.
>>>
>>> What appears to be happening with 8.2 is that the column in the new table
>>> refers to the original sequence generator.
>>>
>>> --
>>> Mark Morgan Lloyd
>>> markMLl .AT. telemetry.co .DOT. uk
>>>
>>> [Opinions above are the author's, not those of his employers or
>>> colleagues]
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>
> silly8888 wrote:
>> In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is
>> specified. Otherwise, only the not null constraint is copied. I think
>> this is the most reasonable behavior and I don't see why it should
>> have been explicitly stated in the manual.
>
> I didn't say the sequence value, I said the sequence itself. After all the
> normal usage will be where the "pattern" table is empty.
>
> In other words the newly-created table is not completely decoupled from the
> pattern, the sequence is a shared resource and this is irrespective of any
> including/excluding specification.
>

I can see why you wouldn't expect it to end up sharing the same
sequence. If you were to manually create a sequence and wanted to use
it on a column, you probably wouldn't bother using the SERIAL
datatype, but use integer instead. So really since we know the first
table has a datatype of SERIAL on one of its columns, we might instead
wish to have it create a new implicit sequence instead of merely
converting it to an INTEGER datatype and adding a default constraint
to the same sequence as the original table.

In theory, you could create a table and subsequently change the SERIAL
column's default value to another sequence, but I imagine that this
would be a very rare case since you wouldn't bother with the SERIAL
datatype in the first place if you knew you were going to do that.

I believe most people would expect a new implicit sequence to be
created, but maybe a PostgreSQL "elder" could enlighten us further.

Thom