Postgres Cheat Sheet

Postgres Cheat Sheet.sql

-------------------------------------------------
-- READ OPERATIONS
-------------------------------------------------

-- Find records that exactly match a key value pair in a JSONB column
SELECT data->>'key' FROM target_table WHERE data->>'key' = 'value';

-- Find records that loosely match a key value pair in a JSONB column
SELECT data->>'key' FROM target_table WHERE data->>'key' ILIKE '%value%';

-- Find records that match an object shape contained in an array in a JSONB column
SELECT data->>'items' FROM target_table WHERE data->'items' @> '[{"someKey": "someValue"}]';

-- Find records that exactly match a nested key value pair in a JSONB column
SELECT data->'nest'->>'key' FROM target_table WHERE data->'nest'->>'key' = 'value';

-------------------------------------------------
-- INSERT OPERATIONS
-------------------------------------------------

-- Copy records from another table
INSERT INTO destination_table(column_a, column_b)
SELECT DISTINCT s.source_column_a AS column_a, s.source_column_b AS column_b
FROM source_table s;

-------------------------------------------------
-- UPDATE OPERATIONS
-------------------------------------------------

-- Add a static key value pair to a JSONB column
UPDATE target_table SET data = data || '{"newKey": "newValue"}';

-- Add a static nested key value pair into a JSONB column
UPDATE target_table SET data = jsonb_set(data, '{topKey, nestedKey, nestedNestedKey}', '"value"');

-- Remove a key from a JSONB column
UPDATE target_table SET data = data - 'keyToRemove';

-- Copy and merge key value pairs into a JSONB column from another table
UPDATE destination_table SET
data = data || t.moredata
FROM (
  SELECT DISTINCT jsonb_build_object('destinationKey', s.data->>'sourceKey') AS moredata, d.id AS id
  FROM destination_table d
  LEFT JOIN source_table s ON s.data->>'sourceJoinKey' = d.data->>'destinationJoinKey'
  WHERE s.data->>'sourceKey' IS NOT NULL
) AS t
WHERE destination_table.id = t.id AND t.moredata IS NOT NULL;
-- NOTE: You can validate the inbound data before committing the changes by executing the subquery