CODEWITHSUNDEEP
sqlpostgresqlsql-update
Sergei Basharov
Sergei Basharov

Reputation: 53920

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?

Upvotes: 0

Views: 55

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522674

You may try using the TO_CHAR function on your timestamp:

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

This would output 20180726083756.749. If you want to build a URL, then you may also do that with string concatenation, e.g.

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

Upvotes: 1

Related Questions