Postgres a timestamp column constraint from NOT NULL to NULL

Question

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

GMB · Accepted Answer · 2020-06-25 23:41:21Z

2

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

answered Jun 25, 2020 at 23:41

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stas Over a year ago

and for the roleback would i do something like ALTER TABLE shop ALTER column modified SET NOT NULL;?

GMB Over a year ago

@Stas: yes, that's how you do it.

Collectives™ on Stack Overflow

Postgres a timestamp column constraint from NOT NULL to NULL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related