PostgreSQL Interview Questions

21.How do I find out what version of PostgreSQL I am running?

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

test=#

22.How to create a PostgreSQL user?

There are two methods in which you can create user.
Method 1: Creating the user in the PSQL prompt, with CREATE USER command.
# CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE
Method 2: Creating the user in the shell prompt, with createuser command.
$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

23.How to create a PostgreSQL Database?

There are two metods in which you can create two databases.
Method 1: Creating the database in the PSQL prompt, with createuser command.
# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE
Method 2: Creating the database in the shell prompt, with createdb command.
$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE
* -O owner name is the option in the command line.

24.How do I get a list of databases in a Postgresql database?

25.How to Delete/Drop an existing PostgreSQL database?

26.Getting help on postgreSQL commands

\? will show PSQL command prompt help. \h CREATE will shows help about all the commands that starts with CREATE, when you want something specific such as help for creating index, then you need to give CREATE INDEX.
# \?
# \h CREATE
# \h CREATE INDEX

27.How do I get a list of all the tables in a Postgresql database?

# \d
On an empty database, you’ll get “No relations found.” message for the above command.

28.How to turn on timing, and checking how much time a query takes to execute?

# \timing — After this if you execute a query it will show how much time it took for doing it.
# \timing
Timing is on.
# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms

29.How To Backup and Restore PostgreSQL Database and Table?

We discussed earlier how to backup and restore postgres database and tables using pg_dump and psql utility.

30.How to see the list of available functions in PostgreSQL?

To get to know more about the functions, say \df+
# \df
# \df+

31.How to edit PostgreSQL queries in your favorite editor?

# \e
\e will open the editor, where you can edit the queries and save it. By doing so the query will get executed.

32.Where can I find the PostgreSQL history file?

Similar to the Linux ~/.bash_history file, postgreSQL stores all the sql command that was executed in a history filed called ~/.psql_history as shown below.

$ cat ~/.psql_history
alter user postgres with password 'tmppassword';
\h alter user
select version();
create user ramesh with password 'tmppassword';
\timing
select * from pg_catalog.pg_attribute;

33.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.

34.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.

35.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

36.How do I tell what PostgreSQL version I am running?

Run this query: SELECT version();

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

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

39.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.

40.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.

« Previous | 0 | 1 | 2 | 3 | 4 | Next »

The largest Interview Solution Library on the web