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?
-
2Hey why not have a look at the manual: postgresql.org/docs/9.2/static/sql-altertable.htmlmechanical_meat– mechanical_meat2013-04-24 16:26:06 +00:00Commented Apr 24, 2013 at 16:26
-
That is together (in one query), isn't possible to make this two things?Oto Shavadze– Oto Shavadze2013-04-24 16:30:24 +00:00Commented Apr 24, 2013 at 16:30
Add a comment
|
3 Answers
This should be correct:
ALTER TABLE mytable
ALTER COLUMN col TYPE character varying(15),
ALTER COLUMN col SET NOT NULL
3 Comments
Eugen Konkov
Is there a way to apply default value for cells which have null value at once?
Quelklef
@EugenKonkov Off the top of my head,
UPDATE mytable SET col = <default value> WHERE col IS NULL
Sadi
The inverse is here stackoverflow.com/q/4812933/5645755
Also, if you want to REMOVE NOT NULL constrain in postgresql:
ALTER TABLE mytable
ALTER COLUMN email DROP NOT NULL;
1 Comment
Jakub Turcovsky
This doesn't answer the question.
Assuming you only wanted to change the nullability of the column, which would be a more frequent operation than changing the type, you don't need such a complex syntax. There is an easier way.
alter table my_schema.my_table
alter column my_column
set not null;
This is the same as in Oracle DB, and probably others as well.
You don't need to provide the type information for the column, again. The database already has the type information for your column, you can simply change the nullability of it using the above syntax.
If you need to change the type as well, then it is probably best to do both operations separately. It will be easier to fix and diagnose a problem in the event that something goes wrong.
In my own personal experience, changing the type of a column is quite a rare thing to want to do, whereas changing the nullability is more often required, sometimes because nulls do not work in expected ways when included as part of a unique key, for example. But this is really beyond the scope of your question.
