Intro to PostgreSQL
Table of Contents
- PostgreSQL vs MySQL/MariaDB
- Install PostgreSQL
- Log In to the PostgreSQL Client
- Create, Select, and Drop a Database
- Create and Drop a Table
- Working With Records: Insert, Select, and Update Data
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)
- 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.
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
su to the
sudo su - postgres
Then enter the PostgreSQL client
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
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
\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)
\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
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:
Then hit Enter.
Create and Drop a Table
Create a Table
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) );
\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
DROP TABLE [table name]; to delete a table. For example, to delete the
testtable table, the command is:
DROP TABLE testtable;
\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
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
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
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
Carl the command is:
UPDATE testtable SET name = 'Carl' WHERE id = '2';
SELECT to verify that the record was updated correctly:
testdb=# SELECT * FROM testtable; id | name -------+------- 1 | Alice 2 | Carl (2 rows)
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:
For more troubleshooting tips, see our related articles.