PostgreSQL Interview Questions

Mostly Its not possible because of it will comes on syntax error..

Syntax and datatypes

Max table size = 32 TB

NO LIMIT

It depends on physical space avaliable with you.

Replication is not that much powerful as compared to other databases.

character varaying(n), varchar(n), character(n), char(n), text.

POSTGRESQL Supports Char(n),varchar(n),text data types

The genetic query optimizer (GEQO) is an algorithm that does query planning using heuristic searching

GEQO stands for Genetic Query Optimization. As the name implies this is used for query optimization in POSTGRESQL. The Genetic Query Optimization is used along with POSTGRESQL query evaluator and opti...

SELECT nextval('sequence_name')

4 unsigned

OIDs in POSTGRESQL have 4 byte unique integers. That is each row in POSTGRESQL would be assigned a unique OID. Though the concept of unique OID exists in POSTGRESQL for each row after sufficient number of rows overflowing occurs and the OID value gets repeated from this stage.

select pg_cancel_backend(pid)

set work_mem=size

palloc is used to allocate memory in POSTGRESQL. POSTGRESQL memory allocation follows interesting concept of allocating memory within memory contexts. This concept helps in efficiency way of managing memory preventing many problems that pccurs due to improper memory allocation like memory leaks.

Because of the MVCC architecture of PostgreSQL but is greatly improved starting PostgreSQL 8.3.x if you are not updating the index field.

It will display list of DB's

It will display the databases that are currently available in the server.EXample:-bash-2.05b$ psql -l        List of databases   Name    |&n...

ILIKE or ~~*

Example for using ilike we can get it

SELECT city_name FROM cities WHERE city_name ilike '%chennai%';

this will print both CHENNAI and chennai

Regards,
Gobi S.

Explain and Explain analyze

In postgresql.conf :shared_buffers

PgAdmin

Yes, Can be given at the time of table creation or can be with alter table

Triggers for tables and rules for view

Because of MVCC architecture of PostgreSQL

OID are used for unique row identification in POSTGRESQL. For identifying specific physical rows with block and offset values in POSTGRESQL TIDs are used. The relationship between OID and TID exists in such a way that whenever a row is altered the corresponding TID also gets altered.

psqlODBC, OpenLink, Mac ODBC driver  are some of the generally and widely used ODBC drivers that are available for POSTGRESQL.