How to Use PostgreSQL on Linux

Introduction

PostgreSQL or Postgres is a free and open-source RDBMS emphasizing extensibility and SQL compliance(wikipedia). For more information on “PostgreSQL,” see the official documentation. This post focuses on how to use PostgreSQL in Linux.

Installing using apt

The following methods are provided on the PostgreSQL Document. If it’s not Debian-based Linux such as Ubuntu, You need to try another way for your OS.

# Create the file repository configuration:
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists:
sudo apt-get update
# Install the latest version of PostgreSQL.
sudo apt-get -y install postgresql

Create new PostgreSQL database

In order to use PostgreSQL, create new database using the createdb tool. Enter this command in your terminal:

$ createdb test

When it’s first installed, You’ll encounter this error because PostgreSQL only has the ‘postgres’ user.

FATAL: role "[YOUR USER NAME]" does not exist

In this case, You need to create a user with the same name as your current logged in user. The way to do that is as follows:

# Run PostgreSQL CLI Client(psql) as a "postgres" user
$ sudo -u postgres psql
postgres=#
# Create the User
postgres=# CREATE USER your-user-name-here WITH SUPERUSER;
# Verify that it was created
postgres=# \du
# quit or exit
postgres=# \q

If the user was created, try again this: createdb test.

Access the database

We made a new database. Now let’s access this database. To access a particular database, use psql .

To access the test database, you can simply run the following from your command prompt:

# Access the 'test' database.
$ psql test
test=#

And then you can use PostgreSQL commands like SELECT, INSERT, etc on this database.

for example:

test=# SELECT * FROM PG_USER;

Thanks for reading! :)