psql

Select value of jsonb column in PostgreSQL as text

Snippet Written with a loving hand by kitt some time around 17:10 on 28 March 2024

Select the fields in a JSONB field in postgres with ->>. Use -> for selecting in the form

SELECT id, serialized_params FROM good_jobs WHERE serialized_params->>arguments = '[111777111]' LIMIT 1;

Postgres export to a CSV

Snippet Posted by kitt at 10:31 on 16 November 2023

Easy export to a CSV file, with headers.

# sql
\COPY (SELECT * FROM report_view_table) TO 'export.csv' DELIMITER ',' CSV HEADER;
 
# bash
psql $DATABASE -c "\COPY (SELECT * FROM report_table) TO 'export.csv' DELIMITER ',' CSV HEADER;"

Postgresql Add UTF8 database

Snippet Posted by kitt at 14:50 on 24 August 2016

$ createdb -E UTF8 -T template0 --locale=en_US.UTF-8 <name>
 
# to figure out actual locale value (sometimes listed as utf8), or UTF8, use locale
$ locale -a | grep en