I85PREP
⌘ K
EDB PostgreSQL Associate study materials
EDB PostgreSQL Associate study resources

EDB PostgreSQL Associate practice questions

One question at a time. Build your EDB PostgreSQL Associate understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1PostgreSQL Architecture and Installation

Which PostgreSQL server process listens for incoming client connections and forks a dedicated backend process for each accepted session?

  1. The checkpointer process
  2. The WAL writer process
  3. The autovacuum launcher
  4. The postmaster process Correct answer.

Explanation

The postmaster is the parent server process started by pg_ctl; it owns the listening socket and creates a new backend process per connection. Backend processes then parse, plan, and execute the session's SQL, while the other processes listed are background workers.

Question 2PostgreSQL Architecture and Installation

Which utility must be run on an empty or newly created directory before that directory can be used as a PostgreSQL data directory?

  1. pg_ctl
  2. createdb
  3. pg_resetwal
  4. initdb Correct answer.

Explanation

initdb builds a new cluster in the target directory: it creates the catalog relations, the template databases, and the configuration files. Only after initdb completes can the postmaster be started against that directory.

Question 3PostgreSQL Architecture and Installation

Which memory area is allocated once at server start and shared by all backend processes as a cache of data pages read from disk?

  1. shared_buffers Correct answer.
  2. work_mem
  3. maintenance_work_mem
  4. temp_buffers

Explanation

shared_buffers defines the size of the buffer pool held in shared memory and visible to every backend. The other three parameters size per-backend or per-operation memory, not a cluster-wide page cache.

Question 4PostgreSQL Architecture and Installation

When no port setting is present in postgresql.conf, which TCP port does the PostgreSQL server listen on by default?

  1. 1521
  2. 5432 Correct answer.
  3. 3306
  4. 5433

Explanation

The port parameter defaults to 5432, and a server started without an explicit port setting binds that port (subject to listen_addresses). Port 5433 is commonly used only for a second cluster on the same host.

Question 5PostgreSQL Architecture and Installation

Which environment variable tells PostgreSQL client and server utilities the location of the data directory when no -D option is supplied?

  1. PGDATA Correct answer.
  2. PGDIR
  3. PGHOME
  4. PGROOT

Explanation

PGDATA holds the path of the cluster's data directory and is consulted by utilities such as psql, pg_ctl, and pg_basebackup when -D is omitted. It is set by packaging scripts and by initdb's output messages for convenience.

Question 6PostgreSQL Architecture and Installation

Which configuration file determines which client hosts may connect, to which databases, and with which authentication method?

  1. pg_ctl.conf
  2. pg_hba.conf Correct answer.
  3. postgresql.conf
  4. pg_ident.conf

Explanation

pg_hba.conf contains host-based authentication rules that are evaluated in order for each incoming connection attempt. postgresql.conf holds general server parameters, while pg_ident.conf only maps operating system users to database roles.

Question 7PostgreSQL Architecture and Installation

What is the primary purpose of the write-ahead log in PostgreSQL?

  1. Logging changes before data pages are flushed Correct answer.
  2. Recording per-table statistics for the planner
  3. Holding temporary sort and hash spill files
  4. Caching query plans for repeated execution

Explanation

The WAL rule requires that a change's log record reach durable storage before the corresponding data page is written, so a crash can be repaired by replaying the log. This is what allows random data page writes to be deferred while durability is preserved.

Question 8PostgreSQL Architecture and Installation

Which database does CREATE DATABASE use as its source template when no TEMPLATE clause is specified?

  1. template0
  2. pg_default
  3. postgres
  4. template1 Correct answer.

Explanation

The default template is template1, which is why objects added to template1 appear in every subsequently created database. template0 is used explicitly when a pristine database with a different encoding or locale is required.

Question 9PostgreSQL Architecture and Installation

Which pg_ctl shutdown mode terminates existing client sessions, then performs a clean checkpoint and shutdown?

  1. immediate
  2. smart
  3. fast Correct answer.
  4. abort

Explanation

Fast mode sends SIGINT to the postmaster: current sessions are disconnected, a shutdown checkpoint is taken, and the cluster stops cleanly. Smart mode instead waits for clients to disconnect on their own, while immediate mode skips the orderly shutdown entirely.

Question 10PostgreSQL Architecture and Installation

Which background process writes all dirty shared buffers to disk during a checkpoint and records the checkpoint position in the control file?

  1. The background writer
  2. The checkpointer Correct answer.
  3. The WAL writer
  4. The autovacuum launcher

Explanation

The checkpointer performs the checkpoint cycle: it flushes dirty buffers, writes a checkpoint record, and updates pg_control so recovery knows where to begin. The background writer only performs continuous light cleaning of buffers between checkpoints.

Question 11PostgreSQL Architecture and Installation

In PostgreSQL, what is a tablespace?

  1. A synonym for an entire database cluster
  2. A partitioning strategy that splits a table into range-based child tables
  3. A directory location outside PGDATA where relation files can be stored Correct answer.
  4. A logical schema that groups related objects

Explanation

A tablespace is a named mapping to a filesystem directory in which relation files for selected tables and indexes are placed, letting administrators spread I/O across volumes. It is referenced by schema-qualified objects but is neither a schema nor a partition scheme.

Question 12PostgreSQL Architecture and Installation

In PostgreSQL's multiversion concurrency control, what does the xmin field of a heap tuple record?

  1. The transaction that deleted the tuple
  2. The transaction that created the tuple version Correct answer.
  3. The OID of the table that owns the tuple
  4. The last time the tuple was vacuumed

Explanation

Each row version stores xmin, the transaction ID that inserted it, and xmax, the transaction ID that deleted or superseded it. Visibility rules compare these values with the current snapshot to decide whether a version is seen.

Question 13PostgreSQL Architecture and Installation

What is the purpose of the TOAST mechanism?

  1. To cache frequently accessed rows in shared memory
  2. To automatically convert ordinary tables into partitioned tables
  3. To compress the WAL stream before archiving
  4. To store large column values out of line in a side table Correct answer.

Explanation

TOAST keeps heap pages within the block size limit by moving oversized variable-length values into a companion TOAST table, compressing or slicing them as needed. The main table then stores only a small pointer for that column.

Question 14PostgreSQL Architecture and Installation

Which system catalog contains one row for every relation in a database, including tables, indexes, sequences, and views?

  1. pg_class Correct answer.
  2. pg_attribute
  3. pg_namespace
  4. pg_type

Explanation

pg_class is the central relation catalog; its relkind column distinguishes tables, indexes, sequences, views, and other relation types. Columns of those relations are recorded in pg_attribute, and their schemas in pg_namespace.

Question 15PostgreSQL Architecture and Installation

Which parameter turns on the automatic removal of dead row versions by background workers, which also protects against transaction ID wraparound?

  1. vacuum_cost_delay
  2. autovacuum_vacuum_threshold
  3. autovacuum Correct answer.
  4. track_counts

Explanation

Setting autovacuum to on enables the launcher, which starts worker processes to vacuum and analyze tables as they accumulate changes. The threshold and cost parameters only tune how those workers behave once the feature is enabled.

Question 16PostgreSQL Architecture and Installation

Which view should an administrator query to see the current state and SQL text of every active server session?

  1. pg_locks
  2. pg_stat_activity Correct answer.
  3. pg_stat_user_tables
  4. pg_roles

Explanation

pg_stat_activity has one row per server process, showing the connected database, user, client address, state, and current or last query. It is the standard tool for spotting long-running or idle-in-transaction sessions.

Question 17PostgreSQL Architecture and Installation

Besides setting archive_command, which parameter must be enabled for continuous WAL archiving to take place?

  1. full_page_writes
  2. wal_keep_size
  3. archive_mode Correct answer.
  4. fsync

Explanation

Archiving requires both archive_mode set to on (or always) and a valid archive_command; the archiver process only starts when archive_mode is enabled. The WAL level must also be at least replica for the archive to be usable for recovery.

Question 18PostgreSQL Architecture and Installation

What happens once the number of ordinary client connections reaches the max_connections limit?

  1. Superusers can still connect using the reserved connection slots Correct answer.
  2. The postmaster refuses all further connections immediately
  3. Existing sessions are terminated to make room for new ones
  4. A second postmaster is started automatically to accept the overflow

Explanation

superuser_reserved_connections slots are held back so that a superuser can always log in to diagnose or terminate sessions when ordinary slots are exhausted. Non-superusers receive a 'too many clients' error until a slot frees up.

Question 19PostgreSQL Architecture and Installation

Starting with PostgreSQL 10, what does the first component of a version number such as 15.4 represent?

  1. The number of operating systems supported by that release
  2. The minor release level, which changes only for bug fixes and security patches
  3. The build number of the binary package produced by the packager
  4. The major version, which changes only with pg_upgrade-level changes Correct answer.

Explanation

From version 10 onward PostgreSQL uses a two-part scheme: the major version (15) changes only when an upgrade such as pg_upgrade or a dump and restore is required, and the minor version (4) changes for fix-only releases. Minor upgrades are therefore binary-compatible within a major version.

Question 20PostgreSQL Architecture and Installation

How can an administrator make the server pick up a change to a postgresql.conf parameter that does not require a restart?

  1. Send SIGHUP to the postmaster, for example with pg_ctl reload Correct answer.
  2. Run initdb against the existing data directory
  3. Stop and start the postmaster with pg_ctl -m immediate
  4. Execute CHECKPOINT in the target database

Explanation

SIGHUP, delivered by pg_ctl reload or SELECT pg_reload_conf(), makes the postmaster re-read postgresql.conf and relay the values to backends. Parameters marked postmaster in the documentation still need a full restart, and CHECKPOINT is unrelated to configuration reloading.

Question 21PostgreSQL Architecture and Installation

Which parameter sets the age at which autovacuum forcibly freezes tuples in a table to protect against transaction ID wraparound, even if normal vacuum thresholds are not met?

  1. autovacuum_freeze_max_age Correct answer.
  2. vacuum_cost_limit
  3. old_snapshot_threshold
  4. vacuum_freeze_min_age

Explanation

When a table's oldest unfrozen XID reaches autovacuum_freeze_max_age (200 million by default), an anti-wraparound autovacuum is launched regardless of the usual dead-tuple thresholds. vacuum_freeze_min_age instead sets the youngest age at which tuples may be frozen during a vacuum.

Question 22PostgreSQL Architecture and Installation

What information does the visibility map provide to vacuum and to index-only scans?

  1. Which tuples were modified by the current transaction
  2. Which indexes contain entries pointing at a given heap page
  3. Which pages have free space for new tuple insertions
  4. Which pages contain only tuples visible to all transactions Correct answer.

Explanation

The visibility map keeps two bits per heap page: all-visible and all-frozen. Index-only scans can skip the heap fetch for all-visible pages, and vacuum can skip them when freezing.

Question 23PostgreSQL Architecture and Installation

Which wal_level setting is the minimum required for logical decoding to be possible?

  1. logical Correct answer.
  2. archive
  3. minimal
  4. replica

Explanation

Logical decoding needs the extra information that wal_level = logical adds, such as replication identity and type metadata, beyond what replica level provides. Setting wal_level to logical also satisfies the requirements for physical replication and archiving.

Question 24PostgreSQL Architecture and Installation

After an unclean shutdown, where does the startup process begin applying WAL records during crash recovery?

  1. At the oldest WAL segment still present in pg_wal
  2. At the creation point of the current timeline history file
  3. At the redo location of the last checkpoint recorded in pg_control Correct answer.
  4. At the first record of the most recent WAL segment

Explanation

The checkpoint record stores a redo LSN, and pg_control stores the pointer to the latest checkpoint, so recovery replays from that redo point forward rather than from the start of the log. Checkpointing thus bounds how much WAL must be replayed after a crash.

Question 25PostgreSQL Architecture and Installation

Ignoring tablespaces, where is the operating system file for an ordinary table with relfilenode 16384 in a database whose OID is 13207 located inside the data directory?

  1. global/13207/16384
  2. base/13207/16384 Correct answer.
  3. pg_tblspc/13207/16384
  4. pg_wal/13207/16384

Explanation

Non-shared relations are stored under base/<database OID>/<relfilenode>, and objects placed in a user tablespace instead appear under pg_tblspc via a symbolic link. Shared catalogs live in the global directory, not under a per-database OID.

Question 26SQL Fundamentals and Data Types

Which PostgreSQL data type stores fixed-length strings that are blank-padded to the declared length?

  1. char(10) Correct answer.
  2. varchar(10)
  3. name
  4. text

Explanation

char(n) always stores exactly n characters, padding shorter values with spaces and raising an error for longer ones. The other character types store only the characters supplied, with no padding.

Question 27SQL Fundamentals and Data Types

Which data type should be chosen for a monetary amount that must be stored exactly with two decimal places?

  1. integer
  2. double precision
  3. real
  4. numeric(12,2) Correct answer.

Explanation

numeric(precision, scale) stores exact decimal values, and a scale of 2 keeps exactly two digits after the decimal point. Floating-point types store binary approximations and can produce rounding artifacts.

Question 28SQL Fundamentals and Data Types

Which values can be stored in a column of type boolean?

  1. TRUE, FALSE, and the empty string
  2. TRUE, FALSE, and NULL Correct answer.
  3. Only TRUE and FALSE
  4. 0, 1, and NULL only

Explanation

A boolean column holds true, false, or NULL, since NULL is permitted unless the column is declared NOT NULL. Literals such as 't', 'yes', '1', and '0' are accepted as input spellings but the stored values are boolean.

Question 29SQL Fundamentals and Data Types

Which range of integer values can be stored in a smallint column?

  1. 0 to 65535
  2. -32768 to 32767 Correct answer.
  3. -128 to 127
  4. -2147483648 to 2147483647

Explanation

smallint is a signed 2-byte integer, giving a range of -32768 to 32767. PostgreSQL integer types are signed, so no unsigned variant exists.

Question 30SQL Fundamentals and Data Types

Given the expression COALESCE(NULL, NULL, 'alpha', 'beta'), what is returned?

  1. An error is raised
  2. NULL
  3. 'beta'
  4. 'alpha' Correct answer.

Explanation

COALESCE returns the first argument that is not NULL, evaluating arguments left to right. The first two arguments are NULL, so the third argument is the result and later arguments are not reached.

Question 31SQL Fundamentals and Data Types

What does the DISTINCT keyword in SELECT DISTINCT dept FROM employee do to the result?

  1. It sorts the result by dept.
  2. It groups rows for aggregate functions.
  3. It keeps only rows where dept is not null.
  4. It removes duplicate result rows. Correct answer.

Explanation

DISTINCT compares the selected output rows and eliminates duplicates, leaving one row per distinct combination of selected values. It does not order the output and does not filter NULLs.

Question 32SQL Fundamentals and Data Types

Which clause restricts a query result to at most 10 rows?

  1. ROWNUM <= 10
  2. SAMPLE 10
  3. LIMIT 10 Correct answer.
  4. MAXROWS 10

Explanation

LIMIT n specifies the maximum number of rows a query returns, and it is normally paired with ORDER BY to make the selected rows deterministic. OFFSET can skip rows before the limit is applied.

Question 33SQL Fundamentals and Data Types

An INSERT statement omits a column that was defined with a DEFAULT expression. What is stored in that column for the new row?

  1. The value taken from the previous row
  2. NULL, always
  3. An error is raised
  4. Its default expression value Correct answer.

Explanation

When a column is omitted from an INSERT and has a default, PostgreSQL evaluates the default expression and stores the result. A NULL is stored only when no default exists and the column is nullable.

Question 34SQL Fundamentals and Data Types

A column is declared PRIMARY KEY. Which two properties does this declaration automatically enforce on the column?

  1. NOT NULL and UNIQUE Correct answer.
  2. UNIQUE and CHECK
  3. DEFAULT and UNIQUE
  4. NOT NULL and FOREIGN KEY

Explanation

A primary key is by definition a unique, non-null identifier for each row, so declaring one implicitly adds NOT NULL and uniqueness. PostgreSQL implements the uniqueness with a unique index that also backs lookups.

Question 35SQL Fundamentals and Data Types

A table contains 100 rows, and 20 of them hold NULL in column c. What does SELECT count(c) FROM t return?

  1. 20
  2. 80 Correct answer.
  3. NULL
  4. 100

Explanation

count(expression) counts only the rows where the expression evaluates to a non-null value, so the 20 NULLs are skipped. count(*) would instead count all 100 rows.

Question 36SQL Fundamentals and Data Types

For the query SELECT dept, count(*) FROM employee GROUP BY dept, what does each output row represent?

  1. One row for each storage block of the table
  2. The whole-table total
  3. One dept value with its count Correct answer.
  4. A single employee row

Explanation

GROUP BY collapses the input rows into one group per distinct value of the grouping expression, and each output row reports that value with the aggregate computed over its rows. Non-aggregated columns in the select list must appear in the GROUP BY clause.

Question 37SQL Fundamentals and Data Types

Which clause filters groups after aggregation has been performed?

  1. WHERE
  2. HAVING Correct answer.
  3. FILTER
  4. QUALIFY

Explanation

HAVING is evaluated after GROUP BY, so its conditions can reference aggregate results and it discards whole groups. WHERE runs before grouping and therefore cannot reference aggregates.

Question 38SQL Fundamentals and Data Types

An INSERT ... ON CONFLICT DO NOTHING statement tries to insert a row that violates a unique constraint. What is the outcome?

  1. A new unique value is generated for the column
  2. The conflicting row is deleted first
  3. The statement fails and the transaction aborts
  4. The row is skipped and no error is raised Correct answer.

Explanation

ON CONFLICT DO NOTHING suppresses the uniqueness violation: the conflicting row is simply not inserted and the statement completes without error. The alternative form, DO UPDATE, modifies the existing row instead.

Question 39SQL Fundamentals and Data Types

Which clause makes an UPDATE statement return the rows it changed?

  1. RETURNING Correct answer.
  2. WITH RESULTS
  3. OUTPUT
  4. SELECT INTO

Explanation

RETURNING can be appended to INSERT, UPDATE, DELETE, and MERGE to emit a result set built from the affected rows. It can reference both the old and new column values for UPDATE.

Question 40SQL Fundamentals and Data Types

In PostgreSQL, what is the result of the expression NULL = NULL?

  1. NULL Correct answer.
  2. An error is raised
  3. FALSE
  4. TRUE

Explanation

Any comparison with a NULL operand yields the unknown result, represented as NULL, because NULL means a value is absent rather than equal or unequal. Testing for absence requires IS NULL or IS NOT DISTINCT FROM.

Question 41SQL Fundamentals and Data Types

In a row where col is NULL, what does the expression 'x' || col return?

  1. NULL Correct answer.
  2. An empty string
  3. 'x'
  4. 'x '

Explanation

The string concatenation operator returns NULL whenever any operand is NULL, so the unknown value propagates through the expression. The concat() function or COALESCE on the operand avoids this behavior.

Question 42SQL Fundamentals and Data Types

Which statement about the timestamp with time zone (timestamptz) type is correct?

  1. It stores only times that fall within the current session time zone
  2. It stores the literal text typed by the client, including its zone offset
  3. It behaves identically to timestamp without time zone in all cases
  4. It stores an instant; the session TimeZone affects display Correct answer.

Explanation

timestamptz converts the input to an absolute point on the timeline and stores that instant internally, which is effectively UTC. When values are displayed, they are rendered in the TimeZone setting of the session.

Question 43SQL Fundamentals and Data Types

In ORDER BY col with the default ascending direction, where do NULL values appear?

  1. They are excluded from the output
  2. First, because NULL is the smallest value
  3. Their position is random and must be fixed with GROUP BY
  4. Last; NULLs sort as larger than any value Correct answer.

Explanation

PostgreSQL treats NULL as larger than any non-null value for sorting, so ascending order places NULLs at the end and descending order places them first. The NULLS FIRST and NULLS LAST modifiers override this default.

Question 44SQL Fundamentals and Data Types

Which column definition creates a SQL-standard auto-incrementing column backed by an implicit sequence owned by the table?

  1. id numeric GENERATED BY DEFAULT AS IDENTITY
  2. id int GENERATED BY DEFAULT AS IDENTITY Correct answer.
  3. id uuid DEFAULT gen_random_uuid() NOT NULL
  4. id integer DEFAULT 0

Explanation

Identity columns require an integer type and create a sequence that is internally owned by the column, generating values when none is supplied. GENERATED BY DEFAULT still accepts an explicitly provided value, whereas GENERATED ALWAYS rejects it unless OVERRIDING SYSTEM VALUE is used.

Question 45SQL Fundamentals and Data Types

A child table declares FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE. What happens when a referenced parent row is deleted?

  1. Child rows are set to NULL
  2. The delete fails while child rows exist
  3. Matching child rows are deleted automatically Correct answer.
  4. The foreign key is dropped and child rows remain

Explanation

ON DELETE CASCADE propagates the delete to every child row that references the removed parent key, so referential integrity is maintained. Without the clause, the default NO ACTION behavior blocks the delete while references remain.

Question 46SQL Fundamentals and Data Types

For SELECT DISTINCT ON (dept) dept, name FROM employee ORDER BY dept, salary DESC, what is returned?

  1. All employees, with duplicate depts removed arbitrarily
  2. An error, because DISTINCT ON requires grouping
  3. One row per dept: its highest-paid employee Correct answer.
  4. One row per dept with the lowest salary

Explanation

DISTINCT ON keeps the first row of each set of rows sharing the given expressions, where first is determined by the ORDER BY. Because the ordering sorts each department by descending salary, the top-paid employee per department survives.

Question 47SQL Fundamentals and Data Types

Which statement correctly contrasts the json and jsonb types?

  1. json and jsonb differ only in the maximum size of a single document
  2. jsonb uses a binary form, drops duplicate keys, and supports GIN indexes Correct answer.
  3. json is stored internally in a decomposed binary form and can be indexed with GIN
  4. jsonb preserves the exact input text, whitespace, and key order

Explanation

jsonb parses the document into a binary representation on input, which removes duplicate keys (keeping the last), discards insignificant whitespace, and enables GIN indexing and containment operators. json keeps the original text verbatim and must be reparsed on each access.

Question 48SQL Fundamentals and Data Types

Which column definition creates a generated column that stores the product of price and qty on disk?

  1. total numeric GENERATED ALWAYS AS (price * qty)
  2. total numeric GENERATED AS (price * qty) STORED
  3. total numeric GENERATED ALWAYS AS (price * qty) STORED Correct answer.
  4. total numeric DEFAULT (price * qty) NOT NULL CHECK (total > 0)

Explanation

A stored generated column is declared with GENERATED ALWAYS AS (expression) STORED, which recomputes the expression on insert and update and writes the result to the table. It cannot be written to directly and cannot reference other generated columns.

Question 49SQL Fundamentals and Data Types

Which query returns both the count of rows with status = 'ok' and the total row count in a single scan of the table?

  1. SELECT count(*) WHERE status = 'ok' AS ok, count(*) AS total FROM t
  2. SELECT count(*) FILTER (WHERE status = 'ok'), count(*) FROM t Correct answer.
  3. SELECT count(*) AS ok, count(*) AS total FROM t GROUP BY status = 'ok'
  4. SELECT count(status = 'ok') AS ok, count(*) AS total FROM t

Explanation

An aggregate FILTER clause restricts the rows fed to that one aggregate, so count(*) FILTER (WHERE ...) counts only matching rows while a plain count(*) counts all rows in the same pass. This avoids a second scan or a self-join.

Question 50SQL Fundamentals and Data Types

The statement CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0); is executed. What is the effect?

  1. It defines a reusable named type with a validation rule Correct answer.
  2. It adds a check constraint to the existing column positive_int
  3. It creates a table containing a single integer column
  4. It creates a function that validates integer input

Explanation

A domain is a named data type built on a base type with optional constraints and a default, and every column declared with that domain inherits the check. This lets one rule be reused across many tables without repeating the constraint.