Copy table with separate sequences for serial columns

Question 1

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;

Question 2

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}

Question 3

The problem is that your parts1 table uses the sequence from the parts table (instead of its own sequence) … thus it complains that you can’t drop the parts table because the parts1 table depends on it (via default value for one of the columns)… You are getting this behavior in how you are creating the parts1 table…

CREATE TABLE – see the LIKE source_table [ like_option ... ] section

You can achieve the results you want by creating the table as you have done, and then creating new sequences, setting new default values, and creating new dependencies for each SERIAL column (since the LIKE option behavior apparently does not create its own sequences as of this time).

The steps to accomplish the above are given in Deszo’s answer here

(copied for convenience .. altered his step 2 since you already will have a table created)

CREATE SEQUENCE tablename_colname_seq; -- step 1
ALTER TABLE tablename ALTER COLUMN colname SET DEFAULT nextval('tablename_colname_seq') -- step 2
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; -- step 3

You can also use this SQL to help assist with identifying which columns belong to the pkey for the table in question, although this won’t help you identify ALL serial columns (if one happens to not be a pkey).

select
  sch.nspname as schema_name,
  tab.relname as table_name,
  col.attname as column_name,
  pk.conname as pkey
from pg_constraint pk
join pg_class tab on pk.conrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
join pg_attribute col on tab.oid = col.attrelid and pk.conkey @> ARRAY[col.attnum]
where pk.contype = 'p' and sch.nspname = '<your schema>' and tab.relname = '<your table>';

Please see Erwin Brandstetter’s answer for (I assume) a more complete solution.

Question 4

I wanted to release at least the direct PHP function (I’ll eventually release the tools I’m building to something like Github) though I don’t yet have the time to do much else other than post it here at least. Hopefully this will save others time and please feel free to suggest improvements. I’m not sure how secure the code is (it’s merely a tool I’m only using for development and currently for a client who I’m setting up an intranet-only website) though I did my best to heed the advice from the other questions/answers by Erwin Brandstetter and Joishi Bodio.

Sample URL request:

db_table_duplicate.php?db=database_name&table1=parts2&table2=parts3

I simply have PHP call functions and since I often repeat the actions my PostgreSQL tools execute I simply store the information in a $_GET array.

function dbtd_04_commit()
{
 echo '<p><strong>Step 4/4:</strong> Duplicating the table `'.$_GET['table1'].'`...</p>'."n";
 $table2 = pg_escape_string($_SESSION['db'],$_GET['table2']);
 $query1 = "SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name='$table2';";
 $result1 = pg_query($_SESSION['db'],$query1);

 if ($result1)
 {
  $count1 = pg_num_rows($result1);
  $table1 = pg_escape_string($_SESSION['db'],$_GET['table1']);

  if ($count1>0)
  {
   echo '<div><p><strong class="error">Error:</strong> the table '.$table2.' already exists; the table '.$table1.' was <em>not</em> duplicated. Would you like to <a href="tools/'.$GLOBALS['paths']->file.'?db='.$_GET['db'].'&#38;table1='.$_GET['table1'].'">try a different name</a>?</p></div>'."n";
  }
  else
  {//Check for custom table-structure duplication function.
   $query2 = "SELECT p.proname FROM pg_catalog.pg_namespace AS n JOIN pg_catalog.pg_proc AS p ON p.pronamespace=n.oid WHERE n.nspname='public' AND proname='f_copy_tbl';";
   $result2 = pg_query($_SESSION['db'],$query2);

   if ($result2)
   {
    $count2 = pg_num_rows($result2);

    if ($count2==0)
    {
     $query3 = "CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text) RETURNS void AS $func$"."n";
     $query3 .= "DECLARE _sql text;"."n";
     $query3 .= "BEGIN"."n";
     //-- Copy table
     $query3 .= "EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);"."n";
     //-- Fix serial columns, if any
     $query3 .= "SELECT INTO _sql"."n";
     $query3 .= " string_agg('CREATE SEQUENCE ' || seq, E';n') || E';n'"."n";
     $query3 .= " || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'"."n";
     $query3 .= " , seq, _newtbl, a.attname), E';n') || E';n'"."n";
     $query3 .= " || 'ALTER TABLE ' || quote_ident(_newtbl) || E'n  '"."n";
     $query3 .= " || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$"."n";
     $query3 .= " , a.attname, seq), E'n, ')"."n";
     $query3 .= "FROM pg_attribute a"."n";
     $query3 .= "INNER JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum, quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq"."n";// -- new seq name
     $query3 .= "WHERE a.attrelid = _tbl"."n";
     $query3 .= "AND a.attnum > 0"."n";
     $query3 .= "AND NOT a.attisdropped"."n";
     $query3 .= "AND a.atttypid = ANY ('{int,int8,int2}'::regtype[])"."n";
     $query3 .= "AND ad.adsrc = 'nextval('''"."n";
     $query3 .= " || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass"."n";
     $query3 .= " || '''::regclass)';"."n";
     $query3 .= "IF _sql IS NOT NULL THEN"."n";
     $query3 .= " EXECUTE _sql;"."n";
     $query3 .= "END IF;"."n";
     $query3 .= "END"."n";
     $query3 .= "$func$  LANGUAGE plpgsql VOLATILE;"."n";
     $result3 = pg_query($_SESSION['db'],$query3);

     if ($result3) {}
     else {sql_error_report($query3,pg_last_error($_SESSION['db']),__FUNCTION__);}
    }

    $query4 = "SELECT f_copy_tbl('$table1', '$table2');";
    $result4 = pg_query($_SESSION['db'],$query4);

    if ($result4)
    {
     $query5 = 'DO'."n";
     $query5 .= '$do$'."n";
     $query5 .= 'BEGIN'."n";
     $query5 .= ' INSERT INTO '.$table2.' SELECT * FROM '.$table1.';'."n";
     $query5 .= ' EXECUTE ('."n";
     $query5 .= ' SELECT format($$SELECT setval('."'%s'::regclass, max(%I)) FROM %s".'$$, pg_get_serial_sequence(a.attrelid::regclass::text, a.attname), a.attname, a.attrelid::regclass'."n";
     $query5 .= ' )'."n";
     $query5 .= ' FROM pg_index i'."n";
     $query5 .= ' JOIN pg_attribute a ON a.attrelid = i.indrelid'."n";
     $query5 .= '  AND a.attnum = i.indkey[0]'."n";
     $query5 .= " WHERE i.indrelid = '".$table2."'::regclass"."n";
     $query5 .= ' AND i.indisprimary'."n";
     $query5 .= ' );'."n";
     $query5 .= 'END'."n";
     $query5 .= '$do$ LANGUAGE plpgsql;';
     $result5 = pg_query($_SESSION['db'],$query5);

     if ($result5)
     {
      $query6 = "VACUUM $table2;";
      $result6 = pg_query($_SESSION['db'],$query6);

      if ($result6)
      {
       echo '<div><p><strong>SUCCESS!</strong> The table '.$table1.' has been duplicated.</p></div>'."n";
      }
      else {sql_error_report($query6,pg_last_error($_SESSION['db']),__FUNCTION__);}
     }
     else {sql_error_report($query5,pg_last_error($_SESSION['db']),__FUNCTION__);}
    }
    else {sql_error_report($query4,pg_last_error($_SESSION['db']),__FUNCTION__);}
   }
   else {sql_error_report($query2,pg_last_error($_SESSION['db']),__FUNCTION__);}
  }
 }
 else {sql_error_report($query1,pg_last_error($_SESSION['db']),__FUNCTION__);}
}

Copy table with separate sequences for serial columns

Question :

Answer :

Function to copy tables with (or without) `serial` columns

Leave a Reply Cancel reply

Question :

Answer :

Function to copy tables with (or without) serial columns

Leave a Reply Cancel reply

Function to copy tables with (or without) `serial` columns