PostgreSQL INSERT dynamic value in json

Question

There are two tables:

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 is generated by DEFAULT.

I want to have a history record for the user being created, but i don't know, how to pass generated user.id to json. Something like this:

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

Vao Tsun · Accepted Answer · 2017-12-01 12:49:31Z

2

on the top:

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
;

also maybe use json_build_object for generating json?..

here' working example

update

I did not realyze all keys/values in json come from user table, below is shorter query for it:

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

Also saving user_json does not make much sence - you can always get it by FK on user_history.user_id

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

edited Dec 1, 2017 at 12:49

answered Dec 1, 2017 at 11:45

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Clodoaldo Neto Over a year ago

You are missing the from id

Vao Tsun Over a year ago

@ClodoaldoNeto yes - thank you. I should have checked my code better

efitapia Over a year ago

@VaoTsun i don't quite understand how json_build_object works. I would be very glad to pass the whole row from select, but i get ERROR: subquery must return only one column. Same thing with row_to_json. Is there a way to convert whole result to json?

Vao Tsun Over a year ago

@gorodkovskaya I added wrking example with your exact schema and my exact statement - can't reproduce errr - rows get inserted.

efitapia Over a year ago

@VaoTsun I do json_build_object((SELECT * FROM user WHERE name = 'Some name')). I guess I use the function wrong

|

Collectives™ on Stack Overflow

PostgreSQL INSERT dynamic value in json

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related