PostgreSQL data types, JSONB, indexes, MVCC, vacuum, extensions, psql commands, roles and replication basics.
39 cards · basic cards · AI-written, checked twice. Edit anything.
- What is the SERIAL data type in PostgreSQL?
Show answer
An auto-incrementing integer, starting from 1 by default with each insert.- When should you use BIGSERIAL instead of SERIAL?
Show answer
When you need auto-incrementing integers larger than SERIAL can handle, using 8 bytes instead of 4.- What is the difference between TEXT and VARCHAR in PostgreSQL?
Show answer
TEXT has no length limit; VARCHAR(n) enforces a maximum length of n characters.- What is NUMERIC data type used for?
Show answer
Fixed-point decimal numbers with arbitrary precision, ideal for financial values.- When would you choose FLOAT over NUMERIC?
Show answer
For approximate values where speed matters more than exactness, like scientific calculations.- What boolean literal values does PostgreSQL accept?
Show answer
TRUE/FALSE, YES/NO, ON/OFF, 1/0 (case-insensitive).- What does the DATE data type store?
Show answer
Only the year, month, and day without time information, occupying 4 bytes.- What is the difference between TIMESTAMP and TIMESTAMP WITH TIME ZONE?
Show answer
TIMESTAMP stores local date and time; TIMESTAMP WITH TIME ZONE includes timezone offset.- How do you declare an array of integers in PostgreSQL?
Show answer
Use INTEGER[] or INTEGER ARRAY syntax.- What is JSONB in PostgreSQL?
Show answer
JSON data type stored in a decomposed binary format, allowing indexing and efficient querying.- What is the main difference between JSON and JSONB?
Show answer
JSON stores text and preserves whitespace; JSONB stores binary format and allows indexing.- What does the @> operator do with JSONB?
Show answer
Tests whether the left JSONB contains the right JSONB as a subset.- How do you create an index on a JSONB column?
Show answer
Use GIN index: CREATE INDEX idx ON table USING GIN (jsonb_column);- What does the jsonb_agg function do?
Show answer
Aggregates multiple values into a JSONB array.- Why are indexes important in PostgreSQL?
Show answer
Indexes speed up data retrieval by allowing the database to find rows without scanning the entire table.
FlashKeepers