{"id":"f23e8cdb-9b1b-4433-8f1a-48e76f70a273","shortId":"dgEJnw","kind":"skill","title":"Postgresql Code Review","tagline":"Awesome Copilot skill by Github","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"],"capabilities":["skill","source-github","category-awesome-copilot"],"categories":["awesome-copilot"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/github/awesome-copilot/postgresql-code-review","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"install_from":"skills.sh"}},"qualityScore":"0.300","qualityRationale":"deterministic score 0.30 from registry signals: · indexed on skills.sh · published under github/awesome-copilot","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:v1","enrichmentVersion":1,"enrichedAt":"2026-04-22T17:40:17.258Z","embedding":null,"createdAt":"2026-04-18T20:25:25.136Z","updatedAt":"2026-04-22T17:40:17.258Z","lastSeenAt":"2026-04-22T17:40:17.258Z","tsv":"'0':339 '1':812 '10':297,335 '123':101 '2':262,298,336,824 '20':304 '255':210 '3':301,838 '4':848 '5':859 '6':868 '7':878 '9':252,257 'a-za-z':258 'a-za-z0':248,253 'add':110,263 'advanc':763,876 'alter':107,601 'amount':295,332,343,345 'anti':33,362,366 'anti-pattern':32,361,365 'app':648,661,672 'app.current':627 'applic':620 'appropri':377,567,681,711,752,823,863 'area':47 'array':121,127,141,158,161,174,181,390,394,687,725 'assist':12 'audit':802 'avoid':368 'awesom':4 'bad':52,88,125,160,198,285,448,634 'begin':462,488 'benefici':759 'best':30,49,535,857 'bf':581 'bigseri':223 'broad':636 'built':797 'built-in':796 'bulk':173 'cancel':328 'capabl':892 'case':232 'case-insensit':231 'categori':135,147,152,157,179,180,183 'category-awesome-copilot' 'check':114,246,337,421,538,696,871 'checklist':677 'citext':227,435,685 'code':2,10,15,312,350,675,896 'complet':326 'complianc':780 'concaten':162 'condit':192 'consider':92,710 'constrain':692 'constraint':111,243,419,422,697,708 'contain':723 'copilot':5 'creat':70,143,203,211,219,235,270,292,309,319,329,340,452,478,505,543,544,552,558,609 'crypt':577 'ctes':738 'currenc':299,311,348,349 'current':491,625 'custom':280,307,704 'data':60,79,85,96,97,115,291,378,424,427,604,612,616,683,813 'databas':910 'decim':296,334 'deep':89 'default':238,242,356 'defin':703 'deliv':120 'design':195,406,679,844 'distinct':522 'domain':283,330,705 'effect':404 'effici':739,855 'electron':133,159 'email':208,226,234,245,247 'enabl':605 'encrypt':799 'end':471,495 'ensur':816,831,894 'entir':20 'enum':314,324,410,689 'error':774 'eur':316 'execut':525 'exist':541,548,556,562 'expert':13 'extens':531,534,540,545,553,559,566,750,860,867 'fail':327 'featur':202,747,765,806,870,877,886 'field':388 'fire':501 'focus':25,887 'function':443,451,455,477,481,526,736,745,777,800,849,853 'function/procedure':171 'fuzzi':582 'gbp':317 'gen':579 'generat':569,572 'generic':287,908 'gin':78,139,151,265,278,714 'gin/gist':375 'gist':717 'github':8 'good':66,102,138,172,214,305,474,598,650 'grant':638,653,663 'granular':651 'guidelin':811 'handl':775 'hash':575 'id':185,188,206,222,624,629,669 'idx':72,145,272 'ignor':389,418 'implement':599,694,753,772,785,801,880 'index':64,67,71,137,140,144,266,271,372,712,825,828,835 'ineffici':53,126,168,393,449 'insensit':233 'insert':655 'instead':431,700 'integ':207,630 'issu':407,446 'jpi':318 'jsonb':48,54,68,104,241,264,381,383,437,440,686,720,839,842 'jsonb/arrays':716 'key':225,398 'languag':472,496 'level':594,607,782 'leverag':401,688,760,897 'like':384 'limit':415 'loop':164 'make':899 'manag':632,792 'match':584 'metadata':240,268,274,279 'miss':420,436 'misus':380 'modifi':457,483,508,528 'name':515 'need':504,787 'nest':90 'new':182,470,494,524 'new.updated':463,489 'null':230,347,352 'number':100 'old':520 'oper':122,128,175,391,395,724,726,731 'optim':217,475,770,815 'order':58,73,76,83,94,109 'ossp':551 'over':635 'partit':397,403 'password':574,578 'pattern':34,363,367,847 'pend':118,325,357 'perform':364,709,869 'permiss':637,652 'pg':563 'pgcrypto':557 'pl/pgsql':757,852 'plpgsql':473,497 'polici':610,613 'poor':396 'posit':331,344 'postgr':588,589 'postgresql':1,9,14,28,42,44,193,201,216,306,359,370,402,530,590,674,682,729,743,746,761,768,794,805,808,818,833,866,874,883,889,900 'postgresql-optim':215 'postgresql-specif':27,43,358,369,728,742,767,807,817,832,882 'practic':31,50,536,858 'primari':224 'privileg':631,640,791 'procedur':755 'product':131,146,149,155,177,190 'project':21 'proper':695,732,773,788 'public':646 'qualiti':36,676,850 'queri':69,142,269,721,846 'rang':719 'rather':902 'replac':454,480 'return':459,469,485,493 'reusabl':707 'review':3,11,16,46,123,196,533,592,810,827,851,881 'rls':596,600,784 'role':621,789 'row':518,593,606,781 'salt':580 'schema':194,218,405,645,678,843 'secur':591,595,608,778,783,879,885 'select':18,24,56,81,129,153,187,399,570,576,585,654 'sensit':603,615 'seq':670 'sequenc':666 'set':95,178,417,498,626 'ship':62,87,98,119 'similar':587 'simpl':386 'skill':6 'source-github' 'special':901 'specif':29,45,290,360,371,658,667,730,744,769,809,819,834,884 'sql':51,124,197,284,447,537,597,633,764,909 'standard':37 'status':61,74,80,86,113,116,302,322,353,355 'store':754 'strategi':826 'string':387 'structur':103,438,840 'support':65 'tabl':108,204,220,293,341,514,602,643,659,668 'techniqu':771 'text':433,583 'time':458,484,509,529 'timestamp':213,492,702 'timestamptz':237,468,699 'track':99 'trail':803 'transact':294,321,342,354 'treat':382,904 'trgm':564 'trigger':445,450,460,476,486,499,506,510 'type':281,288,308,310,320,379,411,428,684,690,713,814,820,829 'uniqu':40,228,891 'unstructur':439 'updat':93,176,456,482,507,512,527,656 'usag':55,532,664,861 'usd':315 'use':77,150,200,277,286,374,392,409,412,429,467,565,622,680,698,722,727,733,740,749,766,793,822,864 'user':205,221,273,276,611,623,628,649,662,673 'util':748,837,872 'uuid':550,568,571 'uuid-ossp':549 'v4':573 'valid':106,112,244,425,442,841 'valu':338,416,693 'varchar':209,300,303,413,430 'verifi':862 'window':735 'without':91,441 'word':586 'would':166 'wrong':426 'z':261 'z0':251,256 'za':250,255,260","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-04-22T12:52:19.855Z"},{"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-04-22T17:40:17.258Z"}],"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","source":"skills_sh","category":"awesome-copilot","skills_sh_url":"https://skills.sh/github/awesome-copilot/postgresql-code-review"},"updatedAt":"2026-04-22T17:40:17.258Z"}}