Thank you all. The configurability of Postgre is splendid
On Sun, Jun 16, 2019 at 12:43 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes: > On 2019-Jun-15, PG Bug reporting form wrote: >> The following SQL executed will drop the sequence `t_id_seq`: >> CREATE TABLE t(id SERIAL, value INT NOT NULL); >> CREATE TABLE t_bak LIKE t INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING >> COMMENTS INCLUDING CONSTRAINTS); >> DROP TABLE t CASCADE; >> This will drop default value of column `value` in t_bak.
> Yes. The reason the sequence is dropped is that it is owned by the t.id > column, so when the column goes away, so does the sequence. And this > cascades to that default value.
Yeah, not a bug. The OP might find that generated-as-identity columns work more to his liking than SERIAL does: copying them with LIKE creates an independent new sequence.
regression=# create table src (f1 int generated always as identity); CREATE TABLE regression=# create table dest (like src including identity); CREATE TABLE regression=# \d+ dest Table "public.dest" Column | Type | Collation | Nullable | Default | Storag e | Stats target | Description --------+---------+-----------+----------+------------------------------+------- --+--------------+------------- f1 | integer | | not null | generated always as identity | plain | | Access method: heap
regression=# insert into dest default values; INSERT 0 1 regression=# insert into dest default values; INSERT 0 1 regression=# table dest; f1 ---- 1 2 (2 rows)
regression=# drop table src; DROP TABLE regression=# insert into dest default values; INSERT 0 1 regression=# insert into dest default values; INSERT 0 1 regression=# table dest; f1 ---- 1 2 3 4 (4 rows)