Re: Primary key data type: integer vs identity

On 4/19/19 11:32 AM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 4/19/19 11:14 AM, Rich Shepard wrote:
>      > On Fri, 19 Apr 2019, Adrian Klaver wrote:
>      >
>      >> If it is working for you now I see no reason to switch.
>      >
>      > Adrian,
>      >
>      > It does work. I just learned about the SQL identity type and want
>     to learn
>      > when it's most appropriate to use. The databases I develop all
>     work with
>      > integers as primary keys and reading about the type didn't
>     clarify (for me)
>      > when it should be used.
> 
>     Mainly for folks that want cross database SQL compliance. It is not a
>     type so much as a way of specifying an auto-increment column.
> 
> 
> It also sounds like it has advantages in terms of tying your sequence 
> directly to the column.  If you drop a serial column, it doesn't drop 
> the sequence.

Misread your post the first time still:

  create table serial_test(id serial, fld_1 text); 
 

CREATE TABLE 
 

test=> \d serial_test 
 

                             Table "public.serial_test" 
 

  Column |  Type   | Collation | Nullable |                 Default 
 

--------+---------+-----------+----------+----------------------------------------- 
 

  id     | integer |           | not null | 
nextval('serial_test_id_seq'::regclass) 

  fld_1  | text    |           |          | 
 

 
 

test=> alter table serial_test drop column id; 
 

ALTER TABLE 
 

test=> \d serial_test 
 

            Table "public.serial_test" 
 

  Column | Type | Collation | Nullable | Default 
 

--------+------+-----------+----------+--------- 
 

  fld_1  | text |           |          | 
 

 
 

test=> select * from serial_test_id_seq ; 

ERROR:  relation "serial_test_id_seq" does not exist
LINE 1: select * from serial_test_id_seq ;

> Once I've upgraded to 10+, I might look at converting my existing serial 
> columns.  Peter Eisentraut wrote a good piece(1) on identity columns, 
> including a function for converting existing serial columns.  I've 
> copied the function below, but had two questions about it:
> 
> 1)  Would the function as written also reassign ownership to that table 
> column?  (I see the update to pg_depend and pg_attribute, but don't know 
> enough about them to know if that changes ownership)
> 2)  Would one have to be a superuser to do this?
> 
> Thanks,
> Ken

-- 
Adrian Klaver
adrian.klaver@aklaver.com