Início / dba / Perguntas / 134385
Accepted
GTF
Asked: 2016-04-06 03:18:27 +0800 CST

Converter POSTGRES TIMESTAMP para TIMESTAMPTZ

  • 772

Eu tenho um banco de dados de série temporal de tamanho decente (~ 50k linhas) em execução no Postgres, com alguns outros dados estruturados (em outra instância de banco de dados) que são muito menores.

Estupidamente, quando inicialmente projetei a coisa, tinha todos os campos como TIMESTAMP WITHOUT TIME ZONE, e agora estou pagando por isso com bugs irritantes relacionados ao fuso horário. Eu quero que tudo seja explícito, então quero converter o campo para TIMESTAMP WITH TIME ZONE. Eu percebo que isso não armazena informações extras, e todos os meus carimbos de data/hora já estão em UTC, então a migração deve ser trivial, mas eu queria saber se há alguma complicação/bloqueio de bloqueio potencial que se mostrará problemático (este é um problema de produção banco de dados com clientes que dependem dele)?

postgresql migration

2 respostas

  1. Best Answer

    Preste atenção para que o fuso horário correto (UTC no seu caso) seja aplicado durante a conversão. Se você não for explícito sobre isso, o fuso horário da sessão atual será presumido - geralmente não o UTC.

    ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';

    Verifique também um possível padrão de coluna para sanidade. Qualquer expressão que trabalhe com tipo de dados timestamp(como LOCALTIMESTAMPou now()::timestamp) está sujeita ao mesmo problema. Mudar:

    ALTER TABLE tbl ALTER ts_column SET DEFAULT now();  -- or current_timestamp

    Obviamente, as instruções gravadas na tabela também precisam ser usadas timestamptzagora - ou você terá outra instância do mesmo problema com a conversão automática do tipo timestamp [without time zone].

    Como este é um banco de dados de produção, é melhor fazer tudo em uma única transação para evitar condições de corrida - ou mesmo uma única instrução:

    ALTER TABLE tbl
  ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC'
, ALTER ts_column SET DEFAULT now();

    Fundamentos:

    • 30

  2. Gostaria apenas de adicionar uma nota de advertência à resposta de Erwin Brandstetter (não foi possível comentar porque não há reputação suficiente): se SET DEFAULT now()é um bom padrão realmente depende da coluna específica.

    Por exemplo, se sua coluna for deleted_at, você deve evitar o SET DEFAULT now().

    • 1

relate perguntas