0

I am converting "timestamps without timezone" columns to "timestamps with timezone" on a number of tables. These are large tables and I am going to use batching and other approaches to reduce the time the tables are exclusively locked while data is converted.

Once this procedure is complete, I want to follow up with an application deploy, that uses migration scripts to keep the schema in sync.

When these scripts run I want to check if a column type is already "timezone with timestamp before altering it"?

How can I conditionally carry out the alteration based on a column type check and skip it if it is already a certain type?

5
  • 1
    Why worry? Behind the scenes, PostgreSQL may very well do nothing if the column's data type is already what you're trying to set it to? Why don't you try with a (reasonably sized) sample? Jul 2 at 11:20
  • To be honest, I never thought of this. I'll try it out.
    – mikelus
    Jul 2 at 13:19
  • 1
    Thinking about this - you could ask on one of the PostgreSQL mailing lists - questions about server source code are not usual dba questions - having said that, there are people who actually contribute to the system who hang out here and there are those who know their way around the source code. Jul 2 at 13:25
  • I just checked it out against a couple of tables. 1 with 15 million rows and one with 40 million rows and they both took under 100 ms when I attempted to use alter to change an already converted row, so they must be just skipping the operation.
    – mikelus
    Jul 2 at 13:28
  • Do you try with a changed row? TIMESTAMPTZ -> TIMESTAMP (or vice versa - try both...)! Write it up an answer with numbers and I'll upvote it! \timing... :-) Jul 2 at 13:32
1

To recreate Vérace suggestion and show they they are correct:

create table if not exists check_change_type ( column_to_change_to_tz timestamp without time zone ) [2021-07-02 14:43:57] completed in 100 ms

insert into check_change_type select clock_timestamp() from generate_series(1, 40000000)

[2021-07-02 14:48:13] 40,000,000 rows affected in 4 m 5 s 925 ms

ALTER TABLE check_change_type ALTER column_to_change_to_tz TYPE timestamptz USING column_to_change_to_tz AT TIME ZONE 'UTC'

[2021-07-02 14:53:29] completed in 3 m 52 s 416 ms

ALTER TABLE check_change_type ALTER column_to_change_to_tz TYPE timestamptz

[2021-07-02 14:54:09] completed in 97 ms

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.