# PostgreSQL — full corpus # LLM Wiki An open-source template for building LLM-powered knowledge bases, following [Andrej Karpathy's "LLM Wiki" pattern](https://gist.github.com/karpathy/442a6bf555914893e9891c11519de94f). You provide raw sources. The LLM reads them, writes structured wiki pages, cross-links everything, and maintains it over time. You never edit the wiki directly — you curate sources and ask questions. ## How It Works The system has three layers: ``` raw/ Sources you collect (articles, transcripts, notes, PDFs) wiki/ LLM-written & maintained pages (summaries, concepts, entities, syntheses) CLAUDE.md Schema that tells the LLM how to structure everything ``` Three operations drive the workflow: | Operation | Trigger | What happens | |-----------|---------|--------------| | **Ingest** | "ingest raw/my-source.txt" | LLM reads the source, creates a summary page, creates/updates concept and entity pages, adds cross-links, updates the index and log | | **Query** | Ask any question | LLM searches the wiki, synthesizes an answer with citations, optionally creates a synthesis page for novel insights | | **Lint** | "lint" or "health check" | LLM audits all pages for orphans, contradictions, missing links, incomplete sections, and low-confidence claims — fixes what it can, reports the rest | ## Quick Start 1. **Clone this repo** ```bash git clone https://github.com/YOUR_USERNAME/llm-wiki.git my-knowledge-base cd my-knowledge-base ``` 2. **Customize CLAUDE.md** for your domain - Update the Purpose section with your topic - Replace the placeholder tagging taxonomy with your own categories - Adjust confidence level descriptions if needed - Everything else (workflows, page formats, linking rules) works as-is 3. **Drop sources into `raw/`** - Text files, transcripts, articles, notes — any plain text - These are immutable once added; the LLM never modifies them 4. **Tell the LLM to ingest** ``` ingest raw/my-first-source.txt ``` The LLM will create summary pages, concept pages, entity pages, cross-links, and update the index. 5. **Ask questions** ``` What are the key differences between X and Y? ``` The LLM answers from the wiki, citing specific pages. 6. **Run health checks** ``` lint ``` The LLM audits the wiki and fixes issues. ## Directory Structure ``` . ├── CLAUDE.md # Schema — the LLM's instructions ├── raw/ # Your source documents (immutable) └── wiki/ ├── index.md # Master catalog of all pages ├── log.md # Append-only activity log ├── dashboard.md # Dataview dashboard (Obsidian) ├── analytics.md # Charts View analytics (Obsidian) ├── flashcards.md # Spaced repetition cards ├── summaries/ # One page per source document ├── concepts/ # Concept and framework pages ├── entities/ # People, tools, organizations, etc. ├── syntheses/ # Cross-cutting analyses and comparisons ├── journal/ # Research/session journal entries │ └── template.md # Journal entry template └── presentations/ # Marp slide decks ``` ## Enhancements This template includes several extras beyond the core wiki pattern: ### Dataview Dashboard (`wiki/dashboard.md`) Live queries that surface low-confidence pages, recent updates, concepts by tag, and pages with the most sources. Requires the [Dataview](https://github.com/blacksmithgu/obsidian-dataview) Obsidian plugin. ### Charts View Analytics (`wiki/analytics.md`) Visual analytics with pie charts, bar charts, and word clouds. Requires the [Charts View](https://github.com/caronchen/obsidian-chartsview-plugin) Obsidian plugin. ### Mermaid Diagrams Use Mermaid code blocks in any wiki page to create flowcharts, sequence diagrams, or concept maps. Native support in Obsidian and GitHub. ### Marp Slides (`wiki/presentations/`) Create slide decks from markdown using [Marp](https://marp.app/). Drop presentation files in this directory. ### Research Journal (`wiki/journal/`) Track your research sessions, experiments, or applied work with the included template. The LLM can reference journal entries when answering queries. ### Spaced Repetition (`wiki/flashcards.md`) Flashcards in the format used by the [Spaced Repetition](https://github.com/st3v3nmw/obsidian-spaced-repetition) Obsidian plugin. Ask the LLM to generate flashcards from any wiki page. ### MCP Server This repo works with Claude Code's MCP server capabilities. Point an MCP-compatible client at this repo and the LLM can read/write the wiki programmatically. ## Customizing for Your Domain The schema in `CLAUDE.md` is domain-agnostic. To adapt it: 1. **Purpose** — Describe your knowledge domain in one paragraph 2. **Tagging taxonomy** — Replace placeholder categories with your own (e.g., for a cooking KB: `cuisine`, `technique`, `ingredient`, `equipment`) 3. **Confidence levels** — Adjust the descriptions to match your domain's evidence standards 4. **Entity types** — Update the entity page description to match what entities mean in your domain (people, tools, companies, etc.) 5. **Journal template** — Customize `wiki/journal/template.md` for your workflow Everything else — page format, linking conventions, workflows, rules — is universal and works across domains. ## Example Domains This template works for any knowledge-intensive topic: - **Research notes** — papers, experiments, methodologies - **Book analysis** — themes, characters, author techniques - **Competitive analysis** — companies, products, market trends - **Course notes** — lectures, readings, key concepts - **Personal development** — frameworks, habits, book summaries - **Technical documentation** — APIs, architectures, design patterns - **Hobby deep-dives** — any subject you want to master ## License MIT --- title: "PostgreSQL KB — Master Index" type: index updated: 2026-06-25 postgresql_version: "18" --- # PostgreSQL KB — Master Index **Domain:** PostgreSQL — the relational database: SQL & querying, DDL/DML, data types (incl. JSON/JSONB), indexes, transactions & MVCC, performance/EXPLAIN, PL/pgSQL, the psql client, roles & security, and operations. **Corpus:** 47 provenance-stamped sources in `raw/` — curated chapter and subsection pages of the official PostgreSQL 18 documentation (gathered from postgresql.org). **Pages:** 16 (12 concepts · 1 entity · 1 summary · 2 syntheses). ## Concepts (core SQL + how-tos) - [[concepts/sql-basics]] — `SELECT`/`FROM`/`WHERE`, basic queries and joins - [[concepts/schema-and-tables]] — `CREATE TABLE`, columns/defaults, schemas, `ALTER TABLE`, partitioning - [[concepts/constraints]] — `NOT NULL`, `UNIQUE`, `PRIMARY KEY`, `FOREIGN KEY`, `CHECK`, exclusion - [[concepts/data-types]] — numeric, character, date/time, boolean, arrays - [[concepts/json]] — `json` vs `jsonb`, operators (`->`/`->>`/`@>`), functions, GIN indexing - [[concepts/queries-and-joins]] — join types, subqueries, `WITH` (CTEs), set operations - [[concepts/window-and-aggregates]] — window functions (`OVER`/`PARTITION BY`) and aggregates (`FILTER`) - [[concepts/dml]] — `INSERT` (incl. `ON CONFLICT` upsert), `UPDATE`, `DELETE`, `RETURNING` - [[concepts/indexes]] — B-tree/Hash/GiST/SP-GiST/GIN/BRIN, partial & expression indexes - [[concepts/transactions-and-mvcc]] — `BEGIN`/`COMMIT`, MVCC, isolation levels, locking - [[concepts/plpgsql]] — `CREATE FUNCTION ... LANGUAGE plpgsql`, declarations, control structures - [[concepts/roles-and-security]] — roles, `GRANT`/`REVOKE` privileges, Row-Level Security ## Entities - [[entities/psql]] — the psql interactive terminal: connecting and meta-commands (`\dt`, `\d`, `\copy`…) ## Summaries - [[summaries/operations-and-reference-catalog]] — `pg_dump`/`pg_restore` backups + a map of partitioning, full-text search, extensions, replication, and the full SQL/function reference ## Syntheses (decisions & casebooks) - [[syntheses/query-performance-tuning]] — reading `EXPLAIN`/`EXPLAIN ANALYZE`, the planner, and indexing for speed - [[syntheses/data-modeling-and-types]] — choosing types, normalization vs JSONB, constraint and partitioning design ## Statistics - **Total pages**: 16 - **Concepts**: 12 · **Entities**: 1 · **Summaries**: 1 · **Syntheses**: 2 - **Sources ingested**: 47 (raw/, immutable) - **High confidence**: 14 · **Medium confidence**: 2 · **Low confidence**: 0 ## Coverage notes Strong: SQL and querying (joins/CTEs/windows), DDL/DML with verbatim syntax, data types incl. JSONB, indexes, transactions/MVCC, PL/pgSQL, roles/security, and psql. Pinned to PostgreSQL 18; freshness = source fetch date 2026-06-25. Mapped, not paged (see [[summaries/operations-and-reference-catalog]]): the exhaustive SQL-command and function reference, partitioning deep-dive, full-text search, the extension ecosystem, and replication/HA. For those, use postgresql.org/docs. --- title: "Constraints" type: concept tags: [constraints, primary-key, foreign-key, check, unique] updated: 2026-06-25 confidence: high sources: [raw/web_community-postgresql-documentation-18-5-5-constraints.md] --- # Constraints Constraints give you control over the data in a table beyond what data types alone allow. If a user attempts to store data that would violate a constraint, an error is raised — even if the value came from a default value definition. Constraints can be written as **column constraints** (attached to one column) or **table constraints** (listed separately in the column list). Any constraint can be given a name with the `CONSTRAINT` keyword. ## CHECK A `CHECK` constraint requires that a column value satisfy a Boolean expression: ```sql CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price) ); ``` A check constraint is satisfied if the expression evaluates to **true or the null value**. Because most expressions return null when any operand is null, `CHECK` does not by itself prevent nulls. PostgreSQL does not support `CHECK` constraints that reference table data other than the new or updated row. ## NOT NULL A not-null constraint specifies that a column must not be null: ```sql CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) ); ``` It is functionally equivalent to `CHECK (column_name IS NOT NULL)` but more efficient. A column can have at most one explicit not-null constraint. In most designs, the majority of columns should be marked not null. ## UNIQUE Unique constraints ensure values are unique across all rows, for a single column or a group: ```sql CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); ``` Adding a unique constraint automatically creates a unique B-tree index. By default two null values are **not** considered equal, so duplicate rows containing a null in a constrained column are allowed; add `NULLS NOT DISTINCT` to treat nulls as equal (the default is `NULLS DISTINCT`). ## PRIMARY KEY A primary key requires values that are both **unique and not null**, identifying each row: ```sql CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) ); ``` It automatically creates a unique B-tree index and forces the column(s) to `NOT NULL`. A table can have at most one primary key. ## FOREIGN KEY A foreign key requires that values match a row in another table, maintaining **referential integrity**. `REFERENCES` with no column list uses the referenced table's primary key: ```sql CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) ); ``` The default `ON DELETE` (and `ON UPDATE`) action is `NO ACTION`. Other actions are `RESTRICT`, `CASCADE`, `SET NULL`, and `SET DEFAULT`. A referenced row must form a primary key, a unique constraint, or a non-partial unique index; referencing columns are **not** indexed automatically. ## EXCLUSION Exclusion constraints ensure no two rows compare as "true" on the given operators: ```sql CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) ); ``` Adding one automatically creates an index of the specified type. ## See also - [[concepts/schema-and-tables]] — `CREATE TABLE` and `ALTER TABLE` - [[concepts/indexes]] — the indexes constraints create - [[concepts/data-types]] — column types being constrained --- title: "Data Types" type: concept tags: [data-types, numeric, character, datetime, arrays] updated: 2026-06-25 confidence: high sources: [raw/web_community-postgresql-documentation-18-chapter-8-data-types.md, raw/web_community-postgresql-documentation-18-8-1-numeric-types.md, raw/web_community-postgresql-documentation-18-8-3-character-types.md, raw/web_community-postgresql-documentation-18-8-5-date-time-types.md, raw/web_community-postgresql-documentation-18-8-15-arrays.md] --- # Data Types PostgreSQL has a rich set of native types; users can add more with `CREATE TYPE`. Many built-ins have alias names. This page covers the major families with their exact names. ## Numeric types - Integers: `smallint` (`int2`, 2 bytes), `integer` (`int`, `int4`, 4 bytes), `bigint` (`int8`, 8 bytes). `integer` is the common choice. - Exact: `numeric(precision, scale)` (alias `decimal`) — recommended for monetary amounts. `numeric(3, 1)` rounds to one decimal place and stores -99.9 to 99.9. Bare `numeric` is unconstrained. - Floating point: `real` (`float4`, ~6 digits), `double precision` (`float8`, ~15 digits) — inexact. - Auto-incrementing: `smallserial` (`serial2`), `serial` (`serial4`), `bigserial` (`serial8`); `serial` creates an `integer` column with a `nextval()` default plus `NOT NULL`. ## Character types - `character varying(n)` (alias `varchar(n)`) — variable-length with limit. - `character(n)` (aliases `char(n)`, `bpchar(n)`) — fixed-length, blank-padded. - `text` — variable, unlimited length; PostgreSQL's native string type. Storing a string longer than `n` errors (unless excess is spaces); `varchar` without a length accepts any length. There is no performance advantage to `char(n)` — prefer `text` or `varchar`. ```sql CREATE TABLE test2 (b varchar(5)); INSERT INTO test2 VALUES ('ok'); ``` ## Boolean - `boolean` (alias `bool`). ## Date/time types - `date` — date only (no time of day). - `time [ (p) ] [ without time zone ]` and `time [ (p) ] with time zone` (alias `timetz`). - `timestamp [ (p) ] [ without time zone ]` and `timestamp [ (p) ] with time zone` (alias `timestamptz`). - `interval [ fields ] [ (p) ]` — a time span; `fields` may restrict to e.g. `YEAR TO MONTH` or `DAY TO SECOND`. Precision `p` (fractional seconds) ranges 0–6. Literals are single-quoted: `TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'`, `INTERVAL '1 year 2 months 3 days'`. Use `CURRENT_DATE` / `CURRENT_TIMESTAMP` for current values. `timestamptz` values are stored as UTC and shown in the session `TimeZone`. ## Other families - `json` (text) and `jsonb` (binary, decomposed) — see [[concepts/json]]. - `uuid`, `xml`, `bytea` (binary), `money`, network types (`inet`, `cidr`, `macaddr`). - Geometric, bit strings (`bit`, `bit varying`/`varbit`), text search (`tsvector`, `tsquery`), enums (`CREATE TYPE ... AS ENUM`), composite, range, and domain types. ## Arrays Append `[]` to any type to make an array; multidimensional arrays are allowed (the declared size/dimensions are not enforced): ```sql CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); ``` Array literals use curly braces; the `ARRAY` constructor uses brackets. Elements are 1-based; search with `ANY`/`ALL`: ```sql INSERT INTO sal_emp VALUES ('Bill', '{10000,10000,10000,10000}', '{{"meeting","lunch"},{"training","presentation"}}'); INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000,25000,25000,25000], ARRAY[['breakfast','consulting'],['meeting','lunch']]); SELECT pay_by_quarter[3] FROM sal_emp; SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); ``` ## See also - [[concepts/json]] — `json` and `jsonb` in depth - [[concepts/schema-and-tables]] — assigning types to columns - [[concepts/constraints]] — constraining column values - [[syntheses/data-modeling-and-types]] — choosing the right type --- title: "Data Manipulation: INSERT, UPDATE, DELETE" type: concept tags: [insert, update, delete, upsert, returning] updated: 2026-06-25 confidence: high sources: [raw/web_community-postgresql-documentation-18-chapter-6-data-manipulation.md, raw/web_community-postgresql-documentation-18-6-1-inserting-data.md, raw/web_community-postgresql-documentation-18-6-2-updating-data.md, raw/web_community-postgresql-documentation-18-6-3-deleting-data.md, raw/web_community-postgresql-documentation-18-6-4-returning-data-from-modified.md, raw/web_community-postgresql-documentation-18-13-2-transaction-isolation.md] --- # Data Manipulation: INSERT, UPDATE, DELETE Data manipulation language (DML) fills and changes table contents. Each statement runs inside a transaction (see [[concepts/transactions-and-mvcc]]). ## INSERT `INSERT` adds rows. Values may be given positionally or with an explicit column list; omitted columns take their defaults: ```sql INSERT INTO products VALUES (1, 'Cheese', 9.99); INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99); INSERT INTO products (product_no, name) VALUES (1, 'Cheese'); INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT); INSERT INTO products DEFAULT VALUES; ``` Insert multiple rows, or the result of a query: ```sql INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99); INSERT INTO products (product_no, name, price) SELECT product_no, name, price FROM new_products WHERE release_date = 'today'; ``` For bulk loads, `COPY` is faster than `INSERT`. ## UPDATE `UPDATE ... SET ... WHERE` changes columns of matching rows; the new value can be any expression and may reference the existing value. Omitting `WHERE` updates every row. ```sql UPDATE products SET price = 10 WHERE price = 5; UPDATE products SET price = price * 1.10; UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0; ``` ## DELETE `DELETE FROM ... WHERE` removes matching rows; without `WHERE` it removes them all. ```sql DELETE FROM products WHERE price = 10; DELETE FROM products; -- removes every row ``` ## Upsert: INSERT ... ON CONFLICT An `ON CONFLICT` clause handles uniqueness conflicts instead of erroring: `ON CONFLICT DO NOTHING` skips conflicting rows, while `ON CONFLICT (...) DO UPDATE` performs an "upsert" (the recommended alternative to a manual `UPDATE`-then-`INSERT` loop). Under Read Committed, each proposed row is guaranteed to either insert or update. ## RETURNING `INSERT`, `UPDATE`, and `DELETE` accept a `RETURNING` clause (same form as a `SELECT` list, or `RETURNING *`) to return modified-row data without a separate query — handy for generated keys: ```sql INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id; UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price; DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *; ``` `RETURNING` can also reference `old.` and `new.` values, and works for data-modifying statements inside a `WITH` query (see [[concepts/queries-and-joins]]). --- title: "Indexes and Index Types" type: concept tags: [indexes, btree, gin, gist, brin, create-index] updated: 2026-06-25 confidence: high sources: [raw/web_community-postgresql-documentation-18-chapter-11-indexes.md, raw/web_community-postgresql-documentation-18-11-2-index-types.md] --- # Indexes and Index Types Indexes let the server find rows much faster than scanning a table, at the cost of write and storage overhead, so use them sensibly. `CREATE INDEX` creates a **B-tree** index by default; other types are chosen with `USING`: ```sql CREATE INDEX name ON table USING HASH (column); ``` ## Index types PostgreSQL provides B-tree, Hash, GiST, SP-GiST, GIN, and BRIN (plus the `bloom` extension). Each suits different query shapes: - **B-tree** — equality and range queries on sortable data, using `< <= = >= >`. Also supports `BETWEEN`, `IN`, `IS NULL`/`IS NOT NULL`, anchored pattern matches like `col LIKE 'foo%'` or `col ~ '^foo'`, and retrieving rows in sorted order. The default and most common type. - **Hash** — stores a 32-bit hash; handles only simple equality (`=`). - **GiST** — an infrastructure for many strategies (geometric types, full-text, etc.); operators depend on the operator class. Supports nearest-neighbor ordering, e.g. `SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;`. - **SP-GiST** — space-partitioned trees (quadtrees, k-d trees, radix tries); also supports nearest-neighbor searches. - **GIN** — "inverted index" for values containing multiple components (arrays, `jsonb`, full-text); efficient at testing for the presence of component values (`<@ @> = &&`). See [[concepts/json]]. - **BRIN** — Block Range INdexes store per-block-range summaries (min/max); compact and effective for columns physically correlated with row order. ## Multicolumn, unique, partial, and expression indexes Beyond single-column indexes, PostgreSQL supports: - **Multicolumn indexes** spanning several columns. - **Unique indexes** enforcing uniqueness of the indexed values (see [[concepts/constraints]]). - **Indexes on expressions** (e.g. on `lower(col)`) so a computed value can be searched. - **Partial indexes** covering only rows that satisfy a `WHERE` predicate. - **Index-only scans and covering indexes**, which answer a query from the index alone. Choosing the right index is central to [[syntheses/query-performance-tuning]]; examine usage via the planner and statistics views. --- title: "JSON and JSONB" type: concept tags: [json, jsonb, operators, gin-index] updated: 2026-06-25 confidence: high sources: [raw/web_community-postgresql-documentation-18-8-14-json-types.md, raw/web_community-postgresql-documentation-18-9-16-json-functions-and-operator.md] --- # JSON and JSONB PostgreSQL offers two types for storing JSON data: `json` and `jsonb`. Both accept almost identical input. The difference is efficiency: `json` stores an exact copy of the input text (preserving whitespace, key order, and duplicate keys) and must reparse it on each execution, while `jsonb` stores a decomposed binary format that is slightly slower to input but much faster to process. **Most applications should prefer `jsonb`**, which also supports indexing. ```sql SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json; SELECT '[1, 2, "foo", null]'::jsonb; ``` `jsonb` does not preserve insignificant whitespace, object key order, or duplicate keys (the last value wins). ## Access operators These operators work on both `json` and `jsonb`: - `->` — extracts an array element (by `integer`) or object field (by `text`), returning `json`/`jsonb`. - `->>` — same, but returns the value as `text`. - `#>` — extracts the sub-object at a `text[]` path, returning `json`/`jsonb`. - `#>>` — same, but returns `text`. ```sql SELECT '{"a": {"b":"foo"}}'::json -> 'a'; -- {"b":"foo"} SELECT '[1,2,3]'::json ->> 2; -- 3 SELECT '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}'; -- "bar" ``` Array elements are indexed from zero; negative integers count from the end. These extraction operators return `NULL` rather than failing if the structure does not match. ## jsonb-only operators - `@>` / `<@` — containment: does the first value contain the second (or vice versa)? - `?` — does a `text` string exist as a top-level key or array element? - `?|` / `?&` — do **any** / **all** of the strings in a `text[]` exist as top-level keys/elements? - `||` — concatenate two `jsonb` values; `-` deletes a key/element; `#-` deletes at a path. - `@?` / `@@` — `jsonpath` match operators. ```sql SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; -- t SELECT '["a", "b", "c"]'::jsonb ? 'b'; -- t SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']; -- t ``` ## Key functions - Build values: `to_jsonb()`, `jsonb_build_object()`, `jsonb_build_array()`, `jsonb_object()`. - Expand: `jsonb_array_elements()`, `jsonb_each()`, `jsonb_object_keys()`, `jsonb_to_recordset()`. - Modify: `jsonb_set()`, `jsonb_insert()`, `jsonb_strip_nulls()`. - Query with paths: `jsonb_path_query()`, `jsonb_path_exists()`, `jsonb_path_match()`. ```sql SELECT jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= 2 && @ <= 4)'); ``` ## Indexing jsonb (GIN) `jsonb` supports **GIN** indexes for efficient searching. The default operator class (`jsonb_ops`) supports the key-exists operators `?`, `?|`, `?&`, the containment operator `@>`, and the jsonpath match operators `@?` and `@@`: ```sql CREATE INDEX idxgin ON api USING GIN (jdoc); ``` The non-default `jsonb_path_ops` operator class supports only `@>`, `@?`, and `@@`, but produces smaller, more specific indexes: ```sql CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops); ``` Such an index can accelerate containment queries like `WHERE jdoc @> '{"company": "Magnafone"}'`. Expression indexes (e.g. `GIN ((jdoc -> 'tags'))`) can target a specific key. `jsonb` also supports `btree` and `hash` indexes, useful mainly for whole-document equality. ## See also - [[concepts/data-types]] — the full type catalog - [[concepts/indexes]] — GIN and other index types - [[concepts/queries-and-joins]] — using these operators in `WHERE` --- title: "PL/pgSQL — SQL Procedural Language" type: concept tags: [plpgsql, functions, procedures, triggers] updated: 2026-06-25 confidence: high sources: [raw/web_community-postgresql-documentation-18-chapter-41-pl-pgsql-sql-procedur.md, raw/web_community-postgresql-documentation-18-41-2-structure-of-pl-pgsql.md, raw/web_community-postgresql-documentation-18-41-3-declarations.md, raw/web_community-postgresql-documentation-18-41-6-control-structures.md] --- # PL/pgSQL — SQL Procedural Language PL/pgSQL is PostgreSQL's loadable procedural language. It groups computation and SQL queries inside the server with control structures and inherits all of PostgreSQL's types, functions, and operators. See [[concepts/sql-basics]] for the SQL these blocks run. ## Defining functions and procedures Code lives in `CREATE FUNCTION` / `CREATE PROCEDURE` with body `LANGUAGE plpgsql`. Dollar quoting (`$$ ... $$`) avoids escaping inner quotes: ```sql CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql; ``` A function returns a value; a procedure (called with `CALL`) does not and can manage transactions. `OUT` parameters return multiple values, and `RETURNS TABLE(...)` / `RETURNS SETOF` declare set-returning functions. ## Block structure PL/pgSQL is block-structured. Each block has an optional `DECLARE` section and a mandatory `BEGIN ... END`: ```sql [ <