From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
Cc: | Rich Shepard <rshepard(at)appl-ecosys(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Primary key data type: integer vs identity |
Date: | 2019-04-19 19:07:36 |
Message-ID: | c53b291e-4de2-4ee1-4fa8-4287f622fd3b@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 4/19/19 11:32 AM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2019-04-19 19:35:00 | Re: Primary key data type: integer vs identity |
Previous Message | Adrian Klaver | 2019-04-19 19:02:03 | Re: Primary key data type: integer vs identity |