To create a copy as close as possible 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 have been the default behavior to begin with.
Postgres 10 "fixed" that by adding IDENTITY columns complying to the SQL-standard, which have internal, exclusively dedicated sequences, and behave as desired with CREATE TABLE .. (LIKE ..). The manual:
INCLUDING IDENTITY
Any identity specifications of copied column definitions will be copied. A new sequence is created for each identity column of the new table, separate from the sequences associated with the old table.
Bold emphasis mine. Existing serial columns remain unchanged. Consider replacing serial columns. See:
Function to copy tables with (or without) serial columns
While any serial columns are still involved, this function should do the job:
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, _newschema text = NULL) RETURNS void LANGUAGE plpgsql VOLATILE AS $func$ DECLARE _newtbl_qual_n_quot text; _sql text; BEGIN -- If target schema is not given, default to schema of source IF _newschema IS NULL THEN SELECT INTO _newschema relnamespace::regnamespace::text FROM pg_class WHERE oid = _tbl; END IF; _newtbl_qual_n_quot := format('%I.%I', _newschema, _newtbl); -- Copy table EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING ALL);', _newtbl_qual_n_quot, _tbl); -- Fix serial columns, if any SELECT INTO _sql string_agg('CREATE SEQUENCE ' || seq_qual_n_quot, E';\n') || E';\n' || string_agg(format('ALTER SEQUENCE %s OWNED BY %s.%I' , seq_qual_n_quot, _newtbl_qual_n_quot, a.attname), E';\n') || E';\n' || 'ALTER TABLE ' || _newtbl_qual_n_quot || E'\n ' || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$ , a.attname, seq_qual_n_quot), E'\n, ') FROM pg_attribute a JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum , format('%I.%I' , _newschema , _newtbl || '_' || a.attname || '_seq' ) AS seq_qual_n_quot -- new seq name, qualified & quoted where necessary WHERE a.attrelid = _tbl AND a.attnum > 0 AND NOT a.attisdropped AND a.atttypid = ANY ('{int,int8,int2}'::regtype[]) AND pg_get_expr(ad.adbin, ad.adrelid) = '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$;
Call:
SELECT f_copy_tbl('tbl', 'tbl1');
Or:
SELECT f_copy_tbl('myschema.tbl', 'CpY_tbl_odd_name', 'other_schema');
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);
The source (1st parameter) must be a table, view, materialized view, composite type, or foreign table. Optionally schema-qualified.
The 2nd parameter is the new table name.
The 3nd parameter is schema of the new table. If not given, it defaults to the schema of the source.
The system column pg_attrdef.adsrc has been dropped in Postgres 12. Using pg_get_expr(ad.adbin, ad.adrelid) instead as instructed in the manual Works in older versions, too.
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.
db<>fiddle here
Old sqlfiddle
