PostgreSQL चीटशीट: एक डेवलपर का त्वरित संदर्भ
पोस्टग्रेसक्यूएल के लिए त्वरित संदर्भ
Page content
दैनिक PostgreSQL कार्य के लिए एक त्वरित संदर्भ: PostgreSQL कनेक्शन, SQL सिंटैक्स, psql मेटा-kommends, प्रदर्शन, JSON, विंडो फंक्शंस, और अधिक।
सबसे आवश्यक कमांड्स और ऑपरेशंस को कवर करने वाला एक संक्षिप्त SQL चीटशीट भी उपलब्ध है।
कनेक्ट & बेसिक्स
# कनेक्ट
psql -h HOST -p 5432 -U USER -d DB
psql $DATABASE_URL
# psql के अंदर
\conninfo -- कनेक्शन दिखाएं
\l[+] -- डेटाबेस सूची
\c DB -- डेटाबेस से कनेक्ट करें
\dt[+] [schema.]pat -- टेबल सूची
\dv[+] -- व्यू सूची
\ds[+] -- सीक्वेंस सूची
\df[+] [pat] -- फंक्शंस सूची
\d[S+] name -- टेबल/व्यू/सीक्वेंस विवरण
\dn[+] -- स्कीमा सूची
\du[+] -- रोल सूची
\timing -- क्वेरी टाइमिंग टॉगल
\x -- विस्तृत डिस्प्ले
\e -- बफर को $EDITOR में संपादित करें
\i file.sql -- फाइल चलाएं
\copy ... -- क्लाइंट-साइड COPY
\! shell_cmd -- शेल कमांड चलाएं
डेटा टाइप्स (सामान्य)
- संख्यात्मक:
smallint
,integer
,bigint
,decimal(p,s)
,numeric
,real
,double precision
,serial
,bigserial
- पाठ:
text
,varchar(n)
,char(n)
- बूलियन:
boolean
- समयसंबंधी:
timestamp [with/without time zone]
,date
,time
,interval
- UUID:
uuid
- JSON:
json
,jsonb
(प्राथमिक) - एरे:
type[]
उदाहरण के लिएtext[]
- नेटवर्क:
inet
,cidr
,macaddr
- ज्यामिति:
point
,line
,polygon
, आदि।
PostgreSQL संस्करण की जांच करें
SELECT version();
PostgreSQL सर्वर संस्करण:
pg_config --version
PostgreSQL क्लाइंट संस्करण:
psql --version
DDL (सृजन / परिवर्तन)
-- स्कीमा और टेबल बनाएं
CREATE SCHEMA IF NOT EXISTS app;
CREATE TABLE app.users (
id bigserial PRIMARY KEY,
email text NOT NULL UNIQUE,
name text,
active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now(),
profile jsonb,
tags text[]
);
-- परिवर्तन
ALTER TABLE app.users ADD COLUMN last_login timestamptz;
ALTER TABLE app.users ALTER COLUMN name SET NOT NULL;
ALTER TABLE app.users DROP COLUMN tags;
-- प्रतिबंध
ALTER TABLE app.users ADD CONSTRAINT email_lower_uk UNIQUE (lower(email));
-- इंडेक्स
CREATE INDEX ON app.users (email);
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON app.users (lower(email));
CREATE INDEX users_profile_gin ON app.users USING gin (profile);
CREATE INDEX users_created_at_idx ON app.users (created_at DESC);
DML (इन्सर्ट / अपडेट / अपसर्ट / डिलीट)
INSERT INTO app.users (email, name) VALUES
('a@x.com','A'), ('b@x.com','B')
RETURNING id;
-- अपसर्ट (ON CONFLICT)
INSERT INTO app.users (email, name)
VALUES ('a@x.com','Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();
UPDATE app.users SET active = false WHERE last_login < now() - interval '1 year';
DELETE FROM app.users WHERE active = false AND last_login IS NULL;
क्वेरी एसेंशियल्स
SELECT * FROM app.users ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- पेजिनेशन
-- फिल्टरिंग
SELECT * FROM app.users WHERE email ILIKE '%@example.%' AND active;
-- एग्रीगेट्स & GROUP BY
SELECT active, count(*) AS n
FROM app.users
GROUP BY active
HAVING count(*) > 10;
-- जॉइन्स
SELECT o.id, u.email, o.total
FROM app.orders o
JOIN app.users u ON u.id = o.user_id
LEFT JOIN app.discounts d ON d.id = o.discount_id;
-- DISTINCT ON (Postgres-विशिष्ट)
SELECT DISTINCT ON (user_id) user_id, status, created_at
FROM app.events
ORDER BY user_id, created_at DESC;
-- CTEs
WITH recent AS (
SELECT * FROM app.orders WHERE created_at > now() - interval '30 days'
)
SELECT count(*) FROM recent;
-- रिकर्सिव CTE
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t WHERE n < 10
)
SELECT sum(n) FROM t;
विंडो फंक्शंस
SELECT
user_id,
created_at,
sum(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
row_number() OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
lag(total, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM app.orders;
JSON / JSONB
-- निकालना
SELECT profile->>'company' AS company FROM app.users;
SELECT profile->'address'->>'city' FROM app.users;
-- jsonb क्वेरी के लिए इंडेक्स
CREATE INDEX users_profile_company_gin ON app.users USING gin ((profile->>'company'));
-- अस्तित्व / समावेश
SELECT * FROM app.users WHERE profile ? 'company'; -- की अस्तित्व है
SELECT * FROM app.users WHERE profile @> '{"role":"admin"}'; -- समावेश है
-- jsonb अपडेट
UPDATE app.users
SET profile = jsonb_set(COALESCE(profile,'{}'::jsonb), '{prefs,theme}', '"dark"', true);
एरे
-- सदस्यता & समावेश
SELECT * FROM app.users WHERE 'vip' = ANY(tags);
SELECT * FROM app.users WHERE tags @> ARRAY['beta'];
-- जोड़ना
UPDATE app.users SET tags = array_distinct(tags || ARRAY['vip']);
समय & तिथियाँ
SELECT now() AT TIME ZONE 'Australia/Melbourne';
SELECT date_trunc('day', created_at) AS d, count(*)
FROM app.users GROUP BY d ORDER BY d;
-- अंतराल
SELECT now() - interval '7 days';
ट्रांजैक्शंस & लॉक्स
BEGIN;
UPDATE app.accounts SET balance = balance - 100 WHERE id = 1;
UPDATE app.accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- या ROLLBACK
-- आइसोलेशन लेवल
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- लॉक्स की जांच
SELECT * FROM pg_locks l JOIN pg_stat_activity a USING (pid);
रोल्स & अनुमतियाँ
-- रोल/यूजर बनाएं
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
आयात / निर्यात
-- सर्वर-साइड (सुपरयूजर या उचित अनुमतियों की आवश्यकता)
COPY app.users TO '/tmp/users.csv' CSV HEADER;
COPY app.users(email,name) FROM '/tmp/users.csv' CSV HEADER;
-- क्लाइंट-साइड (psql)
\copy app.users TO 'users.csv' CSV HEADER
\copy app.users(email,name) FROM 'users.csv' CSV HEADER
प्रदर्शन & अवलोकन
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...; -- वास्तविक रनटाइम
-- स्टैट्स व्यू
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20; -- एक्सटेंशन की आवश्यकता है
-- रखरखाव
VACUUM [FULL] [VERBOSE] table_name;
ANALYZE table_name;
REINDEX TABLE table_name;
आवश्यकता के अनुसार एक्सटेंशंस सक्षम करें:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- ट्राइग्राम सर्च
फुल-टेक्स्ट सर्च (त्वरित)
ALTER TABLE app.docs ADD COLUMN tsv tsvector;
UPDATE app.docs SET tsv = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
CREATE INDEX docs_tsv_idx ON app.docs USING gin(tsv);
SELECT id FROM app.docs WHERE tsv @@ plainto_tsquery('english', 'quick brown fox');
उपयोगी psql सेटिंग्स
\pset pager off -- पेजर बंद करें
\pset null '∅'
\pset format aligned -- अन्य: अनएलाइन्ड, सीवीएस
\set ON_ERROR_STOP on
\timing on
उपयोगी कैटालॉग क्वेरी
-- टेबल साइज
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
-- इंडेक्स ब्लोट (लगभग)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan ASC NULLS FIRST LIMIT 20;
बैकअप / रिस्टोर
# लॉजिकल डंप
pg_dump -h HOST -U USER -d DB -F c -f db.dump # कस्टम फॉर्मेट
pg_restore -h HOST -U USER -d NEW_DB -j 4 db.dump
# प्लेन SQL
pg_dump -h HOST -U USER -d DB > dump.sql
psql -h HOST -U USER -d DB -f dump.sql
रिप्लिकेशन (उच्च-स्तरीय)
- WAL फाइलें प्राइमरी से स्टैंडबाई की ओर स्ट्रीम होती हैं
- मुख्य सेटिंग्स:
wal_level
,max_wal_senders
,hot_standby
,primary_conninfo
- उपकरण:
pg_basebackup
,standby.signal
(PG ≥12)
गोटचास & टिप्स
- हमेशा
jsonb
का उपयोग करें,json
नहीं, इंडेक्सिंग और ऑपरेटर्स के लिए। timestamptz
(टाइमज़ोन-एवर) का प्राथमिकता दें।DISTINCT ON
“प्रत्येक समूह के लिए टॉप-एन” के लिए पोस्टग्रेस गोल्ड है।GENERATED ALWAYS AS IDENTITY
का उपयोगserial
के बजाय करें।- उत्पादन क्वेरी में
SELECT *
से बचें। - चयनात्मक फ़िल्टर और जॉइन कीज़ के लिए इंडेक्स बनाएं।
EXPLAIN (ANALYZE)
के साथ मापें पहले कि आप अनुकूलन करें।
संस्करण-विशिष्ट नाइसिटीज़ (≥v12+)
-- जनरेटेड आइडेंटिटी
CREATE TABLE t (id bigINT GENERATED ALWAYS AS IDENTITY, ...);
-- अपसर्ट के साथ पार्शियल इंडेक्स
CREATE UNIQUE INDEX ON t (key) WHERE is_active;