What is PostgreSQL?
PostgreSQL is a free, open source object-relational database. It has SQL, ACID transactions and MVCC, plus custom types, arrays,
jsonband functions in several languages. It ships under the permissive PostgreSQL License and is developed by a community, not one vendor."Object-relational" means you can extend it:
CREATE TYPEadds your own types, andCREATE EXTENSIONadds packages such as PostGIS or pgvector.What is the difference between PostgreSQL and MySQL?
Both are open source relational databases. PostgreSQL implements more of the SQL standard and has more built-in types. The differences that show up in code:
PostgreSQL MySQL DDL in a transaction yes, can be rolled back no, DDL commits implicitly Upsert INSERT ... ON CONFLICTINSERT ... ON DUPLICATE KEY UPDATERETURNINGyes no FULL OUTER JOINyes no JSON jsonand indexablejsonbJSONLicense PostgreSQL License GPL What is psql, and which psql commands should you know?
psqlis PostgreSQL's command-line client. You type SQL ending with;, plus backslash meta-commands that psql runs itself.psql -h localhost -U postgres -d shopCommand Does \llist databases \c shopconnect to shop\dtlist tables \d orderscolumns, indexes, constraints \dnlist schemas \dulist roles \qquit Meta-commands never reach the server, so a database driver in your application cannot run them. See the psql reference.
What is the difference between a database and a schema in PostgreSQL, and what is
search_path?A server holds databases, a database holds schemas, and a schema holds tables, views and functions. A connection uses one database and cannot query another directly (that takes
postgres_fdw), but one query can join tables across schemas.search_pathlists the schemas searched for an unqualified name; the default is"$user", public.CREATE SCHEMA sales; CREATE TABLE sales.orders (id int, amount numeric); SET search_path TO sales, public; SELECT * FROM orders; -- finds sales.ordersSince PostgreSQL 15, ordinary users cannot create objects in
publicunless grantedCREATEon it. More in schemas.What is the difference between
SERIALand an identity column in PostgreSQL?Both fill an integer column from a sequence.
SERIALis PostgreSQL shorthand that creates a sequence and setsDEFAULT nextval(...); an identity column (GENERATED ... AS IDENTITY, since PostgreSQL 10) is the SQL standard form and the better choice for new tables.PostgreSQL-- fails on purpose: GENERATED ALWAYS rejects a hand-written id CREATE TABLE users ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text ); INSERT INTO users (name) VALUES ('Asha'); INSERT INTO users (id, name) VALUES (5, 'Ben');The second insert fails with "cannot insert a non-DEFAULT value into column".
OVERRIDING SYSTEM VALUEforces it;GENERATED BY DEFAULTaccepts a given id the waySERIALdoes.ALWAYSstops code from writing ids the sequence will later collide with.Does
NULL = NULLreturn true in PostgreSQL, and what doesCOALESCEdo?PostgreSQLSELECT COALESCE(NULL, NULL, 'none'), NULL = NULL;It returns
none | NULL.COALESCEreturns its first argument that is not NULL.NULL = NULLis NULL, not true: NULL means "unknown", and comparing with an unknown value gives unknown.Test with
IS NULL, and compare two values that may be NULL withIS NOT DISTINCT FROM, which treats two NULLs as equal.WHERE col = NULLreturns no rows and raises no error, becauseWHEREkeeps only rows where the condition is true.Is
LIKEcase-sensitive in PostgreSQL, and what isILIKE?PostgreSQLSELECT 'PostgreSQL' LIKE 'post%', 'PostgreSQL' ILIKE 'post%';It returns
false | true.LIKEis case-sensitive in PostgreSQL, so'post%'does not matchPostgreSQL;ILIKEis the case-insensitive version, a PostgreSQL extension. In both,%matches any run of characters and_exactly one. For regular expressions use~, or~*to ignore case.The follow-up is speed. A B-tree index helps only a pattern anchored at the start (
'post%').ILIKEand a leading%scan the table unless you add a trigram index from thepg_trgmextension.How do
LIMIT,OFFSETandFETCH FIRSTwork in PostgreSQL?LIMIT nreturns at most n rows andOFFSET mskips m rows first.FETCH FIRST n ROWS ONLYis the standard spelling, and since PostgreSQL 13WITH TIESalso keeps rows tied with the last one.PostgreSQLCREATE TABLE scores (name text, points int); INSERT INTO scores VALUES ('Asha', 90), ('Ben', 85), ('Chen', 85), ('Dev', 70); SELECT name, points FROM scores ORDER BY points DESC FETCH FIRST 2 ROWS WITH TIES;It returns three rows: Asha, then Ben and Chen, tied on 85. Without
ORDER BYthe rows you get are not defined. A largeOFFSETis slow because skipped rows are still read.What is the difference between
DELETE,TRUNCATEandDROPin PostgreSQL?DELETEremoves chosen rows,TRUNCATEremoves all rows at once, andDROP TABLEremoves the table.DELETETRUNCATEDROP TABLERow triggers fire do not fire do not fire Big tables slow, leaves dead rows fast fast Can roll back yes yes yes The trap: all three are transactional in PostgreSQL, while MySQL and Oracle commit on
TRUNCATE.PostgreSQLCREATE TABLE logs (id int); INSERT INTO logs VALUES (1), (2), (3); BEGIN; TRUNCATE logs; ROLLBACK; SELECT count(*) FROM logs;It returns 3.
TRUNCATEtakes anACCESS EXCLUSIVElock, blocking all access to the table until commit.What is the difference between a primary key and a unique key in PostgreSQL?
A primary key is
UNIQUEplusNOT NULL, and a table has at most one. A table can have many unique constraints, and their columns allow NULL. PostgreSQL builds a unique B-tree index for each, namedusers_pkeyandusers_email_keyhere.PostgreSQLCREATE TABLE users (id int PRIMARY KEY, email text UNIQUE); INSERT INTO users VALUES (1, NULL), (2, NULL), (3, 'asha@example.com'); SELECT count(*) AS total_rows, count(email) AS emails FROM users;It returns
3 | 1: the two NULL emails are not duplicates, because NULL is not equal to NULL. A foreign key usually references the primary key, but any unique constraint works. More on keys in the DBMS questions.What happens when you concatenate a string with NULL in PostgreSQL?
PostgreSQLSELECT 'Hi ' || NULL, concat('Hi ', NULL, 'there');It returns
NULL | Hi there.||returns NULL when either side is NULL, so one missing middle name blanks the whole full name.concat()skips NULL arguments, andconcat_ws(', ', a, b, c)skips them and puts the separator between the rest.Oracle developers trip on this, because Oracle's
||treats NULL as an empty string. To keep||, wrap the column that may be missing:first_name || ' ' || COALESCE(last_name, '').
PostgreSQL Interview Questions for Freshers
What PostgreSQL is, psql, schemas, identity columns, NULL, pattern matching, pagination and the commands that remove data.