Change column type and set not null

Question

How do you change the column type and also set that column to not null together?

I am trying:

ALTER TABLE mytable ALTER COLUMN col TYPE character varying(15) SET NOT NULL

This returns an error.

What is the right syntax?

Hey why not have a look at the manual: http://www.postgresql.org/docs/9.2/static/sql-altertable.html — mechanical_meat, Apr 24 '13 at 16:26
That is together (in one query), isn't possible to make this two things? — Oto Shavadze, Apr 24 '13 at 16:30

Federico Razzoli · Accepted Answer · 2013-04-24T16:57:25.640

276

This should be correct:

ALTER TABLE mytable
    ALTER COLUMN col TYPE character varying(15),
    ALTER COLUMN col SET NOT NULL

edited Apr 24 '13 at 16:57

answered Apr 24 '13 at 16:42

Federico Razzoli

2

Is there a way to apply default value for cells which have null value at once? – Eugen Konkov Aug 13 '20 at 18:32
3

@EugenKonkov Off the top of my head, `UPDATE mytable SET col = WHERE col IS NULL` – Quelklef Jan 15 '21 at 23:27
5

The inverse is here https://stackoverflow.com/q/4812933/5645755 – Sadi Sep 20 '21 at 15:24

score 10 · Answer 2 · answered Dec 29 '21 at 07:09

10

Also, if you want to REMOVE NOT NULL constrain in postgresql:

ALTER TABLE mytable 
    ALTER COLUMN email DROP NOT NULL;

answered Dec 29 '21 at 07:09

Shoniisra

2 Answers2