PostgresSQL Interview Questions & Answers

0
1233

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);

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?

PostgreSQL doesn't.

Submitted By:-Sam Dahiya           Email-ID: – samdahiya541@gmail.com

SHARE

LEAVE A REPLY