{"id":"f23e8cdb-9b1b-4433-8f1a-48e76f70a273","shortId":"dgEJnw","kind":"skill","title":"postgresql-code-review","tagline":"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","description":"# PostgreSQL Code Review Assistant\n\nExpert 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.\n\n## 🎯 PostgreSQL-Specific Review Areas\n\n### JSONB Best Practices\n```sql\n-- ❌ BAD: Inefficient JSONB usage\nSELECT * FROM orders WHERE data->>'status' = 'shipped';  -- No index support\n\n-- ✅ GOOD: Indexable JSONB queries\nCREATE INDEX idx_orders_status ON orders USING gin((data->'status'));\nSELECT * FROM orders WHERE data @> '{\"status\": \"shipped\"}';\n\n-- ❌ BAD: Deep nesting without consideration\nUPDATE orders SET data = data || '{\"shipping\":{\"tracking\":{\"number\":\"123\"}}}';\n\n-- ✅ GOOD: Structured JSONB with validation\nALTER TABLE orders ADD CONSTRAINT valid_status \nCHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));\n```\n\n### Array Operations Review\n```sql\n-- ❌ BAD: Inefficient array operations\nSELECT * FROM products WHERE 'electronics' = ANY(categories);  -- No index\n\n-- ✅ GOOD: GIN indexed array queries\nCREATE INDEX idx_products_categories ON products USING gin(categories);\nSELECT * FROM products WHERE categories @> ARRAY['electronics'];\n\n-- ❌ BAD: Array concatenation in loops\n-- This would be inefficient in a function/procedure\n\n-- ✅ GOOD: Bulk array operations\nUPDATE products SET categories = categories || ARRAY['new_category']\nWHERE id IN (SELECT id FROM products WHERE condition);\n```\n\n### PostgreSQL Schema Design Review\n```sql\n-- ❌ BAD: Not using PostgreSQL features\nCREATE TABLE users (\n    id INTEGER,\n    email VARCHAR(255),\n    created_at TIMESTAMP\n);\n\n-- ✅ GOOD: PostgreSQL-optimized schema\nCREATE TABLE users (\n    id BIGSERIAL PRIMARY KEY,\n    email CITEXT UNIQUE NOT NULL,  -- Case-insensitive email\n    created_at TIMESTAMPTZ DEFAULT NOW(),\n    metadata JSONB DEFAULT '{}',\n    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')\n);\n\n-- Add JSONB GIN index for metadata queries\nCREATE INDEX idx_users_metadata ON users USING gin(metadata);\n```\n\n### Custom Types and Domains\n```sql\n-- ❌ BAD: Using generic types for specific data\nCREATE TABLE transactions (\n    amount DECIMAL(10,2),\n    currency VARCHAR(3),\n    status VARCHAR(20)\n);\n\n-- ✅ GOOD: PostgreSQL custom types\nCREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');\nCREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');\nCREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0);\n\nCREATE TABLE transactions (\n    amount positive_amount NOT NULL,\n    currency currency_code NOT NULL,\n    status transaction_status DEFAULT 'pending'\n);\n```\n\n## 🔍 PostgreSQL-Specific Anti-Patterns\n\n### Performance Anti-Patterns\n- **Avoiding PostgreSQL-specific indexes**: Not using GIN/GiST for appropriate data types\n- **Misusing JSONB**: Treating JSONB like a simple string field\n- **Ignoring array operators**: Using inefficient array operations\n- **Poor partition key selection**: Not leveraging PostgreSQL partitioning effectively\n\n### Schema Design Issues\n- **Not using ENUM types**: Using VARCHAR for limited value sets\n- **Ignoring constraints**: Missing CHECK constraints for data validation\n- **Wrong data types**: Using VARCHAR instead of TEXT or CITEXT\n- **Missing JSONB structure**: Unstructured JSONB without validation\n\n### Function and Trigger Issues\n```sql\n-- ❌ BAD: Inefficient trigger function\nCREATE OR REPLACE FUNCTION update_modified_time()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();  -- Should use TIMESTAMPTZ\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n-- ✅ GOOD: Optimized trigger function\nCREATE OR REPLACE FUNCTION update_modified_time()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = CURRENT_TIMESTAMP;\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n-- Set trigger to fire only when needed\nCREATE TRIGGER update_modified_time_trigger\n    BEFORE UPDATE ON table_name\n    FOR EACH ROW\n    WHEN (OLD.* IS DISTINCT FROM NEW.*)\n    EXECUTE FUNCTION update_modified_time();\n```\n\n## 📊 PostgreSQL Extension Usage Review\n\n### Extension Best Practices\n```sql\n-- ✅ Check if extension exists before creating\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\nCREATE EXTENSION IF NOT EXISTS \"pgcrypto\";\nCREATE EXTENSION IF NOT EXISTS \"pg_trgm\";\n\n-- ✅ Use extensions appropriately\n-- UUID generation\nSELECT uuid_generate_v4();\n\n-- Password hashing\nSELECT crypt('password', gen_salt('bf'));\n\n-- Fuzzy text matching\nSELECT word_similarity('postgres', 'postgre');\n```\n\n## 🛡️ PostgreSQL Security Review\n\n### Row Level Security (RLS)\n```sql\n-- ✅ GOOD: Implementing RLS\nALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;\n\nCREATE POLICY user_data_policy ON sensitive_data\n    FOR ALL TO application_role\n    USING (user_id = current_setting('app.current_user_id')::INTEGER);\n```\n\n### Privilege Management\n```sql\n-- ❌ BAD: Overly broad permissions\nGRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;\n\n-- ✅ GOOD: Granular permissions\nGRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;\nGRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;\n```\n\n## 🎯 PostgreSQL Code Quality Checklist\n\n### Schema Design\n- [ ] Using appropriate PostgreSQL data types (CITEXT, JSONB, arrays)\n- [ ] Leveraging ENUM types for constrained values\n- [ ] Implementing proper CHECK constraints\n- [ ] Using TIMESTAMPTZ instead of TIMESTAMP\n- [ ] Defining custom domains for reusable constraints\n\n### Performance Considerations\n- [ ] Appropriate index types (GIN for JSONB/arrays, GiST for ranges)\n- [ ] JSONB queries using containment operators (@>, ?)\n- [ ] Array operations using PostgreSQL-specific operators\n- [ ] Proper use of window functions and CTEs\n- [ ] Efficient use of PostgreSQL-specific functions\n\n### PostgreSQL Features Utilization\n- [ ] Using extensions where appropriate\n- [ ] Implementing stored procedures in PL/pgSQL when beneficial\n- [ ] Leveraging PostgreSQL's advanced SQL features\n- [ ] Using PostgreSQL-specific optimization techniques\n- [ ] Implementing proper error handling in functions\n\n### Security and Compliance\n- [ ] Row Level Security (RLS) implementation where needed\n- [ ] Proper role and privilege management\n- [ ] Using PostgreSQL's built-in encryption functions\n- [ ] Implementing audit trails with PostgreSQL features\n\n## 📝 PostgreSQL-Specific Review Guidelines\n\n1. **Data Type Optimization**: Ensure PostgreSQL-specific types are used appropriately\n2. **Index Strategy**: Review index types and ensure PostgreSQL-specific indexes are utilized\n3. **JSONB Structure**: Validate JSONB schema design and query patterns\n4. **Function Quality**: Review PL/pgSQL functions for efficiency and best practices\n5. **Extension Usage**: Verify appropriate use of PostgreSQL extensions\n6. **Performance Features**: Check utilization of PostgreSQL's advanced features\n7. **Security Implementation**: Review PostgreSQL-specific security features\n\nFocus on PostgreSQL's unique capabilities and ensure the code leverages what makes PostgreSQL special rather than treating it as a generic SQL database.","tags":["postgresql","code","review","awesome","copilot","github","agent-skills","agents","custom-agents","github-copilot","hacktoberfest","prompt-engineering"],"capabilities":["skill","source-github","skill-postgresql-code-review","topic-agent-skills","topic-agents","topic-awesome","topic-custom-agents","topic-github-copilot","topic-hacktoberfest","topic-prompt-engineering"],"categories":["awesome-copilot"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/github/awesome-copilot/postgresql-code-review","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add github/awesome-copilot","source_repo":"https://github.com/github/awesome-copilot","install_from":"skills.sh"}},"qualityScore":"0.700","qualityRationale":"deterministic score 0.70 from registry signals: · indexed on github topic:agent-skills · 33270 github stars · SKILL.md body (7,043 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T18:52:20.067Z","embedding":null,"createdAt":"2026-04-18T20:25:25.136Z","updatedAt":"2026-05-18T18:52:20.067Z","lastSeenAt":"2026-05-18T18:52:20.067Z","tsv":"'0':374 '1':847 '10':332,370 '123':136 '2':297,333,371,859 '20':339 '255':245 '3':336,873 '4':883 '5':894 '6':903 '7':913 '9':287,292 'a-za-z':293 'a-za-z0':283,288 'add':145,298 'advanc':798,911 'alter':142,636 'amount':330,367,378,380 'anti':17,68,397,401 'anti-pattern':16,67,396,400 'app':683,696,707 'app.current':662 'applic':655 'appropri':412,602,716,746,787,858,898 'area':82 'array':26,156,162,176,193,196,209,216,425,429,722,760 'assist':10,47 'audit':837 'avoid':403 'bad':87,123,160,195,233,320,483,669 'begin':497,523 'benefici':794 'best':14,65,84,570,892 'bf':616 'bigseri':258 'broad':671 'built':832 'built-in':831 'bulk':208 'cancel':363 'capabl':927 'case':267 'case-insensit':266 'categori':170,182,187,192,214,215,218 'check':149,281,372,456,573,731,906 'checklist':712 'citext':262,470,720 'code':3,8,45,50,347,385,710,931 'complet':361 'complianc':815 'concaten':197 'condit':227 'consider':127,745 'constrain':727 'constraint':146,278,454,457,732,743 'contain':758 'cover':23 'creat':105,178,238,246,254,270,305,327,344,354,364,375,487,513,540,578,579,587,593,644 'crypt':612 'ctes':773 'currenc':334,346,383,384 'current':526,660 'custom':28,315,342,739 'data':95,114,120,131,132,150,326,413,459,462,639,647,651,718,848 'databas':945 'decim':331,369 'deep':124 'default':273,277,391 'defin':738 'deliv':155 'design':31,230,441,714,879 'distinct':557 'domain':318,365,740 'effect':439 'effici':774,890 'electron':168,194 'email':243,261,269,280,282 'enabl':640 'encrypt':834 'end':506,530 'ensur':851,866,929 'entir':55 'enum':349,359,445,724 'error':809 'eur':351 'exclus':37 'execut':560 'exist':576,583,591,597 'expert':48 'extens':566,569,575,580,588,594,601,785,895,902 'fail':362 'featur':39,237,782,800,841,905,912,921 'field':423 'fire':536 'focus':11,60,922 'function':32,478,486,490,512,516,561,771,780,812,835,884,888 'function/procedure':206 'fuzzi':617 'gbp':352 'gen':614 'generat':604,607 'generic':322,943 'gin':113,174,186,300,313,749 'gin/gist':410 'gist':752 'good':101,137,173,207,249,340,509,633,685 'grant':673,688,698 'granular':686 'guidelin':846 'handl':810 'hash':610 'id':220,223,241,257,659,664,704 'idx':107,180,307 'ignor':424,453 'implement':634,729,788,807,820,836,915 'index':99,102,106,172,175,179,301,306,407,747,860,863,870 'ineffici':88,161,203,428,484 'insensit':268 'insert':690 'instead':466,735 'integ':242,665 'issu':442,481 'jpi':353 'jsonb':24,83,89,103,139,276,299,416,418,472,475,721,755,874,877 'jsonb/arrays':751 'key':260,433 'languag':507,531 'level':42,629,642,817 'leverag':436,723,795,932 'like':40,419 'limit':450 'loop':199 'make':934 'manag':667,827 'match':619 'metadata':275,303,309,314 'miss':455,471 'misus':415 'modifi':492,518,543,563 'name':550 'need':539,822 'nest':125 'new':217,505,529,559 'new.updated':498,524 'null':265,382,387 'number':135 'old':555 'oper':25,157,163,210,426,430,759,761,766 'optim':33,252,510,805,850 'order':93,108,111,118,129,144 'ossp':586 'over':670 'partit':432,438 'password':609,613 'pattern':18,69,398,402,882 'pend':153,360,392 'perform':399,744,904 'permiss':672,687 'pg':598 'pgcrypto':592 'pl/pgsql':792,887 'plpgsql':508,532 'polici':645,648 'poor':431 'posit':366,379 'postgr':623,624 'postgresql':2,6,13,36,44,49,63,77,79,228,236,251,341,394,405,437,565,625,709,717,764,778,781,796,803,829,840,843,853,868,901,909,918,924,935 'postgresql-code-review':1 'postgresql-exclus':35 'postgresql-optim':250 'postgresql-specif':5,62,78,393,404,763,777,802,842,852,867,917 'practic':15,66,85,571,893 'primari':259 'privileg':666,675,826 'procedur':790 'product':166,181,184,190,212,225 'project':56 'proper':730,767,808,823 'public':681 'qualiti':21,71,711,885 'queri':104,177,304,756,881 'rang':754 'rather':937 'replac':489,515 'return':494,504,520,528 'reusabl':742 'review':4,9,46,51,81,158,231,568,627,845,862,886,916 'rls':631,635,819 'role':656,824 'row':41,553,628,641,816 'salt':615 'schema':30,229,253,440,680,713,878 'secur':38,626,630,643,813,818,914,920 'securi':43 'select':53,59,91,116,164,188,222,434,605,611,620,689 'sensit':638,650 'seq':705 'sequenc':701 'set':130,213,452,533,661 'ship':97,122,133,154 'similar':622 'simpl':421 'skill' 'skill-postgresql-code-review' 'source-github' 'special':936 'specif':7,64,80,325,395,406,693,702,765,779,804,844,854,869,919 'sql':86,159,232,319,482,572,632,668,799,944 'standard':22,72 'status':96,109,115,121,148,151,337,357,388,390 'store':789 'strategi':861 'string':422 'structur':138,473,875 'support':100 'tabl':143,239,255,328,376,549,637,678,694,703 'techniqu':806 'text':468,618 'time':493,519,544,564 'timestamp':248,527,737 'timestamptz':272,503,734 'topic-agent-skills' 'topic-agents' 'topic-awesome' 'topic-custom-agents' 'topic-github-copilot' 'topic-hacktoberfest' 'topic-prompt-engineering' 'track':134 'trail':838 'transact':329,356,377,389 'treat':417,939 'trgm':599 'trigger':480,485,495,511,521,534,541,545 'type':29,316,323,343,345,355,414,446,463,719,725,748,849,855,864 'uniqu':20,75,263,926 'unstructur':474 'updat':128,211,491,517,542,547,562,691 'usag':27,90,567,699,896 'usd':350 'use':112,185,235,312,321,409,427,444,447,464,502,600,657,715,733,757,762,768,775,784,801,828,857,899 'user':240,256,308,311,646,658,663,684,697,708 'util':783,872,907 'uuid':585,603,606 'uuid-ossp':584 'v4':608 'valid':141,147,279,460,477,876 'valu':373,451,728 'varchar':244,335,338,448,465 'verifi':897 'window':770 'without':126,476 'word':621 'would':201 'wrong':461 'z':296 'z0':286,291 'za':285,290,295","prices":[{"id":"2a5a4d05-8fb7-482b-87a9-35a35cb2f47b","listingId":"f23e8cdb-9b1b-4433-8f1a-48e76f70a273","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"github","category":"awesome-copilot","install_from":"skills.sh"},"createdAt":"2026-04-18T20:25:25.136Z"}],"sources":[{"listingId":"f23e8cdb-9b1b-4433-8f1a-48e76f70a273","source":"github","sourceId":"github/awesome-copilot/postgresql-code-review","sourceUrl":"https://github.com/github/awesome-copilot/tree/main/skills/postgresql-code-review","isPrimary":false,"firstSeenAt":"2026-04-18T21:50:33.170Z","lastSeenAt":"2026-05-18T18:52:20.067Z"},{"listingId":"f23e8cdb-9b1b-4433-8f1a-48e76f70a273","source":"skills_sh","sourceId":"github/awesome-copilot/postgresql-code-review","sourceUrl":"https://skills.sh/github/awesome-copilot/postgresql-code-review","isPrimary":true,"firstSeenAt":"2026-04-18T20:25:25.136Z","lastSeenAt":"2026-05-07T22:40:17.043Z"}],"details":{"listingId":"f23e8cdb-9b1b-4433-8f1a-48e76f70a273","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"github","slug":"postgresql-code-review","github":{"repo":"github/awesome-copilot","stars":33270,"topics":["agent-skills","agents","ai","awesome","custom-agents","github-copilot","hacktoberfest","prompt-engineering"],"license":"mit","html_url":"https://github.com/github/awesome-copilot","pushed_at":"2026-05-18T01:26:59Z","description":"Community-contributed instructions, agents, skills, and configurations to help you make the most of GitHub Copilot.","skill_md_sha":"72d8eac69920b97d20bbcf012469808129ec129f","skill_md_path":"skills/postgresql-code-review/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/github/awesome-copilot/tree/main/skills/postgresql-code-review"},"layout":"multi","source":"github","category":"awesome-copilot","frontmatter":{"name":"postgresql-code-review","description":"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 Security (RLS)."},"skills_sh_url":"https://skills.sh/github/awesome-copilot/postgresql-code-review"},"updatedAt":"2026-05-18T18:52:20.067Z"}}