sql - PostgreSQL 在 json 中插入动态值

Question

有两个表:

CREATE TABLE user (
  id bigserial,
  name varchar(255),
  address varchar(255)
)

CREATE TABLE user_history (
  id bigserial,
  user_id int8
  user_json json,
  create_date timestamp
)

user.id 由 DEFAULT 生成。

我想为正在创建的用户创建历史记录，但我不知道如何将生成的 user.id 传递给 json。像这样:

INSERT INTO user_history
VALUES (
  DEFAULT,
  (SELECT id FROM user WHERE name = 'Some name'),
  '{
    "id": GENERATED ID HERE,
    "name": "Some name",
    "address": "Some address"
   }',
  '2010-01-01 00:00:00'
);

Postgres 10

Answer 1

在顶部:

with c as (SELECT id FROM user WHERE name = 'Some name')
INSERT INTO user_history (user_id,user_json,create_date)
select  id, concat(
  '{
    "id": ',id,',
    "name": "Some name",
    "address": "Some address"
   }')::json,
  '2010-01-01 00:00:00'
from c
;

也可能使用 json_build_object 来生成 json？..

here' working example

更新

我没有意识到 json 中的所有键/值都来自用户表，下面是对它的较短查询:

INSERT INTO user_history (user_id,user_json,create_date)
select  id, json_build_object('id',id,'name',name,'address',address), '2010-01-01 00:00:00'
from "user"

http://sqlfiddle.com/#!17/19a8e/3

保存 user_json 也没有多大意义 - 你总是可以通过 FK 在 user_history.user_id 上获取它

t=# select to_json(u) from "user" u;
                  to_json
--------------------------------------------
 {"id":1,"name":"Some name","address":null}