SQL - Tips


(how to) specify condition in COUNT

SELECT COUNT(CASE WHEN is_admin IS TRUE THEN 1 END) AS admin_count FROM users;
SELECT COUNT(CASE WHEN is_admin IS TRUE THEN 1 ELSE NULL END) AS admin_count FROM users;

NOTE: don’t specify ELSE 0 in condition - all rows will be counted then!

[PostgreSQL] (how to) count occurrences of specific element in array column

  1. https://stackoverflow.com/a/26524451/3632318

count occurrences of player id 2503 in all rows (in all games):

=# select * from game_stats;

 id | game_id |     player_ids    |
----+---------+-------------------+
  1 |   17233 |  {}               |
  2 |   17235 |  {}               |
  3 |   17210 |  {2503}           |
  4 |   17213 |  {2502,2503,2503} |
SELECT
  unnested_game_stats.game_id AS game_id,
  COUNT(*) AS player_count
FROM (
  SELECT
    game_stats.game_id AS game_id,
    UNNEST(game_stats.player_ids) AS player_id
  FROM game_stats
) unnested_game_stats
WHERE unnested_game_stats.player_id = 2503

[PostgreSQL] (how to) expand multiple array columns to a set of rows

  1. https://www.postgresql.org/docs/9.5/static/functions-array.html
  2. http://www.sql.ru/forum/1112093/funkciya-unnest
  3. https://www.depesz.com/2013/07/30/waiting-for-9-4-add-sql-standard-with-ordinality-support-for-unnest-and-any-other-srf/
  4. https://www.postgresql.org/message-id/52124690.8050309%40cybertec.at

calculate total penalty time for player id 2503 in all rows (in all games):

=# select * from game_stats;

 id  | game_id | penalized_player_ids | penalty_times
-----+---------+----------------------+---------------
 908 |   40885 | {2503, 2503}         | {6,8}
 907 |   40882 | {2502, 2503}         | {2,4}

this email from pgsql-hackers mailing list contains the only (!) example of using UNNEST function with table columns instead of arrays I’ve managed to find on the Internet.

to expand multiple array columns to a set of rows so that the 1st row contains the 1st elements of all array columns, the 2nd row contains the 2nd elements of all array columns, etc. you’ve got to:

SELECT
  unnested_game_stats.game_id AS game_id,
  COALESCE(CAST(SUM(unnested_game_stats.penalty_time) AS INTEGER), 0)
    AS penalty_time_game_sum
FROM (
  SELECT
    game_stats.game_id AS game_id,
    penalties.penalized_player_id AS penalized_player_id,
    penalties.penalty_time AS penalty_time
  FROM game_stats, UNNEST(
    game_stats.penalized_player_ids,
    game_stats.penalty_times
  ) AS penalties(penalized_player_id, penalty_time)
) unnested_game_stats
WHERE unnested_game_stats.penalized_player_id = 2503

here we select from table and a set of rows returned by UNNEST function (that is passed this table array column names) - but the result is not a Cartesian product (cross join) of all sources: each table row is properly joined with its own unnested values of array columns.

should you replace array column names with arrays, you’ll get a typical cross join:

SELECT game_stats.id, first, second
FROM game_stats, UNNEST(array[1, 2], array[3, 4]) AS t(first, second)

you can get the same result by adding 2 array columns to the table and updating ALL records with {1,2} and {3,4} values in these columns.

[PostgreSQL] (how to) to find the first/last rows within a group

for example, this is how to find creation date of the youngest post for each user:

[PostgreSQL] (how to) cast JSONB object fields

  1. https://www.postgresql.org/docs/9.5/functions-json.html
  2. https://dba.stackexchange.com/a/128418

it’s not allowed to use non-text JSONB objects fields directly without casting:

postgres=# SELECT data->'item_count' > 10 FROM shops;
ERROR:  operator does not exist: jsonb > integer

=> cast these fields, say, before using in WHERE clauses: