I would like to drop the sequence used in table and the table itself in one statement using CASCADE, but I'm getting NOTICE and table is not dropped. For example:
CREATE SEQUENCE seq1;
CREATE TABLE t1 (f1 INT NOT NULL DEFAULT nextval('seq1'));
And then when I do:
DROP SEQUENCE seq1 CASCADE;
I get following message, and the table is not dropped:
NOTICE: drop cascades to default for table t1 column f1
I'm definitely doing something wrong but these are my very first steps in PostgreSQL.
You have a misconception about dependencies. The table never is a depending object of an associated sequence and is never dropped by a
DROP SEQUENCE ... CASCADE;
Only a DEFAULT value drawing from the sequence "depends" on the sequence and is set to NULL if the sequence is deleted with CASCADE.
It is the other way round: if the sequence is owned by a table column it is dropped with a
DROP TABLE f1 CASCADE;
For a sequence to be owned by a table column you can either use the serial type as Milen already suggested. Or you can ALTER an existing sequence:
ALTER SEQUENCE seq1 OWNED BY t1.f1;
I don't know why are you creating a sequence manually - maybe you have justification, or maybe it's due to habits working with another DBMS.
But if you don't have a special need for it, use the SERIAL pseudo-type and when you drop the table the sequence(s) behind the SERIAL column(s) will be dropped too.
You asked to drop the sequence and cascade that action. While the default can't exist without the sequence, and it is therefore dropped, the table and the column can exist without the sequence, so they remain.
With the way you've specified this, dropping the table will not drop the sequence, although you can make the sequence depend on the column with which it is used, and therefore have it drop automatically if you drop the table. You can do this by altering the owner of the sequence, or use SERIAL instead. Declaring a column to be type SERIAL automatically creates a sequence, makes it generate a default for the column, and makes that column the owner of the sequence.
drop cascade table table_name; you can also use this... and i will also recommend you to use a serial with primary key so that you can identify a column uniquely..