Postgresql – How to safely replace the old table with its cleaned copy

ddldependenciespostgresqlsequence

I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with

DROP TABLE table;
ALTER TABLE table_cleaned rename to table;

but the first statement gives cannot drop table cals_status because other objects depend on it.

I definitely don't want to use cascade as it would delete all the dependent rows.

How can I safely replace the old table with its cleaned copy?

Following Erwin Brandstetter's advice, this is the missing detail of the error message:

DETAIL:  default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq

Best Answer

By default, the error message tells you more. Like:

ERROR: cannot drop table tbl because other objects depend on it
SQL state: 2BP01
Detail: view v_tbl depends on table tbl
Hint: Use DROP ... CASCADE to drop the dependent objects too.

General solution

Include the object mentioned in the Detail line in your script.

Also, to do it safely, wrap all of it in a transaction, and write-lock the old table before working on the new to avoid losing work done during the transition:

BEGIN;

LOCK TABLE tbl IN SHARE MODE;

-- prepare tbl_cleaned

DROP VIEW v_tbl;  -- or any other depending object
-- more?

DROP TABLE tbl;
ALTER TABLE tbl_cleaned rename to tbl;

CREATE VIEW v_tbl AS SELECT  ... -- actual definition, see below
-- more?

COMMIT;

There may be more depending objects. Same procedure.

In the example, to get the view definition:

SELECT 'CREATE VIEW v_foo1 AS '
    || pg_get_viewdef('v_foo1'::regclass) AS view_definition;

See:

How to list the columns of a view in Postgres?

And you may want to rename constraints and indices to fit the renamed table - or hold back on those and create with correct names after renaming the new table. (Again, be sure to get the definition before dropping the old table!)

If there are many depending objects and the table is not too big, a good alternative might be to prepare the new table (possibly as temporary table for better performance), then TRUNCATE the original and INSERT from the prepared new table. This way, depending objects like views, FK constraints, rules, function using the table type etc. can just stay in place. See:

Add new column without table lock?

Or you might be able to update the old table in place? This related answer shows code for all three variants:

Compacting a sequence in PostgreSQL

Specific solution

Your particular error message:

DETAIL:  default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq

... indicates that you are (ab-)using a sequence to generate default values for multiple tables. Or at least it's owned by the wrong column. The solution depends on how you want to deal with the situation.

Or you created the new table copying the complete structure including default values depending on the same sequence.

Typically, you want a dedicated sequence. So you should probably make the new column own the SEQUENCE:

ALTER SEQUENCE cals_status_id_seq OWNED BY tbl_cleaned.id;  -- column in new table *before* switching

DROP TABLE only cascades to the sequence if it's owned by one of the columns in the table to be dropped.

How to reset Postgres' primary key sequence when it falls out of sync?

You may be interested in IDENTITY columns which avoid this kind of complication with serial columns. See:

Auto increment table column

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.

Best Answer

General solution

Specific solution

Related Solutions

PostgreSQL – How to Replace a Materialized View

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

Function to copy any given table

Related Question