Skip to content

Instantly share code, notes, and snippets.

@karladler
Last active March 13, 2023 15:42
Show Gist options
  • Save karladler/220721ff1f3da8434629d21fc3f803c7 to your computer and use it in GitHub Desktop.
Save karladler/220721ff1f3da8434629d21fc3f803c7 to your computer and use it in GitHub Desktop.
Public Postgres Snippets
/* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment