≡ Menu

8 PostgreSQL Examples to Install, Create DB & Table, Insert & Select Records

PostgreSQL LogoPostgreSQL is an enterprise level open source database.

This is one of the most reliable, robust, feature-rich, and powerful relational database system.

Most importantly, PostgreSQL is well known for its ability to handle heavy load, and deliver high performance. This supports pretty much all the features that you would ever need from a RDBMS system.

If you are new to PostgreSQL, this tutorial will give you an excellent jump-start.

The following are covered in this tutorial:

  1. How to Install PostgreSQL
  2. Initialize the DB using initdb
  3. Create a new Custom Postgres Database
  4. Create a New Table
  5. View All Objects (including Tables)
  6. View Table Details (Describe PostgreSQL Table)
  7. Insert Records into Table
  8. View the table Records (Select from Table)

1. Install and PostgreSQL

There are two ways you can install PostgreSQL database:

  • Install from yum Repository
  • Install from Source Code

To install it from yum, use the following command:

yum install postgresql-server

To install it from source code, follow these steps: 9 Steps to Install and Configure PostgreSQL from Source on Linux

2. Initialize the DB using initdb

Next, you should initialize the PostgreSQL database using initdb, and start the postgresql server.

If you are on CentOS 6 (or RedHat), use the following:

service postgresql initdb
service postgresql start

If you are on CentOS 7 (or RHEL 7), Fedora 23+ do the following:

postgresql-setup initdb
systemctl start postgresql.service

When you initialize the database using itdb command, this will create a new PostgreSQL db cluster. A cluster in this context is nothing but a bunch of postgreSQL databases managed on a single node by a single postgreSQL server.

The initdb will do the following:

  • Create appropriate directories required for the database files
  • Create catalog tables which are kind of system tables that will be shared with all the databases on the cluster
  • This will also create a template1 database, which will act as a baseline for all the new databases that will be created on this particular postgres cluster

3. Create a new Custom Postgres Database

To create a new postgres database, use createdb command as shown below.

The following will create a new custom PostgreSQL database called “thegeekstuff”.

# createdb thegeekstuff

If createdb command is not on your system, then something went wrong during your installation. Or, you might not be have this in your PATH environment.

When you install using yum, this command will be under /usr/pgsql-X.Y/bin/createdb (X.Y is the version number), with appropriate link for this in /usr/bin. So, you will not have the problem here.

But, if you’ve installed from source, then you might want to make sure /usr/local/pgsql/bin/ directory is in your PATH variable.

Also, make sure you are creating the database from a user who has privileges to create the database. If you are new to the user/role concepts in PostgreSQL, for now, go-ahead and login as root (assuming you’ve installed postgreSQL as root), and execute the createdb command from there.

In the following example, I created the database from Linux username called “jason”, who doesn’t have the appropriate privilege to create a new database.

$ createdb thegeekstuff
createdb: could not connect to database template1: FATAL:  role "jason" does not exist

Finally, when you are creating a new database, and if your postgreSQL server is not running, then you’ll get the following “could not connect to database template1” error message.

# createdb thegeekstuff
createdb: could not connect to database template1: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Note: If you’ve created a database by mistake, you can delete the database using the “dropdb” command as shown below.

# dropdb thegeekstuff

4. Create a New Table

Once you’ve created a new database, then it is time to create a new table under this database.

First, you should connect to the newly created “thegeekstuff” database using the psql command as shown below.

$ psql thegeekstuff
psql (9.6.1)
Type "help" for help.

thegeekstuff=# 

Note: The above command will take you to the postgreSQL prompt “=#”. In front of this prompt, you’ll see the database name. From this prompt, you can execute all your PostgreSQL DB commands.

For this example, let us create employee table with the following 4 fields:

CREATE TABLE employee (
   id       SERIAL,       --emp id; auto-increment when empty
   name     varchar(20),  --emp name
   dept     varchar(10),  --department
   salary   int           --employee's salary
);

When you execute the above command at the postgresql prompt, you’ll see the following out. i.e If the table is created successfully, it will display “CREATE TABLE” as the message as shown below.

thegeekstuff=# CREATE TABLE employee (
thegeekstuff(#    id       SERIAL,       --emp id; auto-increment when empty
thegeekstuff(#    name     varchar(20),  --emp name
thegeekstuff(#    dept     varchar(10),  --department
thegeekstuff(#    salary   int           --employee's salary
thegeekstuff(# );
CREATE TABLE
thegeekstuff=# 

Few things to keep in mind:

  • The double dash (- followed by another -) means comment entry. Anything that follows a double-dash is comment, as you see from the above command.
  • Varchar is for string fields, specify the maximum length of the string field inside parenthesis. So, varchar(20) for name field specifies that the employee name can be maximum of 20 characters.
  • int field is for numbers
  • SERIAL datatype is specified for id. We could’ve specified int also here. But, what SERIAL does is that, when you don’t specify an employee id during your insert command, it will create one automatically and increment it accordingly from auto-created sequence.

The following are some other database supported by PostgreSQL:

  • char(X)
  • int
  • smallint
  • real
  • double precision
  • date
  • time
  • timestamp
  • interval
  • etc.

5. View All Objects (including Tables)

After we executed the create table command, we should verify to make sure that the table is created successfully.

For this use the \d option as shown below.

“d” in this context is display. This will list all the tables, views and sequences available.

As you see from the following output, we see the newly created employee table.

thegeekstuff-# \d
               List of relations
 Schema |      Name       |   Type   |  Owner   
--------+-----------------+----------+----------
 public | employee        | table    | postgres
 public | employee_id_seq | sequence | postgres
(2 rows)

Apart from the employee table, we also see a sequence which we didn’t create. This is because we specified SERIAL as datatype for the id field, postgres has automatically created this employee_id_seq, which it will in-turn use it when you don’t specify a value during your insert for the employee id field.

Also, use the “\d+” command, will display couple of extra columns about the objects; size and description as shown below.

thegeekstuff-# \d+
                             List of relations
 Schema |      Name       |   Type   |  Owner   |    Size    | Description 
--------+-----------------+----------+----------+------------+-------------
 public | employee        | table    | postgres | 0 bytes    | 
 public | employee_id_seq | sequence | postgres | 8192 bytes | 
(2 rows)

The size of our employee table is “0 bytes” as we have not yet inserted new records.

6. View Table Details (Describe PostgreSQL Table)

If you’ve used other database like Oracle, or MySQL, etc, you are familiar with “desc” command, which will display all the details about the tables including the columns and its datatypes.

In PostgreSQL, the equivalent of “desc” command is “\d” followed by the table name.

“d” in this context is “describe”.

As you see below, this describes the details of the employee table.

thegeekstuff-# \d employee
                                Table "public.employee"
 Column |         Type          |                       Modifiers                       
--------+-----------------------+-------------------------------------------------------
 id     | integer               | not null default nextval('employee_id_seq'::regclass)
 name   | character varying(20) | 
 dept   | character varying(10) | 
 salary | integer               | 

When you use “\d+” as shown below, this will display additional information about the table columns; stroage, stats target and description.

thegeekstuff-# \d+ employee
                                                    Table "public.employee"
 Column |         Type          |                       Modifiers                       | Storage  | Stats target | Description 
--------+-----------------------+-------------------------------------------------------+----------+--------------+-------------
 id     | integer               | not null default nextval('employee_id_seq'::regclass) | plain    |              | 
 name   | character varying(20) |                                                       | extended |              | 
 dept   | character varying(10) |                                                       | extended |              | 
 salary | integer               |                                                       | plain    |              | 

7. Insert Records into Table

Next step is to insert some employee records into employee table.

The syntax is exactly same as any standard SQL insert syntax.

The following will insert 5 records to the employee table with the given values for all the fields.

INSERT INTO employee VALUES (100,'Thomas','Sales',5000);
INSERT INTO employee VALUES (200,'Jason','Technology',5500);
INSERT INTO employee VALUES (300,'Mayla','Technology',7000);
INSERT INTO employee VALUES (400,'Nisha','Marketing',9500);
INSERT INTO employee VALUES (500,'Randy','Technology',6000);

The order of the values should match the order of the column names that you see in the output of the “\d employee” command. i.e In the same order as how you created the table.

Note: In the above insert, even-though id can be auto-generated, we specified the value of employee id (for the id column).

In the following example, we can insert values only to specific columns of employee table by specifying the column name within parenthesis as shown below.

The following example will insert a new employee record only with values for name and dept columns.

thegeekstuff=# INSERT INTO employee (name,dept) VALUES ('Ritu', 'Accounting');
INSERT 0 1

If the insert command is successful, it will display the message “INSERT 0 1” as shown above.

8. View the table Records (Select from Table)

Just like any typical SELECT sql command, you van view only the specific columns of a table by specifying the column names as shown below.

thegeekstuff=# SELECT name, dept FROM employee;
  name  |    dept    
--------+------------
 Thomas | Sales
 Jason  | Technology
 Mayla  | Technology
 Nisha  | Marketing
 Randy  | Technology
 Ritu   | Accounting
(6 rows)

SELECT * will display all the columns from the specified table as shown below.

thegeekstuff=# SELECT * FROM employee;
 id  |  name  |    dept    | salary 
-----+--------+------------+--------
 100 | Thomas | Sales      |   5000
 200 | Jason  | Technology |   5500
 300 | Mayla  | Technology |   7000
 400 | Nisha  | Marketing  |   9500
 500 | Randy  | Technology |   6000
   1 | Ritu   | Accounting |       
(6 rows)

In the above output, the value of the id column for the 6th record is 1.

This is because we never specified the id value, and it automatically took the value of 1 from its own sequence. The next time you create a record with an id value, it will use 2 as emp id.

Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book