A few PostgreSQL tricks

In this article, we will look at some cool, practical query tricks you can use in projects that use PostgreSQL as the DBMS:

Order results but select a specific first-row

This trick allows you to write a query with an order-by clause which allows you to choose which row should come first based on some condition, despite the presence of another order-by clause.

See the example below which will always return the company with ID = 2 as the first row despite how the other order by clause is written.

CREATE TABLE companies_test (
  id serial not null,
  company_name text not null,
  created_at timestamp default now()
);

INSERT INTO companies_test (company_name)
VALUES ('Company A'), ('Company B'),('Company C');

select id, company_name, created_at 
from companies_test
order by (id = 2) desc, created_at asc

Using string aggregates to return arrays for related records

The string_agg function is a good function in Postgres that allows you to aggregate columns after a group by operations.

Let's say you have a database of individuals who share the same first and last names and you want to capture all the dates of birth linked to that name in the database, (sorry if this is a quirky example, could only think of it as it's something I faced) you could do something like this:

CREATE TABLE IF NOT EXISTS individuals_test (
  id serial not null,
  first_name text not null,
  last_name text not null,
  date_of_birth date,
  created_at timestamp default now()
);

INSERT INTO individuals_test (first_name, last_name, date_of_birth)
VALUES ('John', 'Doe', '1970-01-01'), ('John', 'Doe', '2003-10-01'),('John', 'Doe', '1993-04-01');

 SELECT 
   concat(first_name, ' ', last_name) as full_name, 
   string_agg(date_of_birth::text, ';') as dates_of_birth 
 FROM individuals_test 
 GROUP BY concat(first_name, ' ', last_name) ORDER BY full_name

The result will look something like

| full_name | dates_of_birth                   |
| --------- | -------------------------------- |
| John Doe  | 1970-01-01;2003-10-01;1993-04-01 |

Checking if a record exists with a true or false result

Most times we have some parts in our code where we would just like to know if a record exists. Here is a pattern for a query that always guarantees a result that will be either true or false depending on the result of the subquery - this way you don't have to check if the resultset from your query is empty or not:

SELECT exists (select from users where username = :username);

CHECK constraint to validate a JSON column

Some days you may find yourself storing JSON in a database column - now the thing about JSON is that you will have to deal with a Schema somewhere, usually in your application code. Here is a simple approach to check that the JSON has at least the required top-level keys as a simple validation to ensure incorrectly formatted JSON isn't entered in your columns. You can go crazy with this and validate against a whole JSON Schema - take a look at pg_jsonschema by the Supabase team

CREATE TABLE IF NOT EXISTS user_app_settings (
  username text not null,
  settings jsonb
);

ALTER TABLE user_app_settings 
ADD CONSTRAINT check_settings_fields 
     CHECK (settings::jsonb ?& array['cover_photo', 'current_theme', 'toolbar_x', 'toolbar_y', 'version']);

 -- this insert will work because the JSON is well-formed according to our rules
 INSERT INTO user_app_settings
 VALUES ('zikani03', '{"version": "v1.0-beta", "cover_photo": "https://some.site/pic/zikani03", "current_theme":"nord", "toolbar_x": 354.5, "toolbar_y": 100}'::jsonb);

 -- this insert below should fail since we don't have the "version" field 
 INSERT INTO user_app_settings
 VALUES ('john', '{"no_version": "v1.0-beta", "cover_photo": "https://some.site/pic/john", "current_theme":"nord", "toolbar_x": 354.5, "toolbar_y": 100}'::jsonb);

Conclusion

In this article, I shared a few tricks you can use in your projects that use Postgresql, though some of them would also likely work in other DBMSs if you found the equivalent functions. I hope to share more as I keep learning...

Thanks for reading.