Important PostgreSQL commands you should know

In this tutorial, we will learn about some useful PostgreSQL commands. We have already discussed the installation of PostgreSQL on CentOS & on Ubuntu systems. So let’s start,

Recommended Read : MongoDB installation & configuration on RHEL/CentOS

Also Read : Installing & Configuring MariaDB on RHEL/CentOS

PostgreSQL Commands

Connecting to PostgreSQL

When we install Postgres on our servers, a new user by the name ‘postgres’ is also created along with the installation. So when we are to connect to the PostgreSQL server, we will switch to this user & than connect the database,

# su – postgres

Now to connect to database, we will run the command ‘psql’,

$ psql

Once into the database, we can use ‘\q’ to exit the database,

postgres=# \q

Creating Roles

Now the PostgreSQL allows access to database based on roles, they are similar to ‘users’ in Linux system. Also we can create a set of roles, which is similar to ‘groups’ in Linux & based on these roles, a user’s access is determined. A role created will be applied globally & we don’t have to create it again for another database on the same server.

To create a role, first connect to database & then we will use command ‘createuser’,

postgres=# CREATE USER test;

Or we can also use the following,

postgres=# CREATE ROLE test;

Both these postgresql commands perform same functions.

To create a user with password,

$ CREATE USER test PASSWORD ‘enter password here’

Check all roles

To check all roles, connect to postgres database & than run the following command,

postgres=# \du

Delete a role

To delete a role, we will use the DROP command,

postgres=# DROP ROLE test;

Create a new Database

To create a new database, use the following,

postgres=# CREATE DATABASE linuxtechlab;

Delete a database

To delete a created database, use

postgres=# DROP DATABSE linuxtechla;

List all database

To list all database, run the following command,

postgres=# \l

or we can also use,

postgres=# \list

Connect to a database

To connect a database, first switch to the created user/role,

$ sudo -i -u test

than connect to database the following command,

$ psql -d linuxtechlab

Change to another database

Once connected to a database, we can also switch to another database without having to repeat the whole process of loggin into user & than connecting to different different database. We use the following command,

linuxtechlab=> \connect new_database

Create Table

To create a table, first connect to the desired database where the table is to be created. Next create table with the command,

linuxtechlab=> CREATE TABLE USERS (Serial_No int, First_Name varchar, Last_Name varchar);

Now insert some records into it,

linuxtechlab=> INSERT INTO USERS VALUES (1, ‘Dan’, ‘Prince’);

Check the tables database

To check the inserted table data, use

linuxtechlab=> SELECT * FROM USERS ;

& it will produce all the inserted data from the table USERS.

Delete a table

To delete a table from the database, we will use,

linuxtechlab=> DROP TABLE USERS:

List all the tables in a database

To check the tables of a database, use

linuxtechlab=> \dt

Adding a column to a table

Once a table has been created, it can be altered to add new columns to it using the following command,

linuxtechlab=> ALTER TABLE USERS ADD date_of_birth date;

Updating a Row

We can not only alter the columns of a table but can also update the records that are entered in rows,

linuxtechlab=> UPDATE USERS SET date_of_birth = ‘03-04-1990’ WHERE Seriel_No = ‘1’;

linuxtechlab=> SELECT * FROM USERS;

Last command was to verify the changes that were made.

Remove a Column

To remove a column from a table, run

linuxtechlab=> ALTER TABLE USERS DROP date_of_birth;

Remove a Row

To delete a row, use the following example,,

linuxtechlab=> DELETE FROM USERS WHERE Seriel_No = ‘1’;

Now these were only some of the important & useful PostgreSQL commands that we can use to work on PostgreSQL database. Please feel free to send in any questions you have regarding the tutorial using the comment box below.