[HACKERS] Changing the type of timestamp columns
Hi guys, Is it safe to update the atttypid of a timestamp column to be a timestamptz column? I wish to do this on a production database, so I need to be sure! Thanks, Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Changing the type of timestamp columns
Is it safe to update the atttypid of a timestamp column to be a timestamptz column? I wish to do this on a production database, so I need to be sure! Oh, and what about indexes on them? Do I just drop them beforehand and recreate? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Changing the type of timestamp columns
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Is it safe to update the atttypid of a timestamp column to be a > timestamptz column? For sufficiently small values of "safe", sure. The problem is that unless you live in GMT zone, the interpretation of values is different: the zero reference for timestamptz is midnight GMT 2000-01-01, whereas for timestamp it's midnight your local time 2000-01-01. So if you do the above, all the stored timestamps will appear to change value by your offset from GMT. If you're planning to replace all the column entries then it won't matter, but ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Changing the type of timestamp columns
KL, > Is it safe to update the atttypid of a timestamp column to be a > timestamptz column? I wish to do this on a production database, so I > need to be sure! I wouldn't try this without running it against a test database copy first. I've already discovered that a backend change to a column data type like your describing can disrupt indexes, views, and analyze rows based on the table -- the last time I did such, I ended up having to dump and reload the database to get everything responding reliably. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Changing the type of timestamp columns
I wouldn't try this without running it against a test database copy first. I've already discovered that a backend change to a column data type like your describing can disrupt indexes, views, and analyze rows based on the table -- the last time I did such, I ended up having to dump and reload the database to get everything responding reliably. Yeah, I just remembered table types and pg_depend entries, so I'm not really keen to do it any more :/ Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
