Update timezone columns in a Postgres DB

Question

I have started creating a product database using timestamp without timezone. Then, realizing my error, I started using timestamp with timezone. Now I'd like to unify this to the latter.

Question: Is it possible in an existing Postgres 8.4 DB already containing data to convert all the columns of type timestamp without TZ to ones with TZ?

The best solution would be a script that would do this in one execution (of course). Even a script that would fix a single column at a time would be great. The problem is that a naïve ALTERing the column fails on some existing VIEWs that use it in output (though I fail to see why it is bad in this case - it's just widening the output type a bit).

Richard Huxton · Accepted Answer · 2013-12-09 18:39:31Z

1

You want ALTER TABLE ... ALTER COLUMN ... TYPE ... USING (...) which does what you would expect. You will need to decide what timezone these times are in, and supply the suitable AT TIME ZONE expression for your USING clause.

These will ALTERs will rewrite each table, so allow for that. You may want to CLUSTER them afterwards.

However, you seem to think that the two types are interchangeable. They are not. That is why you need to drop and rebuild your views. Also you will want to rewrite any applications appropriately too.

If you can't see why they are different, make a good hot cup of tea or coffee, sit down and read the time & date sections of the manuals and spend an hour or so reading them thoroughly. Perhaps some of the Q&As here too. This is not necesarily a minor change. I'd be especially wary of any daylight-saving / Summer shifts in whatever time zone(s) you decide apply.

answered Dec 9, 2013 at 18:39

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

2 Comments

vektor Over a year ago

Thanks, I will try this ASAP. My current timestamps w/ TZ have just one constant TZ anyway (ok 2 if I count summer time), so the upgrade of the old ones is very straightforward. That's the reason I'm fixing it now before it's too late :)

vektor Over a year ago

In the end I found all the columns with the incorrect type using a script, then tried the ALTER TABLE...ALTER COLUMN in a rollbacked transaction to find all the dependent objects - it was a couple of VIEWs. Eventually I prepared a script that would drop these VIEWs, update the column types and re-create the VIEWs...

Collectives™ on Stack Overflow

Update timezone columns in a Postgres DB

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