-- Extract orders from a JSON column
WITH order_data AS (
  SELECT 
    id,
    jsonb_array_elements(data->'orders') AS order_item
  FROM customers
  WHERE id = 1
)
SELECT
  id,
  order_item->>'order_id' AS order_id,
  (order_item->>'amount')::numeric AS amount,
  order_item->>'status' AS status
FROM order_data;

SELECT c.id, jt.*
FROM customers c,
JSON_TABLE(
  c.data,
  '$.orders[*]'
  COLUMNS (
    order_id    TEXT     PATH '$.order_id',
    amount      NUMERIC  PATH '$.amount',
    status      TEXT     PATH '$.status',
    created_at  DATE     PATH '$.created_at'
      DEFAULT '1970-01-01' ON EMPTY
  )
) AS jt
WHERE c.id = 1;

-- Flatten a deeply nested JSON structure
SELECT 
  customer_id,
  order_id,
  item_name,
  quantity
FROM customers,
JSON_TABLE(
  data,
  '$.orders[*]'
  COLUMNS (
    order_id    TEXT PATH '$.order_id',
    NESTED PATH '$.items[*]'
      COLUMNS (
        item_name  TEXT    PATH '$.name',
        quantity   INTEGER PATH '$.qty'
      )
  )
) jt;

-- Upsert and get back what changed
MERGE INTO inventory AS target
USING incoming_shipment AS source
  ON target.product_id = source.product_id
WHEN MATCHED THEN
  UPDATE SET 
    quantity = target.quantity + source.quantity,
    last_updated = NOW()
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity, last_updated)
  VALUES (source.product_id, source.quantity, NOW())
RETURNING 
  target.product_id,
  target.quantity,
  CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS action;

-- Insert or skip (no update) - previously required INSERT ON CONFLICT
MERGE INTO audit_log AS target
USING new_events AS source ON target.event_id = source.event_id
WHEN NOT MATCHED THEN
  INSERT (event_id, event_data, created_at)
  VALUES (source.event_id, source.event_data, NOW())
WHEN MATCHED THEN
  DO NOTHING;  -- New in PostgreSQL 17

-- You'll see vacuum complete faster for large tables
-- No config change needed; it's automatic

-- Monitor vacuum progress
SELECT 
  schemaname,
  tablename,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  num_dead_item_ids
FROM pg_stat_progress_vacuum;

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE TEXT)
SELECT u.name, COUNT(o.id) 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- New: SERIALIZE option shows time spent serializing results
-- Useful for identifying bottlenecks in data transfer vs. computation

-- Create a replication slot that survives failover
SELECT pg_create_logical_replication_slot(
  'my_subscription',
  'pgoutput',
  false,           -- not temporary
  false,           -- not two-phase
  true             -- failover = true (NEW in PG17)
);

-- On standby: sync slots from primary
-- postgresql.conf
sync_replication_slots = on

-- pg_subscription now supports slot synchronization
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary port=5432 dbname=mydb'
  PUBLICATION my_pub
  WITH (failover = true);  -- Enable failover support

-- New: COPY with DEFAULT keyword support
COPY products (id, name, price, category)
FROM '/data/products.csv'
WITH (FORMAT CSV, HEADER, DEFAULT 'General');
-- Empty category fields will be set to 'General'

-- pg_column_compression: see what compression is used per column
SELECT attname, pg_column_compression(heap_tuple) 
FROM pg_attribute 
WHERE attrelid = 'large_table'::regclass;

-- array_sample: randomly sample from an array
SELECT array_sample(ARRAY[1,2,3,4,5,6,7,8,9,10], 3);
-- Returns something like: {3, 7, 1}

-- string_to_table: split string into rows (replaces regexp_split_to_table for simple cases)  
SELECT * FROM string_to_table('apple,banana,cherry', ',');
--  string_to_table
-- -----------------
--  apple
--  banana
--  cherry

-- Old error for missing column
SELECT naem FROM users;
-- ERROR: column "naem" does not exist

-- New error (with suggestion)  
-- ERROR: column "naem" does not exist
-- HINT: Did you mean "name"?