PostgreSQL: change column type from boolean

Question

I have table t1 and column c1 of type boolean NULL. All values in the column are NULL. However PostgreSQL won't let me change the type of the column this way:

ALTER TABLE "t1"
ALTER "c1" TYPE timestamp;

* ERROR: column "c1" cannot be cast to type timestamp without time zone

How can I change the type? Thank you

Bruno · Accepted Answer · 2013-07-25 22:09:18Z

10

If all the values in your columns are indeed null, simple add USING NULL: there's no difficult conversion to make.

ALTER TABLE "t1"
ALTER "c1" TYPE timestamp USING NULL;

answered Jul 25, 2013 at 22:09

Bruno

123k33 gold badges278 silver badges389 bronze badges

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

1 Comment

Craig Ringer Over a year ago

Pretty sure this will merrily throw away non-null data if there is any, so do be careful.

snorthway · Accepted Answer · 2013-07-25 22:00:56Z

0

I think you just have to give it a time zone. This worked for me:

ALTER TABLE t1
    ALTER COLUMN c1 SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch';

answered Jul 25, 2013 at 22:00

snorthway

5861 gold badge10 silver badges15 bronze badges

1 Comment

Bruno Over a year ago

Your approach (a) doesn't use the required column type (timestamptz instead of timestamp, it's not necessarily a bad thing, on the contrary, but that's not the same as in the question) and (b) fills all the values to "epoch", which is "1-Jan-1970 00:00" instead of being null. (Try SELECT timestamp with time zone 'epoch';: that's what USING is going to use.)

Collectives™ on Stack Overflow

PostgreSQL: change column type from boolean

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related