Re: Primary key data type: integer vs identity

On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver <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.
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

(1) https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col
name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
colnum smallint;
seqid oid;
count int;
BEGIN
-- find column number
SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND
attname = col;
IF NOT FOUND THEN
RAISE EXCEPTION 'column does not exist';
END IF;

-- find sequence
SELECT INTO seqid objid
FROM pg_depend
WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl,
colnum)
AND classid = 'pg_class'::regclass AND objsubid = 0
AND deptype = 'a';

GET DIAGNOSTICS count = ROW_COUNT;
IF count < 1 THEN
RAISE EXCEPTION 'no linked sequence found';
ELSIF count > 1 THEN
RAISE EXCEPTION 'more than one linked sequence found';
END IF;

-- drop the default
EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) ||
' DROP DEFAULT';

-- change the dependency between column and sequence to internal
UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a';

-- mark the column as identity column
UPDATE pg_attribute
SET attidentity = 'd'
WHERE attrelid = tbl
AND attname = col;
END;
$$;

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.