D
D
di2019-06-26 11:28:12
PostgreSQL
di, 2019-06-26 11:28:12

How to create a table from another using a different sequence for the id column?

Reading the Postgres documentation.
https://postgrespro.ru/docs/postgresql/10/sql-crea...
It says the following


The identity characteristic in the definition of the copied column will only be copied if the command has an INCLUDING IDENTITY clause. For each identity column in the new table, a new sequence is created, independent of the sequences associated with the old table.

And I thought that if you do not specify including identity, then a new sequence is created, independent of the sequences associated with the old table.
However, when I ran the command:
create table b_table (like newtable INCLUDING COMMENTS INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING INDEXES);

Then I got a new table where the serial id column was bound to the same sequence as in the old one, which surprised me a little.
Questions:
1. Why?
2. What other mechanisms are there for copying a table structure in Postgres? I just need to get a copy of the table and not link it to the old one.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Melkij, 2019-06-26
@Delgus

melkij=> create table identity_test (i int primary key generated by default as identity, val int);
CREATE TABLE
melkij=> insert into identity_test (val) values (1);
INSERT 0 1
melkij=> table identity_test;
 i | val 
---+-----
 1 |   1
(1 строка)

melkij=> create table identity_test_like (like identity_test INCLUDING COMMENTS INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING INDEXES);
CREATE TABLE
melkij=> \d identity_test_like
                    Таблица "public.identity_test_like"
 Столбец |   Тип   | Правило сортировки | Допустимость NULL | По умолчанию 
---------+---------+--------------------+-------------------+--------------
 i       | integer |                    | not null          | 
 val     | integer |                    |                   | 
Индексы:
    "identity_test_like_pkey" PRIMARY KEY, btree (i)

melkij=> \d identity_test
                                Таблица "public.identity_test"
 Столбец |   Тип   | Правило сортировки | Допустимость NULL |           По умолчанию           
---------+---------+--------------------+-------------------+----------------------------------
 i       | integer |                    | not null          | generated by default as identity
 val     | integer |                    |                   | 
Индексы:
    "identity_test_pkey" PRIMARY KEY, btree (i)

include identity no - identity is not copied. Everything looks correct.
If you are not talking about identity, but about syntactic sugar serial, then its nextval was copied because you yourself asked for it through INCLUDING DEFAULTS.
There is no serial data type. It's syntactic sugar around an int field, creating a sequence, and specifying nextval in default.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question