PostgresSQL Interview Questions & Answers

Q: – What Is A Sequence?

A sequence is a special kind of database object designed for generating unique numeric identifiers. It is typically used to generate artificial primary keys.
Sequences are similar, but not identical, to the AUTO_INCREMENT concept in MySQL.

Q: – What is a CTID?

CTIDs identify specific physical rows by their block and offset positions within a table. They are used by index entries to point to physical rows. A logical row's CTID changes when it is updated, so the CTID cannot be used as a long-term row identifier. But it is sometimes useful to identify a row within a transaction when no competing update is expected.

Q: – Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?

You probably have run out of virtual memory on your system, or your kernel has a low limit for certain resources. Try this before starting the server:

ulimit -d 262144
limit datasize 256m

Q: – How do I tell what PostgreSQL version I am running?

Run this query: SELECT version();

Q: – How do I create a column that will default to the current time?

Use CURRENT_TIMESTAMP:
CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Q: – How do I perform an outer join?

PostgreSQL supports outer joins using the SQL standard syntax. Here are two examples:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
or
SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col);