PostgreSQL: serial vs identity

by

serial is the “old” implementation of auto-generated unique values that has been part of Postgres for ages. However that is not part of the SQL standard.

To be more compliant with the SQL standard, Postgres 10 introduced the syntax using generated as identity.

The underlying implementation is still based on a sequence, the definition now complies with the SQL standard. One thing that this new syntax allows is to prevent an accidental override of the value.

Consider the following tables:

create table t1 (id serial primary key);
create table t2 (id integer primary key generated always as identity);

Now when you run:

insert into t1 (id) values (1);

The underlying sequence and the values in the table are not in sync any more. If you run another

insert into t1 default_values;

You will get an error because the sequence was not advanced by the first insert, and now tries to insert the value 1 again.

With the second table however,

insert into t2 (id) values (1);

Results in:

ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.

So you can’t accidentally “forget” the sequence usage. You can still force this, using the override system value option:

insert into t2 (id) overriding system value values (1);

which still leaves you with a sequence that is out-of-sync with the values in the table, but at least you were made aware of that.

identity columns also have another advantage: they also minimize the grants you need to give to a role in order to allow inserts.

While a table using a serial column requires the INSERT privilege on the table and the USAGE privilege on the underlying sequence this is not needed for tables using an identity columns. Granting the INSERT privilege is enough.

It is recommended to use the new identity syntax rather than serial

More Related Contents:

  1. How to reset postgres’ primary key sequence when it falls out of sync?
  2. How to import CSV file data into a PostgreSQL table
  3. Calculating and saving space in PostgreSQL
  4. The forgotten assignment operator “=” and the commonplace “:=”
  5. Equivalent to unpivot() in PostgreSQL
  6. Passing user id to PostgreSQL triggers
  7. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  8. I forgot the password I entered during postgres installation
  9. Do nullable columns occupy additional space in PostgreSQL?
  10. How do I efficiently select the previous non-null value?
  11. Postgres password authentication fails
  12. Generate series of dates – using date type as input
  13. Copy a few of the columns of a csv file into a table
  14. Postgres query to check a string is a number
  15. Using pyspark to connect to PostgreSQL
  16. Returning from a function with OUT parameter
  17. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  18. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  19. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  20. Update timestamp when row is updated in PostgreSQL
  21. How to add a conditional unique index on PostgreSQL
  22. Select from a table variable
  23. Error: Column does not exist in postgresql for update [duplicate]
  24. How to see the CREATE VIEW code for a view in PostgreSQL?
  25. How can I ensure that a materialized view is always up to date?
  26. PostgreSQL: Query has no destination for result data
  27. Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy
  28. Convert escaped Unicode character back to actual character in PostgreSQL
  29. postgresql error PANIC: could not locate a valid checkpoint record
  30. PostgreSQL: export resulting data from SQL query to Excel/CSV

Leave a Comment