30 Essential PostgreSQL Commands

 

Introduction

PostgreSQL, an advanced and powerful open-source relational database system, offers a plethora of commands that are essential for database management. Mastering these commands can significantly enhance your ability to interact with and manage PostgreSQL databases effectively. In this post, we’ll explore 30 essential PostgreSQL commands, complete with examples and explanations to ensure you get the most out of your PostgreSQL experience.

1. Connecting to a Database

Before you can execute any commands, you need to connect to your PostgreSQL database. Use the following command:

psql -h hostname -d dbname -U username

2. Listing All Databases

To list all the databases in your PostgreSQL server, use:

\l

This command is particularly useful when you want to see an overview of all existing databases.

3. Creating a New Database

Creating a new database is straightforward with the following command:

CREATE DATABASE dbname;

Replace dbname with your desired database name.

4. Connecting to a Specific Database

After creating a database, you might want to connect to it:

\c dbname

This command switches your connection to the specified database.

5. Listing All Tables

To see all the tables in your current database, use:

\dt

This command provides a quick overview of the tables.

Photo by admingeek from Infotechys

6. Creating a New Table

Creating a table involves specifying the table structure:

CREATE TABLE tablename (
    id SERIAL PRIMARY KEY,
    column1 TEXT,
    column2 INTEGER
);

Modify tablename and column definitions as needed.

7. Inserting Data into a Table

To insert data into a table, use the following command:

INSERT INTO tablename (column1, column2) VALUES ('value1', 123);

8. Selecting Data from a Table

Selecting data allows you to view the table contents:

SELECT * FROM tablename;

9. Updating Data in a Table

To update existing data, execute:

UPDATE tablename SET column1 = 'new_value' WHERE id = 1;

10. Deleting Data from a Table

Deleting data is just as simple:

DELETE FROM tablename WHERE id = 1;

11. Dropping a Table

To remove a table and its data:

DROP TABLE tablename;

12. Adding a Column to a Table

If you need to add a new column to an existing table:

ALTER TABLE tablename ADD COLUMN new_column TEXT;

13. Dropping a Column from a Table

Conversely, to remove a column:

ALTER TABLE tablename DROP COLUMN column_name;

14. Renaming a Table

To rename a table:

ALTER TABLE oldname RENAME TO newname;

15. Creating an Index

Indexes improve query performance:

CREATE INDEX indexname ON tablename (columnname);

16. Dropping an Index

To remove an index:

DROP INDEX indexname;

17. Viewing Table Structure

To view the structure of a table:

\d tablename

18. Granting Privileges

To grant privileges to a user:

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

19. Revoking Privileges

Revoking privileges is equally important:

REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

20. Backing Up a Database

For backup purposes:

pg_dump dbname > backupfile.sql

21. Restoring a Database

To restore a database from a backup:

psql dbname < backupfile.sql

22. Executing SQL File

Run a SQL file using:

\i filename.sql

23. Exiting psql

To exit the psql interface:

\q

24. Changing User Password

Change a user’s password with:

ALTER USER username WITH PASSWORD 'newpassword';

25. Viewing Active Connections

To see all active connections:

SELECT * FROM pg_stat_activity;

26. Terminating a Connection

Terminate a specific connection:

SELECT pg_terminate_backend(pid);

27. Checking PostgreSQL Version

To check your PostgreSQL version:

SELECT version();

28. Vacuuming a Database

For database maintenance:

VACUUM;

29. Analyzing a Database

To gather statistics for query optimization:

ANALYZE;

30. Setting Configuration Parameters

Change configuration settings with:

ALTER SYSTEM SET parameter = 'value';

Table of Commands and Descriptions

Command	Description
psql -h hostname -d dbname -U username	Connect to a PostgreSQL database
\l	List all databases
CREATE DATABASE dbname;	Create a new database
\c dbname	Connect to a specific database
\dt	List all tables in the current database
CREATE TABLE tablename (...)	Create a new table
INSERT INTO tablename (columns) VALUES (...);	Insert data into a table
SELECT * FROM tablename;	Select data from a table
UPDATE tablename SET column = value WHERE ...;	Update data in a table
DELETE FROM tablename WHERE ...;	Delete data from a table
DROP TABLE tablename;	Drop a table
ALTER TABLE tablename ADD COLUMN ...;	Add a column to a table
ALTER TABLE tablename DROP COLUMN ...;	Drop a column from a table
ALTER TABLE oldname RENAME TO newname;	Rename a table
CREATE INDEX indexname ON tablename (...);	Create an index
DROP INDEX indexname;	Drop an index
\d tablename	View table structure
GRANT ALL PRIVILEGES ON DATABASE ... TO ...;	Grant privileges to a user
REVOKE ALL PRIVILEGES ON DATABASE ... FROM ...;	Revoke privileges from a user
pg_dump dbname > backupfile.sql	Backup a database
psql dbname < backupfile.sql	Restore a database
\i filename.sql	Execute a SQL file
\q	Exit psql
ALTER USER username WITH PASSWORD ...;	Change user password
SELECT * FROM pg_stat_activity;	View active connections
SELECT pg_terminate_backend(pid);	Terminate a connection
SELECT version();	Check PostgreSQL version
VACUUM;	Vacuum a database
ANALYZE;	Analyze a database
ALTER SYSTEM SET parameter = value;	Set configuration parameters

By integrating these commands into your workflow, you will be well-equipped to handle various database tasks with ease and efficiency.