PostgreSQL Fundamentals

105 flashcards by MentisLibrary

PostgreSQL specifics: its type system, JSONB, window functions and CTEs, every index type, reading EXPLAIN plans, MVCC and vacuum, roles and security, psql meta-commands, and the extensions worth knowing.

text vs varchar(n) vs char(n) in PostgreSQL

text and varchar are stored identically and perform identically; varchar(n) only adds a length check. char(n) blank-pads to the full width and is almost never what you want.

Which numeric type should hold money?

numeric (also spelled decimal), which is exact base-10 arithmetic with user-defined precision and scale. real and double precision are binary floating point and cannot represent 0.01 exactly.

smallint, integer and bigint

Signed integers of 2, 4 and 8 bytes. integer holds values up to a little over 2.1 billion; bigint up to roughly 9.2 quintillion.

serial vs GENERATED AS IDENTITY

serial is shorthand that creates an integer column, a sequence, and a default. GENERATED ALWAYS AS IDENTITY is the SQL-standard equivalent and is the preferred form in new schemas.

What does timestamptz actually store?

A point in time as UTC, with no time zone attached. On input the value is converted from the session's TimeZone setting, and on output it is converted back.

What is an interval?

A type holding a duration in months, days and microseconds as three separate components, so calendar-aware arithmetic works.

How do array columns work?

Any type can be an array by appending []. Arrays are 1-indexed, support @> containment and = ANY(...) membership, and can be indexed with GIN.

How does NULL behave in comparisons?

Any comparison with NULL yields NULL, which is not true, so a row with a null never satisfies = NULL or <> 'x'. Use IS NULL, IS NOT NULL or IS DISTINCT FROM.

COALESCE vs NULLIF

COALESCE returns its first non-null argument. NULLIF(a, b) returns NULL when a equals b, otherwise a.

How do you define an enumerated type?

With CREATE TYPE ... AS ENUM. Values sort in declaration order, and new values are added with ALTER TYPE ... ADD VALUE.

What is a domain?

A named type built from an existing type plus constraints and an optional default, reusable across tables.

When should a column be uuid?

When ids must be generated without a round trip to the database or must be unguessable. gen_random_uuid() produces a random version 4 UUID.

What is TOAST?

The mechanism that stores oversized column values outside the main table page, compressed and split into chunks, transparently to queries.

What is the difference between json and jsonb?

json stores the exact input text, preserving whitespace, key order and duplicate keys, and reparses it on every access. jsonb stores a decomposed binary form, drops duplicates, does not preserve key order, and supports indexing.

-> vs ->> on a jsonb value

-> extracts a field or array element and returns jsonb. ->> extracts the same thing and returns text.

What do #> and #>> do?

They extract a value at a path given as a text array. #> returns jsonb, #>> returns text.

What does the jsonb @> operator test?

Containment: whether the left document contains the right one as a subset, at any depth, including inside arrays.

What do the jsonb ?, ?| and ?& operators do?

They test top-level key or array element existence: ? for one key, ?| for any key in a list, ?& for all of them.

How do you index a jsonb column?

With a GIN index. The default jsonb_ops opclass indexes every key and value and supports containment plus the existence operators; jsonb_path_ops is smaller and faster but supports only @>.

How do you modify part of a jsonb value?

jsonb_set replaces the value at a path, || merges two objects, and - removes a key or array element.

How do you build jsonb from relational columns?

jsonb_build_object and jsonb_build_array construct values from expressions; to_jsonb converts a row or value; jsonb_agg aggregates rows into an array.

How do you turn a jsonb array into rows?

jsonb_array_elements expands an array into one row per element, and jsonb_each expands an object into key and value rows. Both are set-returning functions, usually used in FROM with LATERAL.

What does jsonb_path_query do?

Evaluates a SQL/JSON path expression against a jsonb value and returns the matching items as rows. @? tests whether a path matches, and @@ evaluates a path predicate.

What is a common table expression?

A named subquery introduced with WITH and referenced later in the same statement. It makes a long query readable by naming its steps.

When does PostgreSQL inline a CTE, and how do you override it?

A plain CTE that is not recursive, has no side effects and is referenced once is inlined into the enclosing query so the planner can push filters into it. MATERIALIZED forces a separate computation and NOT MATERIALIZED forces inlining.

What is a recursive CTE?

WITH RECURSIVE joins a non-recursive anchor term to a recursive term with UNION ALL; the recursive term refers to the CTE itself and runs until it produces no new rows.

What does LATERAL do in a FROM clause?

It lets a subquery or set-returning function on the right reference columns from the items to its left, so it is evaluated once per outer row.

What does DISTINCT ON do?

A PostgreSQL extension that keeps the first row of each group defined by the listed expressions. The ORDER BY decides which row is first and must begin with the same expressions.

How do you write an upsert?

INSERT ... ON CONFLICT (columns) DO UPDATE SET ..., using the EXCLUDED pseudo-table to read the values the insert would have written. DO NOTHING skips conflicting rows instead.

What does RETURNING do?

It makes INSERT, UPDATE and DELETE return rows, using the same expressions a SELECT would.