PostgreSQL Snippets

How to query JSONB

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

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

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

SELECT * FROM users 
  WHERE metadata->>'country' IN ('Chad', 'Japan');
  
SELECT * FROM users
  WHERE metadata->'address'->>'city' = 'Melbourne';

SELECT metadata->>'country' FROM users;

• -> returns object.
• ->> coeaces into value.

How to reset sequence

Default sequence name format seems to be <table>_<column>_seq.

ALTER SEQUENCE events_serial_id_seq RESTART WITH 35730728;

Log full SQL statements in postgresql log

Edit postgres/10.5/data/postgresql.conf, add:

log_statement = 'all'			# none, ddl, mod, all