Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- [
- { col1: a, col2: 5, col3: 1, col4: one},
- { col1: b, col2: 6, col3: 2, col4: two},
- { col1: c, col2: 7, col3: 3, col4: three},
- { col1: d, col2: 8, col3: 4, col4: four},
- ]
- col1 (varchar)| col2 (integer) | col3 (integer) | col4 (varchar)
- -----------------+----------------+--------------------+------------------
- a | 5 | 1 | one
- b | 6 | 2 | two
- c | 7 | 3 | three
- d | 8 | 4 | four
- CREATE OR REPLACE FUNCTION UPDATE_TABLE_FUNC (
- arrayOfValues TEXT[]
- )
- RETURN VOID AS $$
- BEGIN
- UPDATE table SET (col1, col2, col3, col4) = ($1)
- END;
- $$ LANGUAGE plpgsql;
- SELECT * FROM jsonb_to_recordset('[
- { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
- { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
- { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
- { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
- ]'::jsonb) AS t (col1 text, col2 integer, col3 integer, col4 text);
- col1 │ col2 │ col3 │ col4
- ──────┼──────┼──────┼───────
- a │ 1 │ 1 │ one
- b │ 2 │ 2 │ two
- c │ 3 │ 3 │ three
- d │ 4 │ 4 │ four
- CREATE TABLE inputtable (col1 text, col2 integer, col3 integer, col4 text);
- SELECT * FROM jsonb_populate_recordset(NULL::yourtable, '[
- { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
- { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
- { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
- { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
- ]'::jsonb);
- col1 │ col2 │ col3 │ col4
- ──────┼──────┼──────┼───────
- a │ 1 │ 1 │ one
- b │ 2 │ 2 │ two
- c │ 3 │ 3 │ three
- d │ 4 │ 4 │ four
- WITH source AS (SELECT * FROM jsonb_populate_recordset [...])
- UPDATE yourtable
- SET col1 = s.col1, col2 = s.col2
- FROM source AS s
- WHERE col3 = s.col3;
- databases=# set var `cat /home/user/file.json`
- databases=# echo :'var';
- databases=# create table code (name text not null, lastname text not null);
- the names of the columns have to be the same as your json file, for example:
- [
- {
- "name": "enrique",
- "lastname": "vega"
- },
- {
- "name": "jose",
- "lastname": "mendez"
- }
- ]
- databases=# insert into nametable select * from jsonb_populate_recordset(null::nametable,:'var'::jsonb);
Add Comment
Please, Sign In to add comment