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;