On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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
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 = "" 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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
