PostgreSQL - Interview Questions and Answers

There are new, different data-types supported by Postgresql. Following are those data-types:

• UUID
• Numeric types
• Boolean
• Character types
• Temporal types
• Geometric primitives
• Arbitrary precision numeric
• XML
• Arrays etc.

Tokens in PostgreSQL are the building blocks of any source code. They are known to comprise many of the special character symbols. These can be regarded as constants, quoted identifiers, other identifiers, and keywords. Tokens which are keywords consist of pre-defined SQL commands and meanings. Identifiers are used to represent variable names like columns, tables, etc.

• /usr/local/etc/rc.d/010.pgsql.sh start
• /usr/local/etc/rc.d/postgresql start

• /usr/local/etc/rc.d/010.pgsql.sh stop
• /usr/local/etc/rc.d/postgresql stop

The postgresql has maximum size of table is 32TB.

It supports a language of its own known as PL/pgSQL and it supports internal procedural languages. Pl/pgSQL can be compared to oracle, PL/SQL, etc. Languages such as Perl, Python, TCL can be used as embedded languages.

1. There are various data administration tools they are :- 

   1) Psql
   2) Pgadmin
   3) Phppgadmin

   Most of these tools are front end administration tools and web based interfaces. Out of these phppgadmin is the most popular one.

    

Pgadmin forms a graphical front end administration tool. This feature is available under free software released under Artistic License. Pgadmin iii is the new database administration tool released under artistic license.

Postgres’ write ahead logging increases the reliability (in a way resiliency) of the db by logging changes before any changes or updates to the database. This provides log of database in case of a db crash and this also helps to start the work from the point it was discontinued. This it is useful for crash recovery.

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 ram;
CREATE DATABASE

Method 2: Creating the database in the shell prompt, with createdb command.

$ /usr/local/pgsql/bin/createdb mydb -O ram

CREATE DATABASE
* -O owner name is the option in the command line.

The PostgreSQL operators include - Arithmetic operators, Comparison operators, Logical operators, and Bitwise operators.

Yes, Like other open source databases, PostgreSQL is easy to run in virtual containers and is highly portable. Several companies have support for PostgreSQL in cloud hosting environments, including Heroku, GoGrid and Joyent.

The partitioned table is a logical structure. It is used to split a large table into smaller pieces, which are called partitions.

The commands used to control transactions in PostgreSQL are BEGIN TRANSACTION, COMMIT, and ROLLBACK.

PostgreSQL is an advanced version of SQL.  Some of the differences between these two include the following:

Unlike SQL, views in PostgreSQL are not updatable.

Another difference is whereas SQL provides computed columns; the same cannot be expected from PostgreSQL.

Unlike SQL, in PostgreSQL, you don’t need to create a DLL to see the code what it is doing.

PostgreSQL supports dynamic actions whereas SQL doesn’t support them.

The binary data in PostgreSQL either by using the bytes or by using the large object feature.

 It is a location in the disk. In this, PostgreSQL stores the data files, which contain indices and tables, etc.

MVCC or better known as Multi-version concurrency control is used to avoid unwanted locking of the database. The time lag for the user is removed so that one can easily log into his database. All the transactions are well- kept as a record. The time lag occurs when someone else is on the content.

MySQL is the “easy-to-use, web developer” database, and PostgreSQL is the “feature-rich, standards-compliant” database. PostgreSQL is liberally licensed and owned by its community; MySQL is GPL-licensed and owned by Oracle. Beyond that, each database user should make his own evaluation; open source software makes doing comparisons very easy.

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