Databases

Post Example

Operations related to database configuration, connecting, data management, and data querying.

by yhuang SQL

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

Coalesce in Postgres

by yhuang SQL

Returns the first non null value.

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

Auxiliary Statements in Postgres

by yhuang SQL

WITH provides a way to write auxiliary statements for use in a larger query.

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;

Array Contains Array in Postgres

by yhuang SQL

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

Convert Single Value Rows to Array in Postgres

by yhuang SQL

select array(select id from some.table);

List dictionaries in Postgres

by yhuang SQL

SELECT dictname FROM pg_catalog.pg_ts_dict;

# More details
\dFd+

# Configuration names
SELECT cfgname FROM pg_catalog.pg_ts_config;