• psql
• jsonb
• Sample queries

Command line

$ psql
  -c    # Execute a command
  -l    # List databases
  -h    # Host
  -d    # Database name
  -U    # Username
  -W    # Password

Console

> \?    # Help
> \l    # List databases
> \c    # Connect to a database
> \dt   # List tables
> \du   # List roles
> \d    # List tables, views, sequences (\dS+)
> \d Name   # Describe table, views, sequences (\dS+)
> SET search_path = some_schema # set default schema to query

Official Documentation

Search and select keys in jsonb columns

  SELECT id, foodfacts->'product_name', foodfacts->'brands'
  FROM products
  WHERE foodfacts @> '{"id": "12345"}';
  # WHERE foodfacts->>'id' = '12345';

List all keys of a record's jsonb column

  SELECT jsonb_object_keys(foodfacts) FROM products;

Display jsonb column as a table

  SELECT * FROM json_each((
    SELECT foodfacts::json from products
  ));
<br />

==== Sample queries ====

Find the closest representations in time, from now
{{{class="sql"
SELECT representations.id, events.id as event_id,
       now(),
       representations.start_date_time,
       (start_date_time - now()) as time_diff
FROM representations
JOIN events ON representations.event_id = events.id
WHERE events.is_display IS true
ORDER BY
  abs(
    extract(
      epoch FROM
        (start_date_time - now())))
LIMIT 10;
}}