Some SQL things
Table of Contents
: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;