20 Useful Tips for PostgreSQL Beginners

PostgreSQL is an advanced database management system used for storing structured or non-structured data. It supports both SQL (relational) and JSON (non-relational) querying, which makes it more advance than other traditional databases.

In this beginners tutorial, you will learn about basic PostgreSQL commands and SQL statements. We have tried to include all basic commands, required for beginners to start working with it. Also, I request all our readers to send their valuable suggestions to improve this article.

This tutorial doesn’t cover the installation of the PostgreSQL server. You can visit here to install the PostgreSQL server as per your operating system.

1. Login to the PostgreSQL

Use the following commands to connect to the PostgreSQL server.

su - postgres 
psql 

2. Create a new database

Use CREATE DATABASE statement to create a new database in PostgreSQL server.

postgres=# CREATE DATABASE db_1

You can also use command line utility to create database directly.

createdb db_1 

3. Drop database

You can delete any PostgreSQL database with DROP DATABASE statement or dropdb from Linux shell.

postgres=# DROP DATABASE db_1;

To delete database from Linux terminal

dropdb db_1 

4. List all databases

Type \l or \list to list all available databases in current PostgreSQL server.

postgres=# \l

5. Connect to database

This is to connect to the specific database. Once you connected, all the statements will be executed on that database.

postgres=# \c db_1 

6. Create table in a database

Use CREATE TABLE statement to create a new table in database. Before creating a database, you must have connected to the database. Here is a sample statement to create a table in the PostgreSQL database.

db_1=# CREATE TABLE table_1 (name VARCHAR(20),
tbl_id INT,
tbl_name VARCHAR(100),
tbl_date DATE);

7. List tables in a database

The following statement is used to list all tables in the current database.

db_1=# \dt

8. List columns of a table

Use \d followed by the table name to descirbe the table structure.

db_1=# \d table_1

9. Add columns in existing table

db1=# ALTER TABLE table_1 ADD tbl_address VARCHAR(400);

10. Delete column from table

To remove a column name from the existing table, use the below statement.

db1=#  ALTER TABLE tbl1 drop tbl_address;

11. Create role in postgres

Create a new role on the PostgreSQL server using the following command.

postgres=# CREATE ROLE role1 WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;

You must change 「role1」 with your role name and change 「password」 with a secure password.

12. List roles in postgres

List all the available roles in the Postgres server. This is good practice for the database administrator to keep check the current roles on a production server and remove them if not required.

postgres=# \du

13. Grant privileges to role on database

Once a created a new role, You must provide access to the database. This will allow users to access the database.

postgres=# GRANT ALL PRIVILEGES ON DATABASE db1 TO role1;

14. Alter specific role

You can add or remove specific permissions from a role at any time. To change a role use the following command.

postgres=# ALTER ROLE role1 CREATEROLE CREATEDB SUPERUSER;

Click here to get list of roles in postgresql.

15. Drop specific role

If the role is no more required, delete it with DROP ROLE statement.

postgres=# DROP ROLE role1;

16. Backup database

Backup a single database from Postgres server and save in a file.

pg_dump db_1 > backup.sql  

17. Restore database

Use the following command to restore the Postgres database from a backup file.

psql db_1 < backup.sql  

18. Backup all databases

You can also backup all databases in a single command and store in a file.

pg_dumpall > pgbackup.sql 

19. Show all runtime parameters of database

The SHOW ALL statement will list all the runtime parameters set on database server.

postgres=# SHOW ALL;

20. PostgreSQL Web Interface

Yes, phpPgAdmin can be best option for you. Use following link to install.

Install phpPgAdmin on CentOS/RHEL and Fedora