postgresql drop table

Question

I have two tables (tbl and tbl_new) that both use the same sequence (tbl_id_seq). I'd like to drop one of those tables. On tbl, I've removed the modifier "not null default nextval('tbl_id_seq'::regclass)" but that modifier remains on tbl_new. I'm getting the following error:

ERROR: cannot drop table tbl because other objects depend on it DETAIL: default for table tbl_new column id depends on sequence tbl_id_seq

After reviewing http://www.postgresql.org/docs/9.1/static/sql-droptable.html It looks like there is only CASCADE and RESTRICT as options.

Answer 1

You need to decouple the sequence and the table it "belongs" to:

ALTER SEQUENCE "tbl_id_seq" OWNED BY NONE;

I suppose it was created automatically (and "bound") by defining the tbl_id field of tbl as SERIAL.

Answer 2

To find sequences and all tables that depend on them via column default:

SELECT sn.nspname || '.' || s.relname AS seq
      ,tn.nspname || '.' || t.relname AS tbl
FROM   pg_class s
JOIN   pg_namespace sn ON sn.oid = s.relnamespace
LEFT   JOIN pg_depend d ON d.refobjid = s.oid AND d.deptype <> 'i'
LEFT   JOIN pg_attrdef ad ON ad.oid = d.objid
LEFT   JOIN pg_class t ON t.oid = ad.adrelid
LEFT   JOIN pg_namespace tn ON tn.oid = t.relnamespace
WHERE  s.relkind = 'S'
AND    s.relname ~~ '%part_of_seq_name%' -- enter search term here
ORDER  BY 1,2;

Now with LEFT JOIN to show "free-standing" sequences as well.
You can then use the method @Milen posted to make the sequence "free-standing".

I posted a related answer a few days ago.