PostgreSQL tips

Update fields in JSONB format

Use the jsonb_set(COLUMN_NAME, PATH_TO_CHANGE, VALUE) function. The value must be a valid value in JSON (ie use '500' for numbers, 'true' for boolean and '"str"' for strings). Examples:

-- Set the postcode field to the 92600 integer.
UPDATE sales SET address = jsonb_set(address, '{postcode}', '92600') WHERE id = 47880;
-- Set a nested field to the "FRA" value. Note: the nested object must exists.
UPDATE sales SET address = jsonb_set(address, '{country,code}', '"FRA"');
-- Set a value at a given position in an array (an object must exist at this position).
UPDATE sales SET phones = jsonb_set(phones, '{0,number}', '"06"');

CSV export/import

To export content of a table to a CSV file (eg to re-import it in a different database on a different server). Update the query and connection options to fit your needs:

psql -U user -c 'COPY (SELECT * FROM sales WHERE id = 110683) To STDOUT WITH CSV HEADER;' database > /tmp/sales.csv

To import the data from a CSV file, you need to specify the columns you want to import. To find them, run head -n 1 /tmp/sales.csv to get the first lines of the CSV which contains the headers. You can then run the command below.

psql -U user database -c "COPY sales(id,organization_id,date,name,address) FROM STDIN WITH CSV HEADER;" < /tmp/sales.csv

Note 1: it should be possible to import the data with psql -U user database -c "COPY sales(id,organization_id,date,name,address) FROM '/tmp/sales.csv' WITH CSV HEADER;". However, when I tried that, I got: ERROR:  could not open file "/tmp/sale.csv" for reading: No such file or directory even though the file existed.

Note 2: you can also achieve this with pgAdmin with you want a nice GUI.


Pages

blogroll

social

>