20221006121926-some_sql_things.org

Some SQL things

Join only on first record

Shameless borrowed from https://www.sisense.com/blog/4-ways-to-join-only-the-first-row-in-sql/

Correlated subquery

This loops since the subquery runs once for each record in the outer query

select * from users
  join widgets on widgets.id = (
    select id from widgets
    where widgets.user_id = users.id
    order by created_at desc
    limit 1)

Complete subquery

PostgreSQL DISTINCT ON with different ORDER BY

select * from users
  join (
    select distinct on (user_id) *
      from widgets
      order by user_id, created_at desc)
    as most_recent_user_widget on users.id = most_recent_user_widget.user_id

If not using PostgreSQL (ie DISTINCT ON)

We can use the ROW_NUMBER function with ordering and selecting the first row.

See also https://stackoverflow.com/a/49980276

select * from users
  join (
    select * from (
        select *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at desc) rn
        from widgets) w where w.rn = 1
      ) as most_recent_user_widgeton on users.id = most_recent_user_widget.user_id

Date / Time stuff

Some length of time ago

PostgreSQL

NOW() - '1 day'::INTERVAL

MSSQL

DATEADD(day, -1, GETDATE())

Extract date part

MSQL

SELECT DATEPART(MINUTE, CURRENT_TIMESTAMP)

Convert Datetime to Date

MSSQL

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

OR

SELECT CONVERT(DATE, GETDATE())

JSON

bytea

If you need to insert into a bytea column, one approach would be to hexdump the file the insert the it using the convert utility.

xxd -ps /path/to/json | tr -d '\n' | pbcopy

update sometable set "json" = convert('PASTE_HEXDUMP_FROM_ABOVE_STEP_HERE', 'hex') where id = 1

Schema comments

• Comments can be added to any database object.
• Only one comment allowed per object.
• To remove a comment, just replace the comment with NULL

Add a comment to a Table

COMMENT ON TABLE horsey_coconuts IS 'Coconuts in Mercia';

View with

\dt+ table_name

Add comments to a column

COMMENT ON COLUMN horsey_coconuts.swallow_speed IS 'The air speed the swallow who carried the coconut';

View with

\d+ table_name

CTE (with query)

Use case 1: data migrations

Store deleted records in a temp table in case something needs to be rolled back. Return * from the delete operation which populates the CTE.

WITH deletes AS (
  DELETE FROM widgets WHERE widget_type_id IN (
    SELECT id FROM widget_types wt WHERE wt.name= 'shiny'
  )
  RETURNING *
)
SELECT *
  INTO temp_delete_widgets_20230627
  FROM deletes;

Use case 2: Updates where you’d want to use a join

WITH posts_to_update AS (
  SELECT id FROM posts p
  JOIN authors a ON a.id = p.author_id
  WHERE a.name IN ('dude1', 'dude1')
  )
UPDATE posts
    active = false
FROM posts_to_update
WHERE posts.id = posts_to_update.id;

Constraints (PG)

• See also ActiveRecord Migrations Cheatsheet for some stuff on CHECK CONSTRAINT
• See also https://www.crunchydata.com/blog/enums-vs-check-constraints-in-postgres for a case for preferring CHECK CONSTRAINT over ENUM. tl;dr there much easier to update since you don’t have to modify the existing data.
• See also https://www.crunchydata.com/blog/postgres-constraints-for-newbies as an easy to read reference on PG constraints

deferring constraints

Some constraints support deferrability (checks do not)

Some use cases you might want to only perform the check at the end of the transaction. Use DEFERABLE for this.

alter table things add constraint foo_unique (foo) deferrable

Or you can defer constraints on demand within a transaction. See also https://www.postgresql.org/docs/16/sql-set-constraints.html

See also https://hashrocket.com/blog/posts/deferring-database-constraints for more advanced example

EXCLUSION CONSTRAINT

These are like unique constraints, but allows you to define uniqueness criteria way beyond simple equality of a unique index.

See also https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE