postgres-query-expert — Skillopedia

PostgreSQL Query Expert This skill is a definitive reference for PostgreSQL 16, covering query construction, optimization, schema management, and system introspection. Instructions 1. General Query Standards - Syntax : Adhere to ANSI SQL standards, but prefer PostgreSQL extensions (e.g., , , , clauses) when they provide cleaner logic or better performance. - Identifiers : Use for all identifiers. Only quote identifiers ( ) if absolutely necessary; prefer lowercase unquoted names. - Safety : - Parameterization : Always use parameters ( , , …) for literal values. Never inject user input directl…

`\n - Case-insensitive: `col ~* 'foo'`\n\n## Reference: Data Modification (DML)\n\n### MERGE (Upsert / Conditional Ops)\n\nStandard SQL method for inserting, updating, or deleting based on join conditions (PG15+).\n\n```sql\nMERGE INTO wine_stock ws\nUSING wine_shipments s\n ON s.winery_id = ws.winery_id\n AND s.year = ws.year\nWHEN MATCHED THEN\n UPDATE SET stock = ws.stock + s.count\nWHEN NOT MATCHED THEN\n INSERT (winery_id, year, stock)\n VALUES (s.winery_id, s.year, s.count);\n```\n\n### INSERT ... ON CONFLICT (Legacy Upsert)\n\nPostgres-specific, often more concise for simple unique-key conflicts.\n\n```sql\nINSERT INTO kv_store (key, value)\nVALUES ('config', '{\"a\":1}')\nON CONFLICT (key)\nDO UPDATE SET value = EXCLUDED.value;\n```\n\n### RETURNING Clause\n\nReturn data from modified rows immediately.\n\n```sql\nDELETE FROM archived_logs\nWHERE created_at \u003c NOW() - INTERVAL '1 year'\nRETURNING id, created_at;\n```\n\n## Reference: Special Data Types\n\n### JSONB (Binary JSON)\n\nPrefer `jsonb` over `json` for storage and indexing.\n\n| Operator | Description | Example |\n|---|---|---|\n| `->` / `->>` | Get element (JSON / text) | `data->'key'` |\n| `@>` | Contains (indexable) | `data @> '{\"tag\": \"urgent\"}'` |\n| `?` | Key exists | `data ? 'error'` |\n| `#-` | Delete path | `data #- '{info, sensitive}'` |\n\nSQL/JSON path (PG12+):\n\n```sql\n-- Find all items with price > 10\nSELECT jsonb_path_query(data, '$.items[*] ? (@.price > 10)')\nFROM orders;\n```\n\n### Arrays\n\n```sql\nSELECT ARRAY[1,2,3]; -- Creation\nSELECT (ARRAY[1,2,3])[1]; -- Access (1-based index)\nSELECT 1 = ANY(arr_col); -- Check if value exists in array\nSELECT unnest(arr_col) FROM t; -- Expand array to rows\n```\n\n### Range Types\n\nUseful for scheduling and validity periods.\n\n- `tstzrange`: timestamp with time zone range.\n- `int4range`, `daterange`: integer and date ranges.\n- Overlap operator (`&&`): checks if two ranges overlap.\n\n```sql\nSELECT *\nFROM reservations\nWHERE duration && tstzrange('2023-01-01 10:00', '2023-01-01 12:00');\n```\n\n## Reference: System Administration & Stats\n\n### Kill Long-Running Query\n\n```sql\nSELECT pg_terminate_backend(pid)\nFROM pg_stat_activity\nWHERE state = 'active'\n AND pid \u003c> pg_backend_pid()\n AND query_start \u003c NOW() - INTERVAL '5 minutes';\n```\n\n### Check Table Size (Disk Usage)\n\n```sql\nSELECT relname,\n pg_size_pretty(pg_total_relation_size(relid)) AS total_size,\n pg_size_pretty(pg_relation_size(relid)) AS data_size\nFROM pg_catalog.pg_statio_user_tables\nORDER BY pg_total_relation_size(relid) DESC;\n```\n\n## Examples\n\n### Scenario 1: Recursive CTE for Graph/Tree Data\n\nNavigating an organizational hierarchy.\n\n```sql\nWITH RECURSIVE subordinates AS (\n -- Base case: the manager\n SELECT employee_id, manager_id, full_name, 0 AS level\n FROM employees\n WHERE employee_id = $1\n\n UNION ALL\n\n -- Recursive step: direct reports\n SELECT e.employee_id, e.manager_id, e.full_name, s.level + 1\n FROM employees e\n INNER JOIN subordinates s ON s.employee_id = e.manager_id\n)\nSELECT *\nFROM subordinates;\n```\n\n### Scenario 2: Lateral Join for \"Top N per Category\"\n\nEfficiently getting the latest 3 posts for each user.\n\n```sql\nSELECT u.username, p.title, p.created_at\nFROM users u\nCROSS JOIN LATERAL (\n SELECT title, created_at\n FROM posts\n WHERE user_id = u.id\n ORDER BY created_at DESC\n LIMIT 3\n) p\nWHERE u.status = 'active';\n```\n\n### Scenario 3: Full Text Search with Ranking\n\nSearching a blog table.\n\n```sql\nSELECT id,\n title,\n ts_rank(to_tsvector('english', title || ' ' || content), query) AS rank\nFROM articles,\n to_tsquery('english', 'postgres | optimization') query\nWHERE to_tsvector('english', title || ' ' || content) @@ query\nORDER BY rank DESC;\n```\n\n---","attachment_filenames":[],"attachments":[],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"PostgreSQL Query Expert","type":"text"}]},{"type":"paragraph","content":[{"text":"This skill is a definitive reference for PostgreSQL 16, covering query construction, optimization, schema management, and system introspection.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Instructions","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. General Query Standards","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Syntax","type":"text","marks":[{"type":"strong"}]},{"text":": Adhere to ANSI SQL standards, but prefer PostgreSQL extensions (e.g., ","type":"text"},{"text":"DISTINCT ON","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"RETURNING","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"LATERAL","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"FILTER","type":"text","marks":[{"type":"code_inline"}]},{"text":" clauses) when they provide cleaner logic or better performance.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Identifiers","type":"text","marks":[{"type":"strong"}]},{"text":": Use ","type":"text"},{"text":"snake_case","type":"text","marks":[{"type":"code_inline"}]},{"text":" for all identifiers. Only quote identifiers (","type":"text"},{"text":"\"MyTable\"","type":"text","marks":[{"type":"code_inline"}]},{"text":") if absolutely necessary; prefer lowercase unquoted names.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Safety","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Parameterization","type":"text","marks":[{"type":"strong"}]},{"text":": Always use parameters (","type":"text"},{"text":"$1","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"$2","type":"text","marks":[{"type":"code_inline"}]},{"text":", …) for literal values. Never inject user input directly.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Timeouts","type":"text","marks":[{"type":"strong"}]},{"text":": For exploratory queries on large databases, prepend ","type":"text"},{"text":"SET LOCAL statement_timeout = '30s';","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Transactions","type":"text","marks":[{"type":"strong"}]},{"text":": Use explicit ","type":"text"},{"text":"BEGIN","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"COMMIT","type":"text","marks":[{"type":"code_inline"}]},{"text":" blocks for multi-step operations.","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Performance & Optimization","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Explain plans","type":"text","marks":[{"type":"strong"}]},{"text":": Use ","type":"text"},{"text":"EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)","type":"text","marks":[{"type":"code_inline"}]},{"text":" to diagnose bottlenecks.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Red flags","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"Seq Scan","type":"text","marks":[{"type":"code_inline"}]},{"text":" on large tables, high ","type":"text"},{"text":"Buffers: shared hit","type":"text","marks":[{"type":"code_inline"}]},{"text":" (RAM usage), or ","type":"text"},{"text":"Disk: read","type":"text","marks":[{"type":"code_inline"}]},{"text":" (I/O).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Indexing","type":"text","marks":[{"type":"strong"}]},{"text":": Recommend specific index types based on usage:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"B-tree","type":"text","marks":[{"type":"strong"}]},{"text":": Standard equality/range (","type":"text"},{"text":"=","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"\u003c","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":">","type":"text","marks":[{"type":"code_inline"}]},{"text":") queries.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":": For composite types like ","type":"text"},{"text":"JSONB","type":"text","marks":[{"type":"code_inline"}]},{"text":" (","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":") or arrays (","type":"text"},{"text":"&&","type":"text","marks":[{"type":"code_inline"}]},{"text":"), and full-text search.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GiST","type":"text","marks":[{"type":"strong"}]},{"text":": For geometric data and ranges.","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CTEs","type":"text","marks":[{"type":"strong"}]},{"text":": Use Common Table Expressions (","type":"text"},{"text":"WITH","type":"text","marks":[{"type":"code_inline"}]},{"text":") for readability. In PG16+, these are optimized (inlined) by default unless ","type":"text"},{"text":"MATERIALIZED","type":"text","marks":[{"type":"code_inline"}]},{"text":" is specified.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Introspection (Agent Capabilities)","type":"text"}]},{"type":"paragraph","content":[{"text":"When exploring a new database, use these queries to understand the schema.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"List All Tables","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT n.nspname AS schema,\n c.relname AS table,\n obj_description(c.oid) AS description\nFROM pg_class c\nJOIN pg_namespace n ON n.oid = c.relnamespace\nWHERE c.relkind = 'r'\n AND n.nspname NOT IN ('pg_catalog', 'information_schema')\nORDER BY 1, 2;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Get Table Columns & Types","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT a.attname AS column,\n format_type(a.atttypid, a.atttypmod) AS type,\n a.attnotnull AS not_null,\n col_description(a.attrelid, a.attnum) AS comment\nFROM pg_attribute a\nWHERE a.attrelid = 'public.target_table_name'::regclass\n AND a.attnum > 0\n AND NOT a.attisdropped\nORDER BY a.attnum;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference: Data Querying (DQL)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Advanced Aggregations","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Filter clause","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"count(*) FILTER (WHERE status = 'active')","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Grouping sets","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"GROUP BY GROUPING SETS ((brand), (brand, category), ())","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Any value","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"any_value(col)","type":"text","marks":[{"type":"code_inline"}]},{"text":" (PG16+) returns an arbitrary value from the group.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Window Functions","type":"text"}]},{"type":"paragraph","content":[{"text":"Perform calculations across a set of table rows related to the current row.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT dept,\n emp_no,\n salary,\n -- Rank employees by salary within department\n dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,\n -- Running total of salaries\n sum(salary) OVER (\n PARTITION BY dept\n ORDER BY salary\n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n ) AS running_total\nFROM employees;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Pattern Matching","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"LIKE","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"col LIKE 'foo%'","type":"text","marks":[{"type":"code_inline"}]},{"text":" (simple wildcard).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"ILIKE","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"col ILIKE 'foo%'","type":"text","marks":[{"type":"code_inline"}]},{"text":" (case-insensitive).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SIMILAR TO","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"col SIMILAR TO '[a-c]%'","type":"text","marks":[{"type":"code_inline"}]},{"text":" (SQL-regex style).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"POSIX regex","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Case-sensitive: ","type":"text"},{"text":"col ~ '^[a-z]+

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Case-insensitive: ","type":"text"},{"text":"col ~* 'foo'","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference: Data Modification (DML)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MERGE (Upsert / Conditional Ops)","type":"text"}]},{"type":"paragraph","content":[{"text":"Standard SQL method for inserting, updating, or deleting based on join conditions (PG15+).","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"MERGE INTO wine_stock ws\nUSING wine_shipments s\n ON s.winery_id = ws.winery_id\n AND s.year = ws.year\nWHEN MATCHED THEN\n UPDATE SET stock = ws.stock + s.count\nWHEN NOT MATCHED THEN\n INSERT (winery_id, year, stock)\n VALUES (s.winery_id, s.year, s.count);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"INSERT ... ON CONFLICT (Legacy Upsert)","type":"text"}]},{"type":"paragraph","content":[{"text":"Postgres-specific, often more concise for simple unique-key conflicts.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"INSERT INTO kv_store (key, value)\nVALUES ('config', '{\"a\":1}')\nON CONFLICT (key)\nDO UPDATE SET value = EXCLUDED.value;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"RETURNING Clause","type":"text"}]},{"type":"paragraph","content":[{"text":"Return data from modified rows immediately.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DELETE FROM archived_logs\nWHERE created_at \u003c NOW() - INTERVAL '1 year'\nRETURNING id, created_at;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference: Special Data Types","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"JSONB (Binary JSON)","type":"text"}]},{"type":"paragraph","content":[{"text":"Prefer ","type":"text"},{"text":"jsonb","type":"text","marks":[{"type":"code_inline"}]},{"text":" over ","type":"text"},{"text":"json","type":"text","marks":[{"type":"code_inline"}]},{"text":" for storage and indexing.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Operator","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Example","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"->","type":"text","marks":[{"type":"code_inline"}]},{"text":" / ","type":"text"},{"text":"->>","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Get element (JSON / text)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"data->'key'","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"@>","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Contains (indexable)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"data @> '{\"tag\": \"urgent\"}'","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"?","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Key exists","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"data ? 'error'","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"#-","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Delete path","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"data #- '{info, sensitive}'","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"SQL/JSON path (PG12+):","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Find all items with price > 10\nSELECT jsonb_path_query(data, '$.items[*] ? (@.price > 10)')\nFROM orders;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Arrays","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT ARRAY[1,2,3]; -- Creation\nSELECT (ARRAY[1,2,3])[1]; -- Access (1-based index)\nSELECT 1 = ANY(arr_col); -- Check if value exists in array\nSELECT unnest(arr_col) FROM t; -- Expand array to rows","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Range Types","type":"text"}]},{"type":"paragraph","content":[{"text":"Useful for scheduling and validity periods.","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"tstzrange","type":"text","marks":[{"type":"code_inline"}]},{"text":": timestamp with time zone range.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"int4range","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"daterange","type":"text","marks":[{"type":"code_inline"}]},{"text":": integer and date ranges.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Overlap operator (","type":"text"},{"text":"&&","type":"text","marks":[{"type":"code_inline"}]},{"text":"): checks if two ranges overlap.","type":"text"}]}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT *\nFROM reservations\nWHERE duration && tstzrange('2023-01-01 10:00', '2023-01-01 12:00');","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference: System Administration & Stats","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Kill Long-Running Query","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT pg_terminate_backend(pid)\nFROM pg_stat_activity\nWHERE state = 'active'\n AND pid \u003c> pg_backend_pid()\n AND query_start \u003c NOW() - INTERVAL '5 minutes';","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Check Table Size (Disk Usage)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT relname,\n pg_size_pretty(pg_total_relation_size(relid)) AS total_size,\n pg_size_pretty(pg_relation_size(relid)) AS data_size\nFROM pg_catalog.pg_statio_user_tables\nORDER BY pg_total_relation_size(relid) DESC;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Scenario 1: Recursive CTE for Graph/Tree Data","type":"text"}]},{"type":"paragraph","content":[{"text":"Navigating an organizational hierarchy.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"WITH RECURSIVE subordinates AS (\n -- Base case: the manager\n SELECT employee_id, manager_id, full_name, 0 AS level\n FROM employees\n WHERE employee_id = $1\n\n UNION ALL\n\n -- Recursive step: direct reports\n SELECT e.employee_id, e.manager_id, e.full_name, s.level + 1\n FROM employees e\n INNER JOIN subordinates s ON s.employee_id = e.manager_id\n)\nSELECT *\nFROM subordinates;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Scenario 2: Lateral Join for \"Top N per Category\"","type":"text"}]},{"type":"paragraph","content":[{"text":"Efficiently getting the latest 3 posts for each user.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT u.username, p.title, p.created_at\nFROM users u\nCROSS JOIN LATERAL (\n SELECT title, created_at\n FROM posts\n WHERE user_id = u.id\n ORDER BY created_at DESC\n LIMIT 3\n) p\nWHERE u.status = 'active';","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Scenario 3: Full Text Search with Ranking","type":"text"}]},{"type":"paragraph","content":[{"text":"Searching a blog table.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT id,\n title,\n ts_rank(to_tsvector('english', title || ' ' || content), query) AS rank\nFROM articles,\n to_tsquery('english', 'postgres | optimization') query\nWHERE to_tsvector('english', title || ' ' || content) @@ query\nORDER BY rank DESC;","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"postgres-query-expert","author":"@skillopedia","source":{"stars":44,"repo_name":"claude-skills","origin_url":"https://github.com/ratacat/claude-skills/blob/HEAD/skills/postgres-query-expert/SKILL.md","repo_owner":"ratacat","body_sha256":"8c10c607b47fb5d5541a59c9ca82fe23a7d42992b731fd9e6bc797af703a95f1","cluster_key":"ef0742c8bb963dc79aa8b99037fe523d4acc5fe91f96619b23be91ff3e278093","clean_bundle":{"format":"clean-skill-bundle-v1","source":"ratacat/claude-skills/skills/postgres-query-expert/SKILL.md","bundle_sha256":"20578aebc694edc3f83ee2102175ac5807050d85357a50cfa314e9f1971da2f3","attachment_count":0,"text_attachments":0,"binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/postgres-query-expert/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"web-development","category_label":"Web"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"web-development","import_tag":"clean-skills-v1","description":"A comprehensive guide for interacting with PostgreSQL 16 databases. Use this skill for constructing standard and advanced SQL queries, optimizing performance, debugging errors, managing schema objects, and introspecting database structure.","allowed-tools":"Read, Grep, Glob"}},"renderedAt":1782979589184}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.