Postresql

cli tools

pg_dump: save database

pg_dump -Fc ${db_name} > ${dump_name}.dmp

dropdb : drop database

dropdb -U <user> ${db_name}

createdb : create database

createdb -u <user> ${db_name}

pg_restore : restore database

pg_restore -d ${db_name} ${dump_name}.dmp

duplicate database

$ su -
$ su - postgres   // connect as postgre user
$ psql            // conect to database
~=> CREATE DATABASE ${db_name2} WITH TEMPLATE ${db_name1} OWNER rcv;

psql

• connect to database

psql -U ${user} ${db_name}

• display result in page mode

\pset pager on

• change database

\c <other_db>

Database informations

• sessions connected to database

select * from pg_stat_activity;

• get information about database

select table_name, column_name
from information_schema.columns
where column_name like '%xxx%' order by table_name, column_name;

• display enumeration type

select n.nspname as enum_schema, t.typname as enum_name, e.enumlabel as enum_value 
from pg_type t join pg_enum e
on t.oid = e.enumtypid join pg_catalog.pg_namespace n
on n.oid = t.typnamespace 
where t.typname = ${type};

SQL language

alter table

add a column

ALTER TABLE personne ADD COLUMN parent_id INTEGER REFERENCES personne(id);

comment

COMMENT ON TABLE personne IS 'gestion des personnes';
COMMENT ON COLUMN personne.date_naissance IS 'date de naissance';

Postgresql / JSON : sheet cheat

Merci à Hackernoon.

• Select items by the value of a first level attribute (#1 way)

SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';

• Select items by the value of a first level attribute (#2 way)

SELECT * FROM users WHERE metadata->>'country' = 'Peru';

• Select item attribute value

SELECT metadata->>'country' FROM users;

• Select only items where a particular attribute is present

SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;

• Select items by the value of a nested attribute

SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";
SELECT * 
  FROM users 
  WHERE metadata @> '{"company":{"name": "Mozilla"}}';

• Select items by the value of an attribute in an array

SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';

• IN operator on attributes

SELECT * FROM users 
  WHERE metadata->>'countries' IN ('Chad', 'Japan');

• Insert a whole object

UPDATE users SET metadata = '{"country": "India"}';

• Update or insert an attribute

UPDATE users SET metadata = metadata || '{"country": "Egypt"}';

• Removing an attribute

UPDATE users SET metadata = metadata - 'country';