postgresql-code-review

Name: postgresql-code-review
Author: github

11kinstalls4.6kforks

How to install postgresql-code-review?

github/awesome-copilot/postgresql-code-review

$npx -y skills add github/awesome-copilot --skill postgresql-code-review

Installs into the current project.

Summary

PostgreSQL-specific code review assistant focusing on PostgreSQL best practices, anti-patterns, and unique quality standards. Covers JSONB operations, array usage, custom types, schema design, function optimization, and PostgreSQL-exclusive security features like Row Level Securi

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Code Review Assistant
2
3	Expert PostgreSQL code review for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific best practices, anti-patterns, and quality standards that are unique to PostgreSQL.
4
5	## 🎯 PostgreSQL-Specific Review Areas
6
7	### JSONB Best Practices
8	```sql
9	-- ❌ BAD: Inefficient JSONB usage
10	SELECT * FROM orders WHERE data->>'status' = 'shipped'; -- No index support
11
12	-- ✅ GOOD: Indexable JSONB queries
13	CREATE INDEX idx_orders_status ON orders USING gin((data->'status'));
14	SELECT * FROM orders WHERE data @> '{"status": "shipped"}';
15
16	-- ❌ BAD: Deep nesting without consideration
17	UPDATE orders SET data = data \|\| '{"shipping":{"tracking":{"number":"123"}}}';
18
19	-- ✅ GOOD: Structured JSONB with validation
20	ALTER TABLE orders ADD CONSTRAINT valid_status
21	CHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));
22	```
23
24	### Array Operations Review
25	```sql
26	-- ❌ BAD: Inefficient array operations
27	SELECT * FROM products WHERE 'electronics' = ANY(categories); -- No index
28
29	-- ✅ GOOD: GIN indexed array queries
30	CREATE INDEX idx_products_categories ON products USING gin(categories);
31	SELECT * FROM products WHERE categories @> ARRAY['electronics'];
32
33	-- ❌ BAD: Array concatenation in loops
34	-- This would be inefficient in a function/procedure
35
36	-- ✅ GOOD: Bulk array operations
37	UPDATE products SET categories = categories \|\| ARRAY['new_category']
38	WHERE id IN (SELECT id FROM products WHERE condition);
39	```
40
41	### PostgreSQL Schema Design Review
42	```sql
43	-- ❌ BAD: Not using PostgreSQL features
44	CREATE TABLE users (
45	id INTEGER,
46	email VARCHAR(255),
47	created_at TIMESTAMP
48	);
49
50	-- ✅ GOOD: PostgreSQL-optimized schema
51	CREATE TABLE users (
52	id BIGSERIAL PRIMARY KEY,
53	email CITEXT UNIQUE NOT NULL, -- Case-insensitive email
54	created_at TIMESTAMPTZ DEFAULT NOW(),
55	metadata JSONB DEFAULT '{}',
56	CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
57	);
58
59	-- Add JSONB GIN index for metadata queries
60	CREATE INDEX idx_users_metadata ON users USING gin(metadata);
61	```
62
63	### Custom Types and Domains
64	```sql
65	-- ❌ BAD: Using generic types for specific data
66	CREATE TABLE transactions (
67	amount DECIMAL(10,2),
68	currency VARCHAR(3),
69	status VARCHAR(20)
70	);
71
72	-- ✅ GOOD: PostgreSQL custom types
73	CREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');
74	CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');
75	CREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0);
76
77	CREATE TABLE transactions (
78	amount positive_amount NOT NULL,
79	currency currency_code NOT NULL,
80	status transaction_status DEFAULT 'pending'
81	);
82	```
83
84	## 🔍 PostgreSQL-Specific Anti-Patterns
85
86	### Performance Anti-Patterns
87	- Avoiding PostgreSQL-specific indexes: Not using GIN/GiST for appropriate data types
88	- Misusing JSONB: Treating JSONB like a simple string field
89	- Ignoring array operators: Using inefficient array operations
90	- Poor partition key selection: Not leveraging PostgreSQL partitioning effectively
91
92	### Schema Design Issues
93	- Not using ENUM types: Using VARCHAR for limited value sets
94	- Ignoring constraints: Missing CHECK constraints for data validation
95	- Wrong data types: Using VARCHAR instead of TEXT or CITEXT
96	- Missing JSONB structure: Unstructured JSONB without validation
97
98	### Function and Trigger Issues
99	```sql
100	-- ❌ BAD: Inefficient trigger function
101	CREATE OR REPLACE FUNCTION update_modified_time()
102	RETURNS TRIGGER AS $$
103	BEGIN
104	NEW.updated_at = NOW(); -- Should use TIMESTAMPTZ
105	RETURN NEW;
106	END;
107	$$ LANGUAGE plpgsql;
108
109	-- ✅ GOOD: Optimized trigger function
110	CREATE OR REPLACE FUNCTION update_modified_time()
111	RETURNS TRIGGER AS $$
112	BEGIN
113	NEW.updated_at = CURRENT_TIMESTAMP;
114	RETURN NEW;
115	END;
116	$$ LANGUAGE plpgsql;
117
118	-- Set trigger to fire only when needed
119	CREATE TRIGGER update_modified_time_trigger
120	BEFORE UPDATE ON table_name
121	FOR EACH ROW
122	WHEN (OLD.* IS DISTINCT FROM NEW.*)
123	EXECUTE FUNCTION update_modified_time();
124	```
125
126	## 📊 PostgreSQL Extension Usage Review
127
128	### Extension Best Practices
129	```sql
130	-- ✅ Check if extension exists before creating
131	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
132	CREATE EXTENSION IF NOT EXISTS "pgcrypto";
133	CREATE EXTENSION IF NOT EXISTS "pg_trgm";
134
135	-- ✅ Use extensions appropriately
136	-- UUID generation
137	SELECT uuid_generate_v4();
138
139	-- Password hashing
140	SELECT crypt('password', gen_salt('bf'));
141
142	-- Fuzzy text matching
143	SELECT word_similarity('postgres', 'postgre');
144	```
145
146	## 🛡️ PostgreSQL Security Review
147
148	### Row Level Security (RLS)
149	```sql
150	-- ✅ GOOD: Implementing RLS
151	ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
152
153	CREATE POLICY u

People who liked this also viewed

6 picks

Type

postgresql-code-review

11kinstalls4.6kforks

View on GitHub

How to install postgresql-code-review?

github/awesome-copilot/postgresql-code-review

$npx -y skills add github/awesome-copilot --skill postgresql-code-review

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Code Review Assistant
2
3	Expert PostgreSQL code review for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific best practices, anti-patterns, and quality standards that are unique to PostgreSQL.
4
5	## 🎯 PostgreSQL-Specific Review Areas
6
7	### JSONB Best Practices
8	```sql
9	-- ❌ BAD: Inefficient JSONB usage
10	SELECT * FROM orders WHERE data->>'status' = 'shipped'; -- No index support
11
12	-- ✅ GOOD: Indexable JSONB queries
13	CREATE INDEX idx_orders_status ON orders USING gin((data->'status'));
14	SELECT * FROM orders WHERE data @> '{"status": "shipped"}';
15
16	-- ❌ BAD: Deep nesting without consideration
17	UPDATE orders SET data = data \|\| '{"shipping":{"tracking":{"number":"123"}}}';
18
19	-- ✅ GOOD: Structured JSONB with validation
20	ALTER TABLE orders ADD CONSTRAINT valid_status
21	CHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));
22	```
23
24	### Array Operations Review
25	```sql
26	-- ❌ BAD: Inefficient array operations
27	SELECT * FROM products WHERE 'electronics' = ANY(categories); -- No index
28
29	-- ✅ GOOD: GIN indexed array queries
30	CREATE INDEX idx_products_categories ON products USING gin(categories);
31	SELECT * FROM products WHERE categories @> ARRAY['electronics'];
32
33	-- ❌ BAD: Array concatenation in loops
34	-- This would be inefficient in a function/procedure
35
36	-- ✅ GOOD: Bulk array operations
37	UPDATE products SET categories = categories \|\| ARRAY['new_category']
38	WHERE id IN (SELECT id FROM products WHERE condition);
39	```
40
41	### PostgreSQL Schema Design Review
42	```sql
43	-- ❌ BAD: Not using PostgreSQL features
44	CREATE TABLE users (
45	id INTEGER,
46	email VARCHAR(255),
47	created_at TIMESTAMP
48	);
49
50	-- ✅ GOOD: PostgreSQL-optimized schema
51	CREATE TABLE users (
52	id BIGSERIAL PRIMARY KEY,
53	email CITEXT UNIQUE NOT NULL, -- Case-insensitive email
54	created_at TIMESTAMPTZ DEFAULT NOW(),
55	metadata JSONB DEFAULT '{}',
56	CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
57	);
58
59	-- Add JSONB GIN index for metadata queries
60	CREATE INDEX idx_users_metadata ON users USING gin(metadata);
61	```
62
63	### Custom Types and Domains
64	```sql
65	-- ❌ BAD: Using generic types for specific data
66	CREATE TABLE transactions (
67	amount DECIMAL(10,2),
68	currency VARCHAR(3),
69	status VARCHAR(20)
70	);
71
72	-- ✅ GOOD: PostgreSQL custom types
73	CREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');
74	CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');
75	CREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0);
76
77	CREATE TABLE transactions (
78	amount positive_amount NOT NULL,
79	currency currency_code NOT NULL,
80	status transaction_status DEFAULT 'pending'
81	);
82	```
83
84	## 🔍 PostgreSQL-Specific Anti-Patterns
85
86	### Performance Anti-Patterns
87	- Avoiding PostgreSQL-specific indexes: Not using GIN/GiST for appropriate data types
88	- Misusing JSONB: Treating JSONB like a simple string field
89	- Ignoring array operators: Using inefficient array operations
90	- Poor partition key selection: Not leveraging PostgreSQL partitioning effectively
91
92	### Schema Design Issues
93	- Not using ENUM types: Using VARCHAR for limited value sets
94	- Ignoring constraints: Missing CHECK constraints for data validation
95	- Wrong data types: Using VARCHAR instead of TEXT or CITEXT
96	- Missing JSONB structure: Unstructured JSONB without validation
97
98	### Function and Trigger Issues
99	```sql
100	-- ❌ BAD: Inefficient trigger function
101	CREATE OR REPLACE FUNCTION update_modified_time()
102	RETURNS TRIGGER AS $$
103	BEGIN
104	NEW.updated_at = NOW(); -- Should use TIMESTAMPTZ
105	RETURN NEW;
106	END;
107	$$ LANGUAGE plpgsql;
108
109	-- ✅ GOOD: Optimized trigger function
110	CREATE OR REPLACE FUNCTION update_modified_time()
111	RETURNS TRIGGER AS $$
112	BEGIN
113	NEW.updated_at = CURRENT_TIMESTAMP;
114	RETURN NEW;
115	END;
116	$$ LANGUAGE plpgsql;
117
118	-- Set trigger to fire only when needed
119	CREATE TRIGGER update_modified_time_trigger
120	BEFORE UPDATE ON table_name
121	FOR EACH ROW
122	WHEN (OLD.* IS DISTINCT FROM NEW.*)
123	EXECUTE FUNCTION update_modified_time();
124	```
125
126	## 📊 PostgreSQL Extension Usage Review
127
128	### Extension Best Practices
129	```sql
130	-- ✅ Check if extension exists before creating
131	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
132	CREATE EXTENSION IF NOT EXISTS "pgcrypto";
133	CREATE EXTENSION IF NOT EXISTS "pg_trgm";
134
135	-- ✅ Use extensions appropriately
136	-- UUID generation
137	SELECT uuid_generate_v4();
138
139	-- Password hashing
140	SELECT crypt('password', gen_salt('bf'));
141
142	-- Fuzzy text matching
143	SELECT word_similarity('postgres', 'postgre');
144	```
145
146	## 🛡️ PostgreSQL Security Review
147
148	### Row Level Security (RLS)
149	```sql
150	-- ✅ GOOD: Implementing RLS
151	ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
152
153	CREATE POLICY u

People who liked this also viewed

6 picks

Type