Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (
  test1       integer
, tbl_id      bigserial PRIMARY KEY   -- PK with index
, col1        timestamp(2) DEFAULT date_trunc('day', now()) -- other functional default
, col2        numeric(9,3)
, test2       integer DEFAULT 666     -- other simple default
, some_serial smallserial             -- no index
, some_other  serial UNIQUE NOT NULL  -- index, but not PK
, "Odd_COL"   serial                  -- quoted illegal name
);

CREATE TABLE noserial (
  test1       integer PRIMARY KEY
, col1        timestamp(2)
, col2        numeric(9,3)
, test2       integer
);

CREATE SCHEMA myschema;
CREATE TABLE myschema.tbl (
  "ID"        serial PRIMARY KEY
, col1        timestamp(2)
, col2        numeric(9,3)
, test2       integer
, "sTupid_colNAME" smallserial
);

CREATE SCHEMA other_schema;

CREATE TABLE



CREATE TABLE



CREATE SCHEMA



CREATE TABLE



CREATE SCHEMA


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$;

CREATE FUNCTION


-- Call
SELECT f_copy_tbl('tbl', 'cpy_tbl')
     , f_copy_tbl('noserial', 'cpy_noserial')
     , f_copy_tbl('myschema.tbl', 'cpy_tbl_same_schema')
     , f_copy_tbl('myschema.tbl', 'CpY_tbl_odd_name', 'other_schema');


f_copy_tbl
f_copy_tbl
f_copy_tbl
f_copy_tbl









SELECT 1


-- Test insert to verify
INSERT INTO tbl     DEFAULT VALUES;
INSERT INTO tbl     DEFAULT VALUES;
INSERT INTO cpy_tbl DEFAULT VALUES;
INSERT INTO cpy_tbl DEFAULT VALUES;

INSERT INTO noserial(test1)     VALUES (3),(4);
INSERT INTO cpy_noserial(test1) VALUES (3),(4);

INSERT INTO myschema.tbl DEFAULT VALUES;
INSERT INTO myschema.cpy_tbl_same_schema DEFAULT VALUES;
INSERT INTO other_schema."CpY_tbl_odd_name" DEFAULT VALUES;

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 2



INSERT 0 2



INSERT 0 1



INSERT 0 1



INSERT 0 1


-- All serial columns independent now
TABLE tbl;
TABLE cpy_tbl;


test1
tbl_id
col1
col2
test2
some_serial
some_other
Odd_COL


null
1
2023-06-14 00:00:00
null
666
1
1
1

null
2
2023-06-14 00:00:00
null
666
2
2
2



SELECT 2




test1
tbl_id
col1
col2
test2
some_serial
some_other
Odd_COL


null
1
2023-06-14 00:00:00
null
666
1
1
1

null
2
2023-06-14 00:00:00
null
666
2
2
2



SELECT 2


TABLE noserial;
TABLE cpy_noserial;


test1
col1
col2
test2


3
null
null
null

4
null
null
null



SELECT 2




test1
col1
col2
test2


3
null
null
null

4
null
null
null



SELECT 2


TABLE myschema.tbl;
TABLE myschema.cpy_tbl_same_schema;
TABLE other_schema."CpY_tbl_odd_name";


ID
col1
col2
test2
sTupid_colNAME


1
null
null
null
1



SELECT 1




ID
col1
col2
test2
sTupid_colNAME


1
null
null
null
1



SELECT 1




ID
col1
col2
test2
sTupid_colNAME


1
null
null
null
1



SELECT 1
test1	col1	col2	test2
3	null	null	null
4	null	null	null
test1	col1	col2	test2
3	null	null	null
4	null	null	null
test1	tbl_id	col1	col2	test2	some_serial	some_other	Odd_COL
null	1	2023-06-14 00:00:00	null	666	1	1	1
null	2	2023-06-14 00:00:00	null	666	2	2	2