postgresql

Name: postgresql
Author: jgamaraalv

20installs0forks

How to install postgresql?

jgamaraalv/ts-dev-kit/postgresql

$npx -y skills add jgamaraalv/ts-dev-kit --skill postgresql

Installs into the current project.

Summary

PostgreSQL 16+ reference for writing queries, designing schemas, managing indexes, and optimizing performance. Use when: (1) writing SQL queries (SELECT, INSERT, UPDATE, DELETE, CTEs, window functions, subqueries), (2) designing or altering table schemas (CREATE TABLE, constraint

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL 16+ Reference
2
3	Version: 16+. All syntax is standard; most features apply to PostgreSQL 13+.
4
5	<quick_reference>
6
7	## Quick patterns
8
9	```sql
10	-- Check running queries
11	SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE state != 'idle';
12
13	-- Explain a slow query
14	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
15
16	-- List table sizes
17	SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class
18	WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;
19
20	-- Kill a blocking query
21	SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <pid>;
22	```
23
24	</quick_reference>
25
26	<rules>
27
28	## Key non-obvious facts
29
30	- Every statement runs in a transaction. Without `BEGIN`, each statement auto-commits.
31	- `jsonb` stores parsed binary (faster queries); `json` stores raw text (exact input preserved). Prefer `jsonb`.
32	- `LIKE 'foo%'` can use B-tree; `LIKE '%foo'` cannot — use `pg_trgm` GIN for suffix search.
33	- `CREATE INDEX CONCURRENTLY` avoids table lock but cannot run inside a transaction block.
34	- `EXPLAIN` without `ANALYZE` shows the planner's _estimate_. Always use `EXPLAIN (ANALYZE, BUFFERS)` for real data.
35	- Null values are stored in indexes by B-tree (unlike some other databases). `IS NULL` can use an index.
36	- `SERIAL`/`BIGSERIAL` are shorthand for sequence + default; prefer `GENERATED ALWAYS AS IDENTITY` (SQL standard).
37	- Default isolation level is Read Committed. `SERIALIZABLE` prevents all anomalies but may abort transactions.
38
39	</rules>
40
41	<references>
42
43	## Reference files
44
45	Load the relevant file when working on a specific topic:
46
47	\| Topic \| File \| When to read \|
48	\| -------------------------------------- \| -------------------------------------------------------- \| -------------------------------------- \|
49	\| SELECT, JOINs, CTEs, window functions \| [references/queries.md](references/queries.md) \| Writing or debugging any query \|
50	\| CREATE TABLE, ALTER TABLE, constraints \| [references/ddl-schema.md](references/ddl-schema.md) \| Designing or modifying schemas \|
51	\| Index types, creation, strategy \| [references/indexes.md](references/indexes.md) \| Adding indexes or fixing slow queries \|
52	\| Transactions, savepoints, isolation \| [references/transactions.md](references/transactions.md) \| Concurrency, locking, isolation issues \|
53	\| JSONB operators, GIN, jsonpath \| [references/jsonb.md](references/jsonb.md) \| Working with JSON/JSONB columns \|
54	\| EXPLAIN output, VACUUM, stats \| [references/performance.md](references/performance.md) \| Query tuning or performance analysis \|
55	\| psql meta-commands \| [references/psql-cli.md](references/psql-cli.md) \| Working interactively in psql \|
56
57	</references>

People who liked this also viewed

6 picks

Type

postgresql

20installs0forks

View on GitHub

How to install postgresql?

jgamaraalv/ts-dev-kit/postgresql

$npx -y skills add jgamaraalv/ts-dev-kit --skill postgresql

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL 16+ Reference
2
3	Version: 16+. All syntax is standard; most features apply to PostgreSQL 13+.
4
5	<quick_reference>
6
7	## Quick patterns
8
9	```sql
10	-- Check running queries
11	SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE state != 'idle';
12
13	-- Explain a slow query
14	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
15
16	-- List table sizes
17	SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class
18	WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;
19
20	-- Kill a blocking query
21	SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <pid>;
22	```
23
24	</quick_reference>
25
26	<rules>
27
28	## Key non-obvious facts
29
30	- Every statement runs in a transaction. Without `BEGIN`, each statement auto-commits.
31	- `jsonb` stores parsed binary (faster queries); `json` stores raw text (exact input preserved). Prefer `jsonb`.
32	- `LIKE 'foo%'` can use B-tree; `LIKE '%foo'` cannot — use `pg_trgm` GIN for suffix search.
33	- `CREATE INDEX CONCURRENTLY` avoids table lock but cannot run inside a transaction block.
34	- `EXPLAIN` without `ANALYZE` shows the planner's _estimate_. Always use `EXPLAIN (ANALYZE, BUFFERS)` for real data.
35	- Null values are stored in indexes by B-tree (unlike some other databases). `IS NULL` can use an index.
36	- `SERIAL`/`BIGSERIAL` are shorthand for sequence + default; prefer `GENERATED ALWAYS AS IDENTITY` (SQL standard).
37	- Default isolation level is Read Committed. `SERIALIZABLE` prevents all anomalies but may abort transactions.
38
39	</rules>
40
41	<references>
42
43	## Reference files
44
45	Load the relevant file when working on a specific topic:
46
47	\| Topic \| File \| When to read \|
48	\| -------------------------------------- \| -------------------------------------------------------- \| -------------------------------------- \|
49	\| SELECT, JOINs, CTEs, window functions \| [references/queries.md](references/queries.md) \| Writing or debugging any query \|
50	\| CREATE TABLE, ALTER TABLE, constraints \| [references/ddl-schema.md](references/ddl-schema.md) \| Designing or modifying schemas \|
51	\| Index types, creation, strategy \| [references/indexes.md](references/indexes.md) \| Adding indexes or fixing slow queries \|
52	\| Transactions, savepoints, isolation \| [references/transactions.md](references/transactions.md) \| Concurrency, locking, isolation issues \|
53	\| JSONB operators, GIN, jsonpath \| [references/jsonb.md](references/jsonb.md) \| Working with JSON/JSONB columns \|
54	\| EXPLAIN output, VACUUM, stats \| [references/performance.md](references/performance.md) \| Query tuning or performance analysis \|
55	\| psql meta-commands \| [references/psql-cli.md](references/psql-cli.md) \| Working interactively in psql \|
56
57	</references>

People who liked this also viewed

6 picks

Type