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.

how did you create the tbl_id_seq? and how do you drop tbl? Please provide some code. (I just tested it and it worked fine for me so most likely the problem is in a detail thats not provided here.) — Angelo Fuchs
– Angelo Fuchs, Commented Nov 28, 2011 at 17:00

Milen A. Radev · Accepted Answer · 2011-11-28 17:25:42Z

6

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.

answered Nov 28, 2011 at 17:25

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:44:16Z

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Nov 28, 2011 at 17:46

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

postgresql drop table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related