CODEWITHSUNDEEP
postgresql
Fofole
Fofole

Reputation: 3548

Alter table error adding 2 columns

Database:POSTGRESQL

I have a table location_type with 2 columns id,name. I want to delete column name and add 2 other columns:county,city. However I get error and I can't figure out why. This is what I tried:

ALTER TABLE location_type
DROP COLUMN name;

ALTER TABLE location_type
ADD (county character varying(255) NOT NULL,
     city character varying(255) NOT NULL);

Maybe someone can see what I am doing wrong. Any suggestions are appreciated. Thanks.

Upvotes: 2

Views: 3261

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

In addition to what @Marco and @Erkan already cleared up:

If your table is populated already (has rows), you cannot add columns with a NOT NULL without a DEFAULT clause. New rows are added with NULL values unless you provide a different DEFAULT - thus contradicting the constraint.

In older versions of PostgreSQL (before version 8.0) you could not use the DEFAULT clause with new columns. You had to add the column, populate it and then set it NOT NULL.

Upvotes: 1

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

Your syntax is wrong. Try this [Doc]:

ALTER TABLE location_type
    ADD county character varying(255) NOT NULL,
    ADD city character varying(255) NOT NULL;

Upvotes: 5

Marco
Marco

Reputation: 57573

Try with this

ALTER TABLE location_type DROP COLUMN name;
ALTER TABLE location_type ADD county character varying(255) NOT NULL;
ALTER TABLE location_type ADD city character varying(255) NOT NULL;

or

ALTER TABLE location_type 
    DROP COLUMN name,
    ADD county character varying(255) NOT NULL,
    ADD city character varying(255) NOT NULL;

Check manual page

Upvotes: 3

Related Questions