sql-queries-postgresql-including-aurora-rds-supabase-neon

name	sql-queries-postgresql-including-aurora-rds-supabase-neon
description	Sub-skill of sql-queries: PostgreSQL (including Aurora, RDS, Supabase, Neon) (+1).
version	1.0.0
category	data-analytics
type	reference
scripts_exempt	true

PostgreSQL (including Aurora, RDS, Supabase, Neon) (+1)

PostgreSQL (including Aurora, RDS, Supabase, Neon)

Date/time:

-- Current date/time
CURRENT_DATE, CURRENT_TIMESTAMP, NOW()

-- Date arithmetic
date_column + INTERVAL '7 days'
date_column - INTERVAL '1 month'

-- Truncate to period
DATE_TRUNC('month', created_at)

-- Extract parts
EXTRACT(YEAR FROM created_at)
EXTRACT(DOW FROM created_at)  -- 0=Sunday

-- Format
TO_CHAR(created_at, 'YYYY-MM-DD')

String functions:

-- Concatenation
first_name || ' ' || last_name
CONCAT(first_name, ' ', last_name)

-- Pattern matching
column ILIKE '%pattern%'  -- case-insensitive
column ~ '^regex_pattern$'  -- regex

-- String manipulation
LEFT(str, n), RIGHT(str, n)
SPLIT_PART(str, delimiter, position)
REGEXP_REPLACE(str, pattern, replacement)

Arrays and JSON:

-- JSON access
data->>'key'  -- text
data->'nested'->'key'  -- json
data#>>'{path,to,key}'  -- nested text

-- Array operations
ARRAY_AGG(column)
ANY(array_column)
array_column @> ARRAY['value']

Performance tips:

• Use EXPLAIN ANALYZE to profile queries
• Create indexes on frequently filtered/joined columns
• Use EXISTS over IN for correlated subqueries
• Partial indexes for common filter conditions
• Use connection pooling for concurrent access

---

Snowflake

Date/time:

-- Current date/time
CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()

-- Date arithmetic
DATEADD(day, 7, date_column)
DATEDIFF(day, start_date, end_date)

-- Truncate to period
DATE_TRUNC('month', created_at)

-- Extract parts
YEAR(created_at), MONTH(created_at), DAY(created_at)
DAYOFWEEK(created_at)

-- Format
TO_CHAR(created_at, 'YYYY-MM-DD')

String functions:

-- Case-insensitive by default (depends on collation)
column ILIKE '%pattern%'
REGEXP_LIKE(column, 'pattern')

-- Parse JSON
column:key::string  -- dot notation for VARIANT
PARSE_JSON('{"key": "value"}')
GET_PATH(variant_col, 'path.to.key')

-- Flatten arrays/objects
SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f

Semi-structured data:

-- VARIANT type access
data:customer:name::STRING
data:items[0]:price::NUMBER

-- Flatten nested structures
SELECT
    t.id,
    item.value:name::STRING as item_name,
    item.value:qty::NUMBER as quantity
FROM my_table t,
LATERAL FLATTEN(input => t.data:items) item

Performance tips:

• Use clustering keys on large tables (not traditional indexes)
• Filter on clustering key columns for partition pruning
• Set appropriate warehouse size for query complexity
• Use RESULT_SCAN(LAST_QUERY_ID()) to avoid re-running expensive queries
• Use transient tables for staging/temp data

---