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?
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);
SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col);
Q: – How do I perform queries using multiple databases?
There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave.
contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.
Q: – Is possible to create a shared-storage postgresql server cluster?
PostgreSQL does not support clustering using shared storage on a SAN, SCSI backplane, iSCSI volume, or other shared media. Such "RAC-style" clustering isn't supported. Only replication-based clustering is currently supported.
Q: – Does PostgreSQL have stored procedures?
Submitted By:-Sam Dahiya Email-ID: – email@example.com