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 June 27, 2023

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_widgeton 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;

Search Results