Databases

# Dump database into a SQL-script file:
pg_dump {{db_name}} > {{output_file.sql}}

# Same as above, customize username:
pg_dump -U {{username}} {{db_name}} > {{output_file.sql}}

# Same as above, customize host and port:
pg_dump -h {{host}} -p {{port}} {{db_name}} > {{output_file.sql}}

# Dump a database into a custom-format archive file:
pg_dump -Fc {{db_name}} > {{output_file.dump}}

# Dump only database data into an SQL-script file:
pg_dump -a {{db_name}} > {{path/to/output_file.sql}}

# Dump only schema (data definitions) into an SQL-script file:
pg_dump -s {{db_name}} > {{path/to/output_file.sql}}

select id from (values(0)) zero left join app.tag on name in ('foo', 'bar');

SELECT COALESCE (1, 2);         // 1
SELECT COALESCE (NULL, 2 , 1);  // 2

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

select ARRAY[1,4,3] @> ARRAY[3,1,3];   // true
select ARRAY[1,4,3] @> ARRAY[2,1,3];   // false

select array(select id from some.table);

pg_config --sharedir

SELECT dictname FROM pg_catalog.pg_ts_dict;

# More details
\dFd+

# Configuration names
SELECT cfgname FROM pg_catalog.pg_ts_config;