PostgreSQL is one of the most widely used databases in the world & is also very easy to administer. In this tutorial, we will learn some important postgresql commands that every beginner should know.

In our previous tutorials, we have learned to install postgresql on CentOS/RHEL, on Ubuntu & also learned to install Postgresql from source. So if you need to install postgresql first, please refer to those tutorials.

Recommended Read: Scheduling CRON Jobs with Crontab for Beginners

Also Read: Beginner’s guide to Backup Postgres Database

PostgreSQL commands

1- Connect to the database

Connection from localhost,

# su – postgres

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

$ psql

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

postgres=# \q

Connecting from Remote server,

# psql -h host_ip -U user_name -p port_number -d database_name

2- Create role/user

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;

To create a user with password,

$ CREATE USER test PASSWORD ‘enter password here’

3- Delete a role/user

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

postgres=# DROP ROLE test;

4- Show list of users (List of roles)

postgres=# \du

5- Create a new Database

To create a new database, use the following,

postgres=# CREATE DATABASE thelinuxgurus;

6- Delete a database

To delete a created database, use

postgres=# DROP DATABSE thelinuxgurus;

7- Show list of databases

postgres=# \l

8- Change DB when you are connected to other DB

postgres=# \c thelinuxgurus;

Or we can also user the following command,

postgres-# \connect new_database

9- Create a table

To create a table, connect to the desired database & create a table with the following command,

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

Now insert some records into it,

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

10- Delete a table

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

thelinuxgurus=> DROP TABLE USERS;

11- List tables

postgres=# \dt

12- Describe a table to show its structure

postgres=# \d table_name

13- 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,

thelinuxgurus=> ALTER TABLE USERS ADD date_of_birth date;

14- Updating a Row

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

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

thelinuxgurus=> SELECT * FROM USERS;

The last command was to verify the changes that were made.

15- Remove a Column

To remove a column from a table, run

thelinuxgurus=> ALTER TABLE USERS DROP date_of_birth;

16- Remove a Row

To delete a row, use the following example,,

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

17- List of all functions

To check all the functions on the database, use

postgres=# \df

18- To edit function

To edit a function, use the following command,

postgres=# \ef function_name

19- List of all schema in DB

To see all the schemas on the database, use

postgres=# \dn

20- To check the current Installed version

postgres=# select version();

21- To display the command history

postgres=# \s

22- Execute psql commands using a file

So if we have a file (like a .sql file) & we need to execute the file into our database, then use the following command

postgres=# \i /path/file_name

23- To turn on query execution time

postgres=# \timing

24) To list database views

postgres=# \dv

25) Check current date

postgres=# select now();

These were some of the important PostgreSQL commands that everyone should know about to maintain & administer the PostgreSQL database. Please feel free to send in any question or queries using the comment box below.

If you think we have helped you or just want to support us, please consider these:-

Connect to us: Facebook | Twitter | Linkedin

TheLinuxGURUS are thankful for your continued support.