Over a million developers have joined DZone.

Overview of Open Source PostgreSQL Database

DZone's Guide to

Overview of Open Source PostgreSQL Database

Using SQL Shell (psql), we'll work with PostgreSQL and its open source database for queries in this object-relational database.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.


It's an object-relational database that is often named "Postgres", which means "PostgreSQL". It's a powerful open-source database introduced in 1996. The Head of the Elephant is the official logo of this database. It's a cross platform database usually used on UNIX based OS and Windows.


You can download it here for various platforms

Note: It will ask for a password during installation. The same password will be needed in the SQL Shell.

SQL Shell (psql)

After installation of Postgres, open the SQL Shell (psql) where you need to provide the password which you typed during the installation.

Psql is a command-line tool for Postgres as shown in the preceding screenshot.

Help Command

Foe learning some basic commands of psql such as:

  • copyright
  • help with SQL commands
  • help with psql commands
  • terminate with semicolon to execute query
  • quit command

Just type help as you can see in the following image.

\copyright: for distribution (legal) terms between the user and the company.

\h: for help with SQL commands, type \h and press  enter to learn more of this type.

\?: for help with psql commands.

\g: this is for indication of termination with a semicolon to execute query. In other words , if you want to execute a query, then you need to add a semicolon (;) or \g at the end of query.

Note: As you can see in the preceding image, a query is executed with the use of a \g and semicolon (;) at the end. If this is not used, then the query won't execute.

\q: to quit the command-line tool.

Create Database
To create a database in Postgres, just type the following SQL command.

Create database databasename;

List of databases

Once a database gets created, you can check it in the list of databases using \l. In other words, you can use backslash l command.

Note: There are 4 databases asshown in the preceding screenshot. Under this, 3 are the system databases and 1 is the user database named "test" that was created earlier.

Select the Database

To select the database just type the following command.

\c databasename;

In the same way we need to change the default database, which is "postgres".

\c postgres

Create Table
To create a table you need to first select the database in which the table will be created. Here, the database name is "test".

Now, we will create a table in the "test" database with the name "mytable" as shown in the following screenshot. Table is created in the same way as it gets created in any Relational Database Management System (RDBMS) database such as Microsoft SQL Server.

See all Tables

If you want to see all the tables in the "test" database, use /d in the selected database. Here, our selected database is "test".

Schema of a Table
If you want to see the schema of a table such as "mytable", then use \d mytable.

CRUD Operations in Postgre

All CRUD operations are the same as in any RDBMS (Relational Database Management System) database such as Microsoft SQL Server.

Insert Data

Now, insert data in the table "mytable".

Select Data

Select data from the table "mytable".

Update Data

Update data from the table "mytable".

Delete Data
Delete data from the table "mytable".

Drop Table

Drop "mytable" table from the "test" database.

Drop Database

To drop the "test" database, we need to switch the database, otherwise it will show an error as shown in the following screenshot.
Error: cannot drop the currently open database.

Now, just switch to the database named "postgres".

Now, drop the database.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

sql ,database ,tutorial ,postgresql

Published at DZone with permission of Rahul Bansal, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}