Copy table with separate sequences for serial columns

Question

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;

Answer 1

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.

Answer 2

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.

Answer 3

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__);}
}