时间戳

首页 > 解决方案 > How to update a column using another column value?

sql - How to update a column using another column value?

问题描述

I have an empty table with rows that have timestamp and file_url columns.

When I add a row, timestamp is filled with Postgres current_timestamp value. At the same moment, I want to put into file_url the same value, but formatted differently.

timestamp can have this format: 2018-07-26 08:37:56.749125 and for file_url I want to have something like http://somedomain.com/files/20180726083756.749125.jpg.

How can I do it in one run?

标签： sqlpostgresqlsql-update

解决方案

您可以尝试TO_CHAR在时间戳上使用该函数：

select to_char(timestamp '2018-07-26 08:37:56.749125', 'YYYYMMDDHH24MISS.MS');

这将输出20180726083756.749. 如果您想构建一个 URL，那么您也可以使用字符串连接来实现，例如

INSERT INTO yourTable (ts_col, file_url)
SELECT
    current_timestamp,
    'http://somedomain.com/files/' ||
    TO_CHAR(current_timestamp, 'YYYYMMDDHH24MISS.MS') || '.jpg';

推荐阅读