PSQL Connect to Database: How to Manage PostgreSQL from the Command Line

Relational database management systems are a key component of many websites and applications. They provide a structured way to store data and access information in a structured way. PostgreSQL is a relational database management system (RDBMS) that implements the SQL query language. It is a popular choice for many small and large projects because it has many advanced features, such as reliable transactions and concurrency without read locks. This article will show how to manage PostgreSQL from the command line, commonly referred to as PSQL connect to database.

Conventions

# – given commands should be executed with root privileges either directly as a root user or by use of sudo command
$ – given commands should be executed as a regular user

PostgreSQL Roles and Databases

Postgres uses a concept called “roles” to handle authentication and authorization. This concept is similar to regular Unix accounts, but Postgres uses the term “role” to distinguish between user privileges.

Postgres is set up during installation with ident authentication, which means it associates roles with Unix/Linux system accounts. If a role exists in Postgres, then a Unix/Linux username with the same name can log in using that role.

Log in to the PostgreSQL Account

The installation procedure creates an account named postgres with the Postgres role built into it. In order to use Postgres, we must log in to the system using that account.

Log in to the postgres account on the server using:

$ sudo -iu postgres

Now, we can access the PostgreSQL prompt with the command

$ psql

We can then login and interact with the database management system. To exit the PostgreSQL prompt, run this command, then hit ENTER:

\q

Access the PostgreSQL Prompt Without Changing Accounts

We can also run the desired commands directly through sudo. For example, in the previous example, we only want to log in to the Postgres prompt. We can log in to the postgres role with the current user using sudo:

$ sudo -u postgres psql

This command will bring us into PostgreSQL without logging into one of its roles.

We can exit with the command:

\q

Create a New Role

After installing PostgreSQL server, we only have an existing postgres role with the database. We can create a new role using the createuser command. The –interactive flag will prompt for the required values.

Let’s execute this command below to create a new PostgreSQL role:

$ sudo -u postgres createuser --interactive

The command above will give you several options, and based on the response given, it will execute the PostgreSQL command needed to create a new user.

Enter name of role to add: master
Shall the new role be a superuser? (y/n) y

We can provide more options with additional flags. Check the available options with the command:

$ man createuser

Create a New Database

The Postgres authentication system automatically creates a database with the same name as the role so that the role has access to it.

Previously, we created a user named master who will automatically have access to the database named master. We can create the necessary database using the createdb command. If you are logged in using the postgres account, type the command:

$ createdb master

If you prefer to use sudo for each command without switching accounts, you can type:

$ sudo -u postgres createdb master

Open a Postgres Prompt with a New Role

To log in using ident-based authentication, you will need a Linux user with the same role name and the same Postgres database.

If a user with the same name does not exist, you will need to create one using the adduser command. You will need to run this command with sudo privileges (not from postgres but from a normal Linux user):

$ sudo adduser master

Follow the steps on the screen when prompted, and once the required account is available, you can switch accounts and log in to the database using the command:

$ sudo -iu master
$ psql

Or you can also use the following one line:

$ sudo -u master psql

You will be logged in automatically.

If we want to connect to a different database than the current user, we can write the database name like this:

$ psql -d postgres

Once logged in, we can check the connection information with the command:

\conninfo

You will see this as the output:

You are connected to database "master" as user "master" via socket in "/var/run/postgresql" at port "5432".

Create and Delete Tables

Once we are connected to the PostgreSQL database system, we can perform some simple tasks. First, we will create a table to store data. Let’s create a table that describes a list of servers for a data center.

The basic syntax for creating a table is as follows:

CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);

As you can see, we have written the table name, the desired columns, the column types, and the maximum data length that can be filled. We can also add constraints for each column if necessary.

Now, we will create a table like this:

CREATE TABLE datacenter (
equip_id serial PRIMARY KEY,
rackname varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar (25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);

We have created a datacenter table to store our servers. This table has a serial type and an auto-incrementing integer. We can provide a primary key constraint for this column, meaning the content must be unique and not null.

We do not provide a length for the two columns equip_id and install_date. This is because some column types do not require a maximum length.

The rackname and color columns should not null. We also create a location column and a constraint to limit its value to 8 options. The last column is the install_date column which tells us when we installed it.

We can see the newly created table with the command:

\d

You will see this after running the command above.

                  List of relations
 Schema |          Name           |   Type   | Owner  
--------+-------------------------+----------+--------
 public | datacenter              | table    | master
 public | datacenter_equip_id_seq | sequence | master
(2 rows)

We already have a datacenter table, but we also have another record called datacenter_equip_id_seq with a sequence type. This record represents the serial type that we gave earlier for the equip_id column. It will record the next number to automatically create data for columns of the serial type.

If you want to see the table data without its sequence, type:

\dt

You will see this:

          List of relations
 Schema |    Name    | Type  | Owner  
--------+------------+-------+--------
 public | datacenter | table | master
(1 row)

Add, Query, and Delete Data In a Table

After having a table, we can insert data into it. We insert data by calling the table we want to add and giving the column names and data for each column.

INSERT INTO datacenter (rackname, color, location, install_date) VALUES ('mercurius', 'red', 'south', '2020-02-16');
INSERT INTO datacenter (rackname, color, location, install_date) VALUES ('venus', 'grey', 'northwest', '2021-09-05');

There are a few things to note when entering data to minimize errors. First, the column name should not be marked with ”, but the contents of the column need to be provided.

We also need to avoid providing the equip_id column data manually. The contents will be created automatically.

We can see the data that has been entered with the command:

SELECT * FROM datacenter;

Our output should be like this:

equip_id | rackname | color | location | install_date
----------+-----------+-------+-----------+--------------
1 | mercurius | red | south | 2020-02-16
2 | venus | grey | northwest | 2021-09-05
(2 rows)

Here, we can see that the equip_id column already exists automatically, and the other data has been successfully added.

If mercurius is no longer used and decommissioned, we have to delete it. To delete data from a table, use the command:

DELETE FROM datacenter WHERE rackname = 'mercurius';

If we want to see the contents of our table again, we will find that the slide is no longer there:

SELECT * FROM datacenter;

And this is the output:

equip_id | rackname | color | location | install_date
----------+----------+-------+-----------+--------------
2 | venus | grey | northwest | 2021-09-05
(1 row)

How to Add and Remove Columns from a Table

We can add new columns to an existing table easily. We can add a column to display when the rackname was last maintained:

ALTER TABLE datacenter ADD last_maint date;

To see the table information again, use the command (note the last column added):

SELECT * FROM datacenter;

You will see this:

equip_id | rackname | color | location | install_date | last_maint
----------+----------+-------+-----------+--------------+------------
2 | venus | grey | northwest | 2021-09-05 |
(1 row)

How to Update Data in a Table

In the previous step, we learned how to add data to a table and delete it, but we didn’t discuss how to modify the existing data.

For example, let’s say we want to retrieve data of type “venus” and change its color to red (this will replace all data of type “venus”):

UPDATE datacenter SET color = 'red' WHERE rackname = 'venus';

We can check that the command worked by:

SELECT * FROM datacenter;

And this is the output:

equip_id | rackname | color | location | install_date | last_maint
----------+----------+-------+-----------+--------------+------------
2 | venus | red | northwest | 2021-09-05 |
(1 row)

As you can see, the color has changed.

Congratulation! You have learned about PSQL Connect to Database: How to Manage PostgreSQL from the Command Line. You should know the PostgreSQL basic commands now.

Of course, if you are one of our PostgreSQL Hosting customers, you don’t have to learn how to manage PostgreSQL from the command line yourself or even what people mean when they say PSQL connect to database. Ask our admins, sit back, and relax. They will help you manage your PostgreSQL database immediately and without any additional fee. Managing a PostgreSQL server is not just about the installation, we can help you with optimizing your PostgreSQL server if you have a VPS with us.

If you liked our PSQL connect to database post using the command line, please share it with your friends and leave a comment below. Thanks.