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.

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

Connect to us: Facebook | Twitter | Google Plus

Donate us some of your hard earned money: [paypal-donation]

Linux TechLab is thankful for your continued support.