Mapping Identity column to Serial instead of using Sequences

[issafram]

Is there a reason that the script creates sequences instead of marking a column as SERIAL?
I'm not a Postgres expert but I think it might be easier to use Serial.

Please let me know if there is an option for that

[madtibo]

Hello @issafram

The PostgreSQL implementation makes it the same: https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-SERIAL

If I manually create a table using a serial, I will get a sequence:

thibaut=# CREATE TABLE testtable ( value serial PRIMARY KEY);
CREATE TABLE
thibaut=# \d testtable
                                 Table « public.testtable »
 Colonne |  Type   | Collationnement | NULL-able |                Par défaut                
---------+---------+-----------------+-----------+------------------------------------------
 value   | integer |                 | not null  | nextval('testtable_value_seq'::regclass)
Index :
    "testtable_pkey" PRIMARY KEY, btree (value)

thibaut=# \ds testtable_value_seq
                  Liste des relations
 Schéma |         Nom         |   Type   | Propriétaire 
--------+---------------------+----------+--------------
 public | testtable_value_seq | séquence | thibaut
(1 line)

The script does not create some real identity columns as we can use since PostgreSQL version 10:

thibaut=# CREATE TABLE testtable2 ( value int PRIMARY KEY GENERATED ALWAYS AS IDENTITY);
CREATE TABLE
thibaut=# \d testtable2
                          Table « public.testtable2 »
 Colonne |  Type   | Collationnement | NULL-able |          Par défaut          
---------+---------+-----------------+-----------+------------------------------
 value   | integer |                 | not null  | generated always as identity
Index :
    "testtable2_pkey" PRIMARY KEY, btree (value)

thibaut=# \ds testtable2_*
                   Liste des relations
 Schéma |         Nom          |   Type   | Propriétaire 
--------+----------------------+----------+--------------
 public | testtable2_value_seq | séquence | thibaut
(1 ligne)

Maybe we should now use IDENTITY columns.

[issafram]

Maybe we should now use IDENTITY columns.

I think you are right. Maybe default to IDENTITY and put an option flag of sorts to use sequences (in case of older Postgres versions).

What do you think?

[madtibo]

Very good idea!

Can you propose a PR with this evolution?

Cordialement,

[issafram]

@madtibo Sorry I haven't had a chance to work on this yet. It looks like maybe #112 might be working on that. If not, I'll try to get around to it when I can

[madtibo]

@issafram #112 is a special case for sequences values. It is not very clear how the OP solved its problem. We will see that if we get a PR...

[madtibo]

There is now the #118 that should do the trick.

If you are still using it, could you test it on your data?

[madtibo]

#150 merged