Как обновить объект json в Postgres на текущее время в эпоху

Question

Мне нужно обновить объект json для поля lastUpdated, указав текущее время в эпоху. Я делаю следующее, но это не удается:

UPDATE data SET data = data  || '{"lastUpdated": extract(epoch from current_timestamp) }'
 WHERE dtype='Employee' and data->>'id' = '1234';

Следующее само по себе не работает:

UPDATE data SET data = data  || '{"lastUpdated": current_timestamp }'
 WHERE dtype='Employee' and data->>'id' = '1234';

Я получаю "неверный синтаксис ввода для типа JSON".

Обратите внимание, что следующие настройки работают, когда я устанавливаю пользовательское время эпохи:

UPDATE data SET data = data  || '{"dateAdded":1447502107000 , "lastUpdated":1447502107000}' WHERE dtype='Employee' and data->>'id' = '1234';

Answer 1

Во-первых, вы должны изменить тип данных столбца data в jsonb, как оператор конкатенации|| не работает с json,

alter table data alter data type jsonb;

Тогда используйте функцию jsonb_build_object():

UPDATE data SET data = data || jsonb_build_object('lastUpdated', extract(epoch from current_timestamp))
WHERE dtype='Employee' and data->>'id' = '1234';

Если вы не хотите изменять тип столбца, вы должны использовать приведение:

UPDATE data SET data = data::jsonb || jsonb_build_object('lastUpdated', extract(epoch from current_timestamp))
WHERE dtype='Employee' and data->>'id' = '1234';

---

Обновить

Выберите один из двух вариантов, чтобы округлить или избавиться от точности отметки времени:

select
    extract(epoch from current_timestamp),
    replace(extract(epoch from current_timestamp::timestamp(3))::text, '.', ''),
    concat(extract(epoch from current_timestamp::timestamp(0))::text, '000')

    date_part     |    replace    |    concat     
------------------+---------------+---------------
 1533560841.14562 | 1533568041146 | 1533568041000
(1 row) 

В качестве альтернативы:

select
    extract(epoch from current_timestamp),
    extract(epoch from current_timestamp(3))* 1000 as v1,
    extract(epoch from current_timestamp(0))* 1000 as v2

Answer 2

Это не работает, потому что extract - это функция, и используя ее внутри '', вы в основном отправляете строку, которая возвращает вам метку времени

UPDATE data SET data = data  || '{"lastUpdated": extract(epoch from 
current_timestamp) }'
WHERE dtype='Employee' and data->>'id' = '1234';

Между тем это работает, потому что вы храните так, как это должно хранить

UPDATE data SET data = data  || '{"dateAdded":1447502107000 , 
"lastUpdated":1447502107000}' WHERE dtype='Employee' and data->>'id' = '1234';

Хотя я бы сказал, чтобы попробовать путь Клина, но решение того, что вы спрашиваете, также может быть (убедитесь, что вы измените тип данных на JSONB):

UPDATE data SET data = data  || '{"lastUpdated":' || extract(epoch from 
current_timestamp)::text || '}'
WHERE dtype='Employee' and data->>'id' = '1234';