Step 3 / Select topic Postgres Medium
What this bank covers
- SERIAL is sugar, not a type
- RETURNING
- Default index access method
- TRUNCATE and triggers
- EXPLAIN vs EXPLAIN ANALYZE
- NULL and UNIQUE
- VACUUM
- JSON vs JSONB
- psql meta-commands
- ON CONFLICT (upsert)
- Window function PARTITION BY
- ROW_NUMBER vs RANK on ties
- LAG / LEAD
- Aggregate FILTER clause
- ARRAY_AGG
- STRING_AGG
- WITH TIES
- WITH ... AS (CTE)
- WITH RECURSIVE
- CTE inlining default
- LATERAL joins
- Array ANY operator
- Array containment @>
- Array overlap &&
- Array indexing base
- unnest()
- WITH ORDINALITY
- cardinality() vs array_length()
- DISTINCT ON
- ON DELETE CASCADE
- ON DELETE SET NULL
- SAVEPOINT
- Setting isolation level
- FOR UPDATE vs FOR SHARE
- Partial index use case
- Expression (functional) indexes
- EXCLUDE constraint access methods
- Multi-column CHECK constraints
- Deferrable constraints
- CREATE DOMAIN
- PARTITION BY RANGE
- PARTITION BY LIST
- INHERITS and the ONLY keyword
- pg_stat_activity
- Cancel vs terminate backend
- SHOW ALL vs pg_settings
- ALTER SYSTEM
- pg_total_relation_size
- COPY for bulk load
- \copy vs COPY
- nextval()
- setval()
- Sequence OWNED BY
- information_schema vs pg_catalog
- to_regclass()
- ctid system column
- to_tsvector
- to_tsquery and @@
- int4range
- tsrange overlap
- jsonb_set()
- jsonb -> vs ->>
- #> and #>> path operators
- Composite types
- ENUM types
- gen_random_uuid()
- IDENTITY columns
- Generated (computed) columns
- SIMILAR TO vs ~
- ILIKE
- now() vs clock_timestamp()
- INTERVAL arithmetic
- date_trunc()
- EXTRACT(dow ...)
- pg_trgm extension
- CREATE EXTENSION
- postgres_fdw (Foreign Data Wrapper)
- Materialized views
- Views vs materialized views
- LISTEN / NOTIFY
- GRANT privileges
- Roles unify users and groups
- search_path
- TABLESAMPLE
- IS DISTINCT FROM
- generate_series()
- Row constructors
- Set-returning functions in FROM
- format()
- CLUSTER
- Fast column defaults
- DROP ... CASCADE
- TRUNCATE RESTART IDENTITY
- pg_locks
- txid_current() and virtual vs real transaction IDs
- current_database() vs current_user
- Schema-qualified table names
- pg_dump vs pg_dumpall
- pg_restore parallel jobs
- COMMENT ON
- Keyset pagination
- Filter dates with ranges
- IS NOT DISTINCT FROM does not use B-tree indexes
- Correlated subqueries run once per row