Public Postgres Snippets

common.sql

/* Count unique entries in `column_name` */
SELECT column_name, count(*) FROM "table_name" GROUP BY column_name;

/* find entries with (NOT) numeric string */
SELECT * FROM table_name WHERE NOT column_name ~ '^\d+\.?\d+$';

/* trim in place */
UPDATE table_name SET column_name = TRIM (column_name);

/* to lower case in place */
UPDATE table_name SET column_name = LOWER(column_name)

/* remove all spaces */
UPDATE table_name SET column_name = REPLACE(column_name, ' ', '');

/* remove empty items from string array */
UPDATE table_name SET column_name = array_remove(column_name, '');

/* Find duplicate rows of `id` and `column_name` */
SELECT * FROM (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY id, column_name ORDER BY id asc) AS Row
  FROM table_name
) dups
WHERE dups.Row > 1;