Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style

Some SQL things

Created on October 6, 2022

Updated on July 19, 2024

Tags: sql, postgres

:ID: E5F844B5-1C69-4CD6-B0D8-C785C776CB5C

# 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)

# 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

Search Results