CODEWITHSUNDEEP
sqldatabasepostgresqlconstraintscreate-table
Stas
Stas

Reputation: 27

Postgres a timestamp column constraint from NOT NULL to NULL

I'm trying to run a migration and make basically a column "modified" which is NOT NULL to be NULL. Like there is no need to have that constraint on it, I've run yoyo migrations and have the following output

psycopg2.ProgrammingError: syntax error at or near "timestamp"
LINE 1: ALTER TABLE shop ALTER COLUMN modified timestamp NULL

Pointing to the timestamp ^

the table itself looks

CREATE TABLE shop (
    id SERIAL PRIMARY KEY,
    uuid uuid NOT NULL UNIQUE,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    deleted timestamp with time zone
);

I've tried to search the web, and found a few similar articles on stackoverflow but it didn't help, so hopefully someone here can help.

Edit:

steps = [
    step("""ALTER TABLE phrases 
    ALTER COLUMN modified TYPE timestamp,
    ALTER column modified SET NULL
;""")
]

In yoyo migration

Upvotes: 2

Views: 1657

Answers (1)

GMB
GMB

Reputation: 222582

In Postgres, you can make a column nullable with DROP NOT NULL:

ALTER TABLE shop ALTER column modified DROP NOT NULL;

If you want to change the datatype at the same time, then:

ALTER TABLE shop 
    ALTER column modified DROP NOT NULL,
    ALTER COLUMN modified TYPE timestamp
;

Demo on DB Fiddle

Upvotes: 2

Related Questions