PostgreSQL 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:
- How to Install PostgreSQL
- Initialize the DB using initdb
- Create a new Custom Postgres Database
- Create a New Table
- View All Objects (including Tables)
- View Table Details (Describe PostgreSQL Table)
- Insert Records into Table
- 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.
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about