Re: Converting to identity columns with domains on PK columns

On 7/5/19 1:55 PM, PegoraroF10 wrote:
> - Because we don´t need to give rigths to user on sequences;
> - Nobody will change values of pk fields, because we would like to have
> GENERATE ALWAYS on those PK Fields.

An IDENTITY column is still backed by a sequence:

create table identity_test(id integer PRIMARY KEY GENERATED BY DEFAULT 
AS IDENTITY);

\ds identity_test_id_seq
                  List of relations
  Schema |         Name         |   Type   |  Owner
--------+----------------------+----------+---------
  public | identity_test_id_seq | sequence | aklaver

You end up with same thing as using a sequence(with some additional 
syntax over its behavior):

create table seq_id_test(id integer PRIMARY KEY);

create sequence seq_id_test_seq AS integer OWNED BY seq_id_test.id;

\ds seq_id_test_seq
                List of relations
  Schema |      Name       |   Type   |  Owner
--------+-----------------+----------+---------
  public | seq_id_test_seq | sequence | aklaver


Rights are the same:

\c - production
You are now connected to database "test" as user "production".

test_(production)> insert into identity_test (id) values(default);
ERROR:  permission denied for table identity_test
test_(production)> insert into seq_id_test (id) values(default);
ERROR:  permission denied for table seq_id_test

A user can change the PK by using OVERRIDING SYSTEM VALUE in an INSERT.


> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com