Обсуждение: CREATE TABLE LIKE and SERIAL
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]
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. On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
> On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd > <markMLl.pgsql-general@telemetry.co.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@postgresql.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. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
2009/10/30 Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk>: >> On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd >> <markMLl.pgsql-general@telemetry.co.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@postgresql.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
Thom Brown <thombrown@gmail.com> writes:
> 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.
Thinking of SERIAL as a type is your first mistake ;-). It is not a
type. It is a shorthand for making a sequence and sticking a suitable
default on a plain integer column. So what LIKE sees is an integer
column with a default, and it copies that.
regards, tom lane
2009/10/30 Tom Lane <tgl@sss.pgh.pa.us>: > Thom Brown <thombrown@gmail.com> writes: >> 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. > > Thinking of SERIAL as a type is your first mistake ;-). It is not a > type. It is a shorthand for making a sequence and sticking a suitable > default on a plain integer column. So what LIKE sees is an integer > column with a default, and it copies that. > Well I realise SERIAL is a convenience rather than a datatype in its own right, but I'm surprised that LIKE can't differentiate between a column created with integer and one created with serial. The table continues to report a serial datatype after its creation. Is it's reference too low-level? Could it be altered to support recognition of serial usage? Thom
Thom Brown <thombrown@gmail.com> writes:
> Well I realise SERIAL is a convenience rather than a datatype in its
> own right, but I'm surprised that LIKE can't differentiate between a
> column created with integer and one created with serial. The table
> continues to report a serial datatype after its creation.
Really?
regression=# create table foo (f1 serial);
NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
f1 | integer | not null default nextval('foo_f1_seq'::regclass)
regression=#
We used to try to treat serial as more like a real type (in particular
pg_dump used to try to dump the results of this using "serial") but we
found out that that was actively a bad idea, because there were too
many corner cases where it did the wrong thing. I doubt we'll want
to go back in that direction.
regards, tom lane
2009/10/30 Tom Lane <tgl@sss.pgh.pa.us>:
> Thom Brown <thombrown@gmail.com> writes:
>> Well I realise SERIAL is a convenience rather than a datatype in its
>> own right, but I'm surprised that LIKE can't differentiate between a
>> column created with integer and one created with serial. The table
>> continues to report a serial datatype after its creation.
>
> Really?
>
> regression=# create table foo (f1 serial);
> NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
> CREATE TABLE
> regression=# \d foo
> Table "public.foo"
> Column | Type | Modifiers
> --------+---------+--------------------------------------------------
> f1 | integer | not null default nextval('foo_f1_seq'::regclass)
>
> regression=#
>
> We used to try to treat serial as more like a real type (in particular
> pg_dump used to try to dump the results of this using "serial") but we
> found out that that was actively a bad idea, because there were too
> many corner cases where it did the wrong thing. I doubt we'll want
> to go back in that direction.
>
Erk... that's strange. I've obviously made the wrong assumptions
here. I'm basing it on pgAdmin describing the table as it reports a
SERIAL datatype. Not sure how it's deciding that. I created a manual
sequence, then a new table with an integer column that gets it's
default value from the sequence, set that to be NOT NULL and act as
the primary key, but it still reports it as an integer, so it doesn't
seem to be assuming it based on these constraints.
How is pgAdmin determining the serial type in this case?
Thom
Tom Lane wrote: > Thinking of SERIAL as a type is your first mistake ;-). It is not a > type. It is a shorthand for making a sequence and sticking a suitable > default on a plain integer column. So what LIKE sees is an integer > column with a default, and it copies that. That's entirely fair, and the manual section dealing with types is very careful to start off with "The data types serial and bigserial are not true types". However I think that the description of CREATE TABLE ... LIKE really could do with a "health warning" for this case. Looking at this very slightly deeper and assuming that the user is aware of the pitfalls, it's obviously easy for him to create a new sequence and to use it as the default value. But what if he wants the new sequence to inherit the current state of an existing one: might I suggest CREATE SEQUENCE ... LIKE would be appropriate here? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Thom Brown <thombrown@gmail.com> writes:
> How is pgAdmin determining the serial type in this case?
Most likely it's looking for the pg_depend entry that shows the sequence
as being "owned by" the column. However, that's an oversimplification
of reality. I would imagine that pgAdmin will lie to you in exactly
the same cases that used to break pg_dump (notably, where someone has
manually adjusted either the default expression or the sequence...)
regards, tom lane