Install and Use PostgreSQL

Table of Contents

Introduction

PostgreSQL is a powerful object-relational database system. It features robust functionality, and supports a large number of concurrent transactions, which makes it an excellent choice for large and/or high-demand database applications.

This tutorial will cover how to install and run PosgreSQL, and an introduction to some of the basic commands.

Requirements

  • A 1&1 Cloud Server running Linux (Ubuntu 16.04 or CentOS 7)

Install on Ubuntu 16.04

Update your server:

sudo apt-get update

Add the official PostgreSQL repository:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Then add the signing keys:

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Install PostgreSQL:

sudo apt-get install postgresql postgresql-contrib

To test the installation, switch to the postgres user:

sudo su - postgres

Then enter the Postgres client psql:

psql

To exit the client, type:

\q

Then hit Enter.

Install on CentOS 7

Update your system:

sudo yum update

Open the repository config file for editing:

sudo nano /etc/yum.repos.d/CentOS-Base.repo

Put the following line at the end of the [base] and [updates] sections:

exclude=postgresql*

Save and exit the file.

Add the official PostgreSQL repository:

sudo yum install https://download.postgresql.org/pub/repos/yum/testing/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm

Then install PostgreSQL:

sudo yum install postgresql10-server.x86_64

Initialize the first database:

sudo /usr/pgsql-10/bin/postgresql10-setup initdb

Then start the process:

sudo systemctl start postgresql-10

Configure it to start automatically if the server is rebooted:

sudo systemctl enable postgresql-10

To test the installation, switch to the postgres user:

sudo su - postgres

Then enter the PostgreSQL client psql:

psql

To exit the client, type:

\q

Then hit Enter.

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)

Content provided by 1&1

Comments

Tags: Database / PostgreSQL