What is the difference between
numericanddouble precisionin PostgreSQL?PostgreSQLSELECT 0.1::float8 + 0.2::float8, 0.1::numeric + 0.2::numeric;It returns
0.30000000000000004 | 0.3.realanddouble precision(float8) are binary floating point: fast, but 0.1 has no exact binary form, so sums drift.numeric(same asdecimal) stores exact decimal digits: slower, and the right type for money.numeric(10,2)allows 10 digits, 2 after the point.A related trap:
7 / 2is integer division and returns 3; write7 / 2.0or cast one side tonumeric.What is the difference between
text,varchar(n)andchar(n)in PostgreSQL?They run at the same speed; only the rules differ.
texthas no limit,varchar(n)rejects a longer value, andchar(n)pads with spaces, then ignores them in comparisons and when it becomes text.PostgreSQLCREATE TABLE codes (fixed char(5), flexible varchar(5)); INSERT INTO codes VALUES ('ab', 'ab'); SELECT fixed || '|', flexible || '|', octet_length(fixed) FROM codes;It returns
ab| | ab| | 5: the padding is stored (5 bytes) but dropped whenfixedis concatenated. Usetext, orvarchar(n)when the limit is a real rule. Trap: an explicit cast cuts instead of failing, so'PostgreSQL'::varchar(5)isPostg.What is the difference between
timestampandtimestamptzin PostgreSQL?timestamptz(timestamp with time zone) stores an absolute instant, kept in UTC and shown in the session'sTimeZone.timestamp(without time zone) stores a wall clock reading with no zone at all. Neither one stores the zone name, and both take 8 bytes.PostgreSQLSET TIME ZONE 'UTC'; CREATE TABLE events (local_at timestamp, instant_at timestamptz); INSERT INTO events VALUES ('2026-11-17 10:00', '2026-11-17 10:00'); SET TIME ZONE 'Asia/Kolkata'; SELECT local_at::text, instant_at::text FROM events;It returns
2026-11-17 10:00:00 | 2026-11-17 15:30:00+05:30: the instant is shown in India time, while the wall clock reading does not move. Usetimestamptzfor the moment something happened.instant_at AT TIME ZONE 'America/New_York'turns it into atimestampin that zone. See date and time types.What do you get when you subtract one date from another in PostgreSQL?
PostgreSQLSELECT DATE '2024-03-01' - DATE '2024-02-01';It returns
29:date - dateis an integer number of days, and 2024 is a leap year.Other date arithmetic:
date + 7returns a date;date + interval '1 month'returns atimestamp, and from January 31 lands on the last day of February;timestamp - timestampreturns aninterval. Cut to a period withdate_trunc('month', ts).now()is the start of the current transaction, so it does not change inside one;clock_timestamp()does.What is the difference between
::andCASTin PostgreSQL, and does a cast to integer round or truncate?PostgreSQLSELECT 3.7::int, CAST(2.5 AS integer), (-2.5)::int;It returns
4 | 3 | -3.x::typeis PostgreSQL shorthand andCAST(x AS type)is the SQL standard; they do the same thing. A cast fromnumericto an integer type rounds, with halves going away from zero, instead of truncating as C or Java do; usetrunc()orfloor()to cut. Adouble precisionvalue rounds halves to even instead, so2.5::float8::intis 2.A string that is not a number, such as
'abc'::int, raises an error rather than returning NULL. Since PostgreSQL 16,pg_input_is_valid('abc', 'integer')tests it first.Are PostgreSQL arrays 0-based or 1-based?
PostgreSQLSELECT (ARRAY[10, 20, 30])[1], (ARRAY[10, 20, 30])[0];It returns
10 | NULL. Arrays start at 1, and an index outside the array returns NULL, not an error. Find rows containing a value with= ANY(col), or test containment with@>, which a GIN index serves.PostgreSQLCREATE TABLE posts (id int, tags text[]); INSERT INTO posts VALUES (1, '{sql,postgres}'), (2, ARRAY['python']), (3, '{postgres,python}'); SELECT id FROM posts WHERE 'postgres' = ANY(tags) ORDER BY id;It returns posts 1 and 3. Drivers pass a list the same way:
WHERE id = ANY($1). Data you join on often belongs in its own table, not an array.What is the difference between JSON and JSONB in PostgreSQL?
jsonstores the input text exactly and reparses it on every read.jsonbstores a parsed binary form: faster to query, indexable with GIN, with containment (@>), but it drops whitespace, reorders keys and keeps only the last duplicate key.PostgreSQLSELECT '{"b": 1, "a": 2, "a": 3}'::json::text AS as_json, '{"b": 1, "a": 2, "a": 3}'::jsonb::text AS as_jsonb;It returns the input unchanged for
jsonand{"a": 3, "b": 1}forjsonb. Usejsonbby default, andjsononly when you must keep the exact text, such as a signed webhook body. See JSON types.What do the JSONB operators
->,->>,@>and?do in PostgreSQL?->returns a value asjsonb,->>returns it astext,@>tests whether the left value contains the right one, and?tests whether a top-level key exists.PostgreSQLCREATE TABLE users (id int, profile jsonb); INSERT INTO users VALUES (1, '{"plan": "pro", "city": "Pune", "age": 30}'), (2, '{"plan": "free", "age": 9}'); SELECT id, (profile -> 'plan')::text AS arrow, profile ->> 'plan' AS double_arrow, profile @> '{"plan": "pro"}' AS is_pro, profile ? 'city' AS has_city, profile ->> 'age' > '10' AS text_compare FROM users ORDER BY id;->keeps the JSON quotes ("pro"),->>gives plainpro. The last column is the trap: it is true for both users, because->>returns text and'9' > '10'compares characters. Cast first:(profile ->> 'age')::int > 10. A GIN index,USING gin (profile jsonb_path_ops), serves@>lookups. More in JSON functions.How do you generate a UUID in PostgreSQL?
Call
gen_random_uuid(), built in since PostgreSQL 13, which returns a random (version 4)uuid. Before 13 it came from thepgcryptoextension;uuid_generate_v4()comes from theuuid-osspextension.PostgreSQLCREATE TABLE orders ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), total numeric ); INSERT INTO orders (total) VALUES (10), (20); SELECT count(DISTINCT id) AS ids, max(pg_column_size(id)) AS bytes FROM orders;It returns
2 | 16: store a UUID asuuid, 16 bytes, not as 36 characters oftext. The follow-up: random UUIDs scatter inserts across the primary key index, which hurts caching on large tables. Time-ordered UUIDs avoid that, and PostgreSQL 18 addsuuidv7()for them.How does the boolean type work in PostgreSQL?
booleanholdstrue,falseor NULL in one byte. As input it accepts strings such as'true','yes','on','1'and't'(and their opposites); psql displays the values astandf. The trap is NULL:PostgreSQLCREATE TABLE tasks (title text, done boolean); INSERT INTO tasks VALUES ('Write', true), ('Test', false), ('Ship', NULL); SELECT count(*) FILTER (WHERE NOT done) AS not_done, count(*) FILTER (WHERE done IS NOT TRUE) AS not_true FROM tasks;It returns
1 | 2:NOT NULLis still NULL, soWHERE NOT doneskips Ship, whileIS NOT TRUEcounts it. DeclareNOT NULL DEFAULT falsewhen a flag has two states. Unlike MySQL, whereBOOLEANistinyint(1),WHERE done = 1is an error in PostgreSQL: there is noboolean = integeroperator.What is an enum type in PostgreSQL, and when should you avoid it?
An enum is a type with a fixed, ordered list of labels, made with
CREATE TYPE ... AS ENUM. Values are checked on input, take 4 bytes, and sort in the order you declared, not alphabetically.PostgreSQLCREATE TYPE priority AS ENUM ('low', 'medium', 'high'); CREATE TABLE tickets (title text, level priority); INSERT INTO tickets VALUES ('Typo', 'low'), ('Outage', 'high'), ('Slow page', 'medium'); SELECT title, level FROM tickets ORDER BY level DESC;It returns Outage, Slow page, Typo.
ALTER TYPE priority ADD VALUE 'urgent'adds a label, but none can be dropped: you build a new type and migrate the column. When the list changes often, a lookup table with a foreign key, ortextwith aCHECK, is easier.
PostgreSQL Data Types and JSONB Interview Questions
Numbers, text, dates and time zones, casts, arrays, JSON and JSONB, UUIDs, booleans and enums, with the trap in each.