On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver <adrian.kla...@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. 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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
