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 - postgrespsql
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.
