PostgreSQL Dynamic SQL – Copy Table with Separate Sequences for Serial Columns

database-designdynamic-sqlpostgresqlprimary-keysequence

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool that I ultimately intend to use on the table parts I created this new tool to duplicate parts so I'd end up with a parts1 table. When I thought I had finally gotten all the issues ironed out I encountered the following error when the column tool deletes the table:

ERROR: cannot drop table parts because other objects depend on it
DETAIL: default for table parts1 column id depends on sequence
parts_id_seq1

I've spent the greater part of my day working towards this solution so in short can I simply use string functions to rename the SEQUENCE_NAME variable to disassociate the parts table from the parts table or is it a more convoluted issue than that? Here is the query:

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid 
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid 
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace 
    WHERE s.relkind = 'S' 
    AND n.nspname = 'public' 
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;

Best Answer

To create a copy as close as possible you must use INCLUDING ALL with CREATE TABLE .. (LIKE ..) since there can be any number of columns with defaults that you obviously want to copy.

You just want serial columns to get their own, independent sequence, which makes a lot of sense and probably should be the default behavior. For now, this should do the job:

Function to copy any given table

Copies any given table (must exist) with new given name and independent serial columns (if any).
Data is not included, it's trivial to copy that, too.

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any    
SELECT INTO _sql
       string_agg('CREATE SEQUENCE ' || seq, E';\n') || E';\n'
    || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'
                        , seq, _newtbl, a.attname), E';\n') || E';\n'
    || 'ALTER TABLE ' || quote_ident(_newtbl) || E'\n  '
    || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                 , a.attname, seq), E'\n, ')
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                       AND ad.adnum   = a.attnum
     , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
         || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
         || '''::regclass)'
;

IF _sql IS NOT NULL THEN
   EXECUTE _sql;
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;

Call:

SELECT f_copy_tbl('tbl', 'tbl1');

Produces and executes SQL code of the form:

CREATE TABLE tbl1 (LIKE tbl INCLUDING ALL);
-- only if there are serial columns:
CREATE SEQUENCE tbl1_tbl_id_seq;     -- one line per serial type ..
CREATE SEQUENCE "tbl1_Odd_COL_seq";  -- .. two in this example
ALTER SEQUENCE tbl1_tbl_id_seq OWNED BY tbl1.tbl_id;
ALTER SEQUENCE "tbl1_Odd_COL_seq" OWNED BY tbl1."Odd_COL";
ALTER TABLE tbl1
  ALTER tbl_id SET DEFAULT nextval('tbl1_tbl_id_seq'::regclass)
, ALTER "Odd_COL" SET DEFAULT nextval('"tbl1_Odd_COL_seq"'::regclass);

  • Only serial columns get their own sequence. Other column default are copied unchanged - including nextval() from a sequence that is not owned by the column or differs in any way from a serial.

  • The function is safe against SQL injection and should work with arbitrary table and column names.

SQL Fiddle.

It would be a useful option for the LIKE feature to create separate sequences for serial columns. You may want to post a friendly feature request to pgsql-general.

Related Question