Intro to PostgreSQL

Table of Contents

Introduction

PostgreSQL is an open source, ACID-compliant, object-oriented relational database. PostgreSQL has been under continuous development for 30 years, and is known for its reliability, performance, and comprehensive feature set. Learn more about PostgreSQL, including an overview of the software, how to install PostgreSQL, and troubleshooting tips.

PostgreSQL vs MySQL/MariaDB

PostgreSQL and MySQL/MariaDB are similar in some ways. Both database systems are:

  • Open Source
  • Transactional (MySQL/MariaDB: Only when using the InnoDB storage engine)
  • ACID-compliant
  • Object-oriented
  • Relational database systems
  • Based on Structured Query Language (SQL)

Both products offer stability, performance, and flexibility, and are excellent choices for a database system. This can make it difficult to choose between them. In fact, for many developers, the choice comes down to a matter of personal preference.

MySQL/MariaDB is known for being easy to use, a quality which has helped it become the world's most popular open-source database. MySQL/MariaDB also has an edge when it comes to Web development, as the response times and performance of a MySQL/MariaDB database tend to be faster than PostgreSQL.

By comparison, PostgreSQL has a reputation for having a more robust and advanced feature set. In fact, fans sometimes refer to PostgreSQL as "an open-source Oracle." PostgreSQL also tends to handle transactions and foreign key references more gracefully than MySQL/MariaDB.

PostgreSQL is a better choice for projects which:

  • Involve a more complex database design.
  • Include complex rulesets.
  • Plan for heavy use of transactions.
  • Will incorporate many foreign key references.

Install PostgreSQL

There are two options for installing PostgreSQL:

  • On a new server: PostgreSQL is available as a ready-to-use application which can be automatically installed on the server when it is built.
  • On an existing server: PostgreSQL can be manually installed and configured on an existing server.

For step-by-step instructions for installing PostgreSQL, see our articles:

Log In to the PostgreSQL Client

First, su to the postgres user:

sudo su - postgres

Then enter the PostgreSQL client psql:

psql

Note: If you prefer not to use the PostgreSQL client, you can also use modified PostgreSQL commands from the command prompt. This tutorial will cover the PostgreSQL client commands. More information on the command prompt variations can be found in the official PostgreSQL documentation.

Create, Select, and Drop a Database

Create a Database

Use CREATE DATABASE [database name] to create a database. For example, to create a database named testdb the command is:

CREATE DATABASE testdb;

List and Select a Database

Use \l to list all available databases:

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

Use \c to connect to a database and select it for use:

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".

Drop a Database

Use DROP DATABASE [database name] to delete a database. For example, to delete the testdb database, the command is:

DROP DATABASE testdb;

To exit the client, type:

\q

Then hit Enter.

Create and Drop a Table

Create a Table

Use CREATE TABLE [table name] (column definitions); to create a table. A full list of CREATE TABLE parameters can be found in the CREATE TABLE chapter of the official PostgreSQL documentation.

For example, to create a table testtable with two basic columns, the command is:

CREATE TABLE testtable (
  id char(5) PRIMARY KEY,
  name varchar(40)
  ); 

Use \d to verify that your table was created:

testdb=# \d
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | testtable | table | postgres
(1 row)

Drop a Table

Use DROP TABLE [table name]; to delete a table. For example, to delete the testtable table, the command is:

DROP TABLE testtable;

Use \d to verify that your table was deleted:

testdb=# \d
No relations found.

Working With Records: Insert, Select, and Update Data

Insert Data into a Table

Use INSERT INTO [table name] VALUES (data, data...); to insert data into a table. A full list of INSERT parameters can be found in the "Inserting Data" chapter of the official PostgreSQL documentation.

For example, to insert a record into the table testtable the command is:

INSERT INTO testtable VALUES (1, 'Alice');
INSERT INTO testtable VALUES (2, 'Bob');

Note: It is important to list the values in the same order as the columns of the table. In our example, the table's first column is id and the second column is name. Therefore, we need to insert the ID as the first value, and the name as the second.

Select Table Data

Use SELECT to select data from a table. A full list of SELECT parameters can be found in the "Queries" chapter of the official PostgreSQL documentation.

For example, to list all of the contents of our testtable the command is:

SELECT * from testtable;

This will return all of the table contents:

testdb=# SELECT * from testtable;
  id   | name
-------+-------
 1     | Alice
 2     | Bob
(2 rows)

You can also specify matching conditions. For example, use SELECT * FROM testtable where id = '1'; to select only the record with ID of 1:

testdb=# SELECT * FROM testtable where id = '1';
  id   | name
-------+-------
 1     | Alice
(1 row)

You can also filter out which columns you want to select. For example, use SELECT name from testtable; to see only the name field for all records:

testdb=# SELECT name from testtable;
 name
 -------
 Alice
 Bob
(2 rows)

Update a Record

Use UPDATE [table name] SET [new values] WHERE [matching condition] to update a record. A full list of UPDATE parameters can be found in the "Updating Data" chapter of the official PostgreSQL documentation.

For example, to change the record with ID of 2 from Bob to Carl the command is:

UPDATE testtable SET name = 'Carl' WHERE id = '2';

Use SELECT to verify that the record was updated correctly:

testdb=# SELECT * FROM testtable;
  id   | name
-------+-------
 1     | Alice
 2     | Carl
(2 rows)

Troubleshooting

The most common issue new PosgreSQL users encounter is that you need to switch to the postgres user before connecting to PosgreSQL. MySQL/MariaDB users are accustomed to logging in to MySQL/MariaDB from the command line as their regular user. But PostgreSQL uses a different security model.

In order to log in to PostgreSQL, switch to the user:

su - postgres

You can then log in to the PostgreSQL client with the command:

psql

For more troubleshooting tips, see our related articles.

Related

Comments

Tags: PostgreSQL