PostgreSQL, we generally call Postgres, is the most popular open-source SQL database, and if you wonder what makes it so special is its reliability, performance, and support for advanced database features.
To work efficiently with PostgreSQL, understanding PSQL commands is extremely important. These commands allow you to create databases, manage tables, manipulate data, and control users with ease.
Welcome to our complete guide on PostgreSQL commands. This guide will help you understand and learn every single Postgres command using simple explanations and examples.
Connection and Basic PostgreSQL Commands
We begin our list with essential PostgreSQL commands used to connect to the database and navigate the interface. These are the first steps we take before working with any data.
\c
This command connects us to a specific database. We type the command followed by the database name. We use it when we need to switch between different databases on the same server. The result is a new connection to the target database.
Example: \c sales_data
\conninfo
We use this command to display details about our current connection. It shows the database name, user name, host, and port. We run it to verify that we are connected to the correct place. The result is a summary of connection settings.
Example: \conninfo
\q
This command quits the PostgreSQL interface immediately. We use it when we finish our work and want to close the terminal window. It exits the program cleanly. The result is a return to our system command line.
Example: \q
\l
We use this to list all databases in the system. It provides a quick overview of every database we can access. We run it to see available databases before connecting to one. The result is a table of database names, owners, and other details.
Example: \l
\dt
This command lists all tables in the current database. We use it to see what relations exist in our schema. It helps us verify our table creation process. The result is a list of table names and their owners.
Example: \dt
\du
We use this to display all user roles in the database. It shows usernames and their specific attributes. We run it to check who has access to the system. The result is a list of roles and their privileges.
Example: \du
\d
This describes a specific table, view, or sequence. We type the command followed by the object name. We use it to see the structure, columns, and data types of an object. The result is a detailed schema report.
Example: \d customers
Database and Schema Management PostgreSQL Commands
Creating and organizing our data storage requires specific PostgreSQL commands. These commands help us define where our data lives and how it is structured at a high level.
CREATE DATABASE
This command creates a new database on the server. We provide a unique name for the new database. We use it when we need a separate space for a new project. The result is a new, empty database ready for tables.
Example: CREATE DATABASE inventory;
DROP DATABASE
We use this to remove a database permanently. We must be careful because it deletes all data inside. We run it only when we are sure we no longer need the database. The result is the complete removal of the database.
Example: DROP DATABASE inventory;
CREATE SCHEMA
This command creates a new schema within a database. A schema helps us organize tables and other objects into groups. We use it to keep our database structure clean and logical. The result is a new namespace for objects.
Example: CREATE SCHEMA finance;
DROP SCHEMA
We use this to delete a schema and all its objects. If the schema contains items, we must add the cascade option. We run it to remove an entire group of tables. The result is the removal of the schema.
Example: DROP SCHEMA finance CASCADE;
Table Management PostgreSQL Commands
Tables are the core of any database. These PostgreSQL commands allow us to build and manage the structures that hold our data.
CREATE TABLE
This command creates a new table in the database. We define the table name and columns with data types. We use it to store data in a structured format. The result is a new table ready to accept records.
Example: CREATE TABLE users (id INT, name TEXT);
DROP TABLE
We use this to delete a table and all its data permanently. We type the table name we want to remove. We run it when a table is no longer needed or was created by mistake. The result is the complete removal of the table.
Example: DROP TABLE users;
TRUNCATE
This command removes all rows from a table quickly. It keeps the table structure but empties the data. We use it when we want to clear a table without deleting the table itself. The result is an empty table.
Example: TRUNCATE TABLE users;
ALTER TABLE
We use this to change the definition of an existing table. It allows us to add, delete, or modify columns. We run it when our data requirements change. The result is an updated table structure.
Example: ALTER TABLE users ADD COLUMN email TEXT;
RENAME TABLE
This command changes the name of an existing table. We use the old name and specify the new one. We run it to make table names more clear or consistent. The result is a table with a new name.
Example: ALTER TABLE users RENAME TO customers;
Column Modification PostgreSQL Commands
Sometimes we need to fine-tune our tables. We use these PostgreSQL commands to adjust individual columns within our tables.
ADD COLUMN
We add a new column to an existing table with this command. We specify the column name and its data type. We use it when we need to store additional information. The result is a new column in the table.
Example: ALTER TABLE products ADD COLUMN price INT;
DROP COLUMN
This command removes a column from a table. We specify which column we want to delete. We use it when we no longer need to store that specific piece of data. The result is the column disappearing from the table.
Example: ALTER TABLE products DROP COLUMN price;
RENAME COLUMN
We use this to change the name of a specific column. We provide the old name and the new name. We run it to make column names clearer. The result is a column with a new label.
Example: ALTER TABLE products RENAME COLUMN price TO cost;
ALTER COLUMN TYPE
This command changes the data type of a column. We specify the column and the new type we want. We use it when the data format needs to change. The result is the column converting to the new data type.
Example: ALTER TABLE products ALTER COLUMN price TYPE NUMERIC;
Data Insertion PostgreSQL Commands
Once we have tables, we need to put data into them. These PostgreSQL commands handle the entry of information into our database.
INSERT INTO
We use this command to add new rows of data to a table. We specify the table and the values we want to insert. We use it to populate our tables with information. The result is new data added to the table.
Example: INSERT INTO users (name, email) VALUES ('John', '[email protected]');
INSERT INTO (Multiple)
This command allows us to insert multiple rows at once. We list sets of values separated by commas. We use it to add data efficiently in bulk. The result is many new rows added quickly.
Example: INSERT INTO users (name) VALUES ('Anna'), ('Bob'), ('Cara');
COPY
We use this command to import data from a file into a table. It copies data directly from a source file like CSV. We run it to load large datasets quickly. The result is the data from the file appearing in the table.
Example: COPY users FROM '/path/to/file.csv' DELIMITER ',' CSV;
Data Update and Delete PostgreSQL Commands
Data changes over time. We use these PostgreSQL commands to modify or remove existing records in our tables.
UPDATE
This command changes existing data in a table. We specify the table, the column to change, and the new value. We use it to correct errors or update old information. The result is the modified data in the table.
Example: UPDATE users SET email = '[email protected]' WHERE id = 1;
DELETE
We use this command to remove specific rows from a table. We use a condition to target which rows to delete. We run it to remove data that is no longer correct. The result is the removal of those rows.
Example: DELETE FROM users WHERE id = 1;
RETURNING
We use this clause with UPDATE or DELETE to see the affected rows. It returns the data that was just changed or removed. We use it to verify what the command did. The result is a display of the modified data.
Example: DELETE FROM users WHERE id = 1 RETURNING *;
Data Querying PostgreSQL Commands
Retrieving data is the most common task we perform. These PostgreSQL commands let us ask questions and get answers from our data.
SELECT
This command retrieves data from one or more tables. We specify which columns we want to see. We use it to view and analyze our stored data. The result is a set of rows containing the requested information.
Example: SELECT name, email FROM users;
SELECT *
We use the asterisk symbol to select all columns in a table. It is a quick way to see everything without typing column names. We use it for exploration or small tables. The result is every column for the chosen rows.
Example: SELECT * FROM users;
SELECT DISTINCT
This command removes duplicate values from the results. We use it when we only want to see unique entries in a column. We run it to get a list of unique items. The result is a list without repeats.
Example: SELECT DISTINCT country FROM customers;
AS (Alias)
We use this to give a column or table a temporary name. It makes output easier to read or understand. We use it to rename headers in our result set. The result is data displayed with the new labels.
Example: SELECT name AS customer_name FROM users;
Filtering PostgreSQL Commands
To find specific data, we need to filter our results. These PostgreSQL commands help us narrow down our queries to exactly what we need.
WHERE
This clause filters records based on a specific condition. We use it to retrieve only rows that meet our criteria. We run it to find specific subsets of data. The result is a list of rows that match the condition.
Example: SELECT * FROM users WHERE city = 'London';
AND
We use this operator to combine multiple conditions. Both conditions must be true for a row to be selected. We use it to make our search more specific. The result is rows that satisfy all requirements.
Example: SELECT * FROM orders WHERE status = 'shipped' AND amount > 100;
OR
This operator selects rows if at least one condition is true. We use it when we want rows that match either condition. We run it to broaden our search criteria. The result is rows that meet any of the conditions.
Example: SELECT * FROM users WHERE city = 'London' OR city = 'Paris';
IN
We use this operator to specify a list of possible values. It matches any value listed in the parentheses. We use it as a shorthand for multiple OR conditions. The result is rows that match any value in the list.
Example: SELECT * FROM users WHERE country IN ('USA', 'Canada', 'Mexico');
BETWEEN
This command filters results within a given range. We use it to select values that fall between two numbers or dates. We run it to find data within a specific interval. The result is rows that fall inside the range.
Example: SELECT * FROM products WHERE price BETWEEN 10 AND 50;
LIKE
We use this operator to search for a pattern in a column. It often uses the percent sign as a wildcard. We use it to find text that matches a specific format. The result is rows that contain the pattern.
Example: SELECT * FROM users WHERE name LIKE 'A%';
ILIKE
This works like LIKE but ignores case differences. We use it to search for patterns regardless of uppercase or lowercase letters. We run it for case-insensitive text matching. The result is rows that match the pattern in any case.
Example: SELECT * FROM users WHERE name ILIKE 'john%';
IS NULL
This command checks for empty or missing values. We use it to find rows where data is not entered. We run it to identify incomplete records. The result is rows that have null values in that column.
Example: SELECT * FROM customers WHERE phone IS NULL;
IS NOT NULL
We use this to find rows that have data in a specific column. It ensures the field is not empty. We use it to filter out missing information. The result is rows that contain actual values.
Example: SELECT * FROM customers WHERE phone IS NOT NULL;
NOT
This operator reverses the result of a condition. We use it to exclude rows that match a specific criteria. We run it to find the opposite of what a condition specifies. The result is rows that do not fit the condition.
Example: SELECT * FROM users WHERE NOT city = 'London';
Sorting and Limiting PostgreSQL Commands
Organizing our output makes it easier to read. These PostgreSQL commands help us control the order and quantity of our results.
ORDER BY
We use this clause to sort the result set in a specific order. We can sort by one or more columns. We use it to organize data alphabetically or numerically. The result is a list sorted as requested.
Example: SELECT * FROM users ORDER BY name ASC;
ASC
This keyword stands for ascending order. We use it with ORDER BY to sort from lowest to highest. It is the default sorting direction for numbers and text. The result is data going up (A to Z, 1 to 10).
Example: SELECT * FROM products ORDER BY price ASC;
DESC
This keyword stands for descending order. We use it with ORDER BY to sort from highest to lowest. We run it when we want the largest values first. The result is data going down (Z to A, 10 to 1).
Example: SELECT * FROM products ORDER BY price DESC;
LIMIT
We use this command to restrict the number of rows returned. We specify the maximum number of rows we want to see. We use it to get a small sample of data. The result is a result set with a capped size.
Example: SELECT * FROM users LIMIT 5;
OFFSET
This command skips a specific number of rows before returning the rest. We use it with LIMIT to paginate through data. We run it to ignore the first few rows. The result is a set of rows starting after the skipped number.
Example: SELECT * FROM users LIMIT 5 OFFSET 10;
FETCH
We use this command to retrieve a specific number of rows. It is a standard SQL way to limit results. We use it similarly to LIMIT but with more standard syntax. The result is a specific number of rows.
Example: SELECT * FROM users FETCH FIRST 5 ROWS ONLY;
Aggregation and Grouping PostgreSQL Commands
We often need to calculate summaries of our data. These PostgreSQL commands allow us to perform math and group data logically.
COUNT
This function counts the number of rows in a table. We can use it to count all rows or only non-null values in a column. We use it to know how many records we have. The result is a single number representing the count.
Example: SELECT COUNT(*) FROM users;
SUM
We use this function to add up values in a numeric column. It calculates the total of a specific field. We use it to find totals like sales or scores. The result is the sum of all values.
Example: SELECT SUM(price) FROM orders;
AVG
This function calculates the average value of a numeric column. We use it to find the mean of a set of numbers. We run it to understand typical values in a dataset. The result is a single average number.
Example: SELECT AVG(age) FROM users;
MIN
We use this function to find the smallest value in a column. It works with numbers, dates, and text. We use it to identify the lowest value or earliest date. The result is the minimum value found.
Example: SELECT MIN(price) FROM products;
MAX
This function finds the largest value in a column. It searches the entire column for the highest entry. We use it to find the highest price or latest date. The result is the maximum value found.
Example: SELECT MAX(price) FROM products;
GROUP BY
This clause groups rows that have the same values. We use it with aggregate functions to summarize data by category. We run it to calculate totals for specific groups. The result is a summary row for each group.
Example: SELECT city, COUNT(*) FROM users GROUP BY city;
HAVING
We use this clause to filter groups created by GROUP BY. It works like WHERE but for aggregated data. We use it to show only groups that meet a condition. The result is groups that satisfy the filter.
Example: SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;
Joining PostgreSQL Commands
Relational databases split data across tables. We use these PostgreSQL commands to bring that data back together.
JOIN
This command combines rows from two or more tables. It links tables based on a related column between them. We use it to see data from multiple sources in one view. The result is a single table with combined columns.
Example: SELECT orders.id, users.name FROM orders JOIN users ON orders.user_id = users.id;
INNER JOIN
This join returns rows when there is a match in both tables. It is the most common type of join. We use it when we only want complete related records. The result is data that exists in both tables.
Example: SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id;
LEFT JOIN
This join returns all rows from the left table and matched rows from the right. If no match exists, it shows NULL on the right side. We use it to see all primary records regardless of matches. The result is all left table data and any matching right data.
Example: SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;
RIGHT JOIN
We use this to return all rows from the right table and matched rows from the left. It keeps all data from the second table. We use it when the right table is the primary focus. The result is all right table data and any matching left data.
Example: SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id;
FULL JOIN
This command returns rows when there is a match in either table. It combines the results of both left and right joins. We use it to see all data from both tables. The result is a complete set of records from both sides.
Example: SELECT * FROM users FULL JOIN orders ON users.id = orders.user_id;
CROSS JOIN
This join returns the Cartesian product of the two tables. It combines every row of the first table with every row of the second. We use it to generate all possible combinations. The result is a very large list of paired rows.
Example: SELECT * FROM users CROSS JOIN products;
Indexing PostgreSQL Commands
Speed is crucial for large databases. These PostgreSQL commands help us optimize our tables for faster searching.
CREATE INDEX
This command creates an index on a specific column. Indexes speed up data retrieval operations. We use it on columns we search or sort frequently. The result is a faster search process at the cost of storage space.
Example: CREATE INDEX idx_user_email ON users (email);
DROP INDEX
We use this to remove an index from the database. We specify the index name we want to delete. We run it if the index slows down inserts or is no longer needed. The result is the removal of the index.
Example: DROP INDEX idx_user_email;
CREATE UNIQUE INDEX
This command creates an index that also enforces uniqueness. It prevents duplicate values in the indexed column. We use it to ensure data integrity and speed. The result is a unique constraint and a performance boost.
Example: CREATE UNIQUE INDEX idx_unique_email ON users (email);
REINDEX
We use this command to rebuild an existing index. It can help if the index becomes bloated or corrupted. We run it to maintain optimal performance. The result is a fresh and efficient index.
Example: REINDEX TABLE users;
View and Sequence PostgreSQL Commands
Complex queries can be saved and reused. These PostgreSQL commands help us manage virtual tables and auto-incrementing numbers.
CREATE VIEW
This command creates a virtual table based on a query. It saves a complex query for future use. We use it to simplify data access for frequent queries. The result is a view that acts like a table.
Example: CREATE VIEW user_orders AS SELECT users.name, orders.id FROM users JOIN orders ON users.id = orders.user_id;
DROP VIEW
We use this to delete a view from the database. It does not affect the underlying data. We run it when the view is no longer required. The result is the removal of the virtual table.
Example: DROP VIEW user_orders;
CREATE SEQUENCE
This command creates a special object that generates numbers. We use it to create unique identifiers for rows. It helps us auto-increment primary keys. The result is a sequence object we can call for the next number.
Example: CREATE SEQUENCE order_id_seq START 1;
NEXTVAL
We use this function to get the next value from a sequence. It advances the sequence and returns the number. We use it when inserting new rows. The result is the next available integer in the sequence.
Example: INSERT INTO orders (id, product) VALUES (NEXTVAL('order_id_seq'), 'Laptop');
Security and Permission PostgreSQL Commands
Keeping data safe is a priority. We use these PostgreSQL commands to control who can access and change data.
CREATE USER
This command creates a new user account with a password. We set login credentials for the database. We use it to allow new people or applications to connect. The result is a new role that can log in.
Example: CREATE USER admin WITH PASSWORD 'secret';
CREATE ROLE
We use this to create a new role that acts as a group. Roles can hold privileges that users inherit. We use it to manage permissions for groups of people. The result is a new role object.
Example: CREATE ROLE sales_team;
GRANT
This command gives specific privileges to a user or role. We can grant permissions like SELECT or INSERT. We use it to control what actions users can perform. The result is the user having the specified access.
Example: GRANT SELECT ON users TO admin;
REVOKE
We use this to remove privileges from a user or role. It takes away previously granted permissions. We run it when access should no longer be allowed. The result is the user losing those specific rights.
Example: REVOKE INSERT ON users FROM admin;
ALTER USER
This command changes the attributes of a user. We can rename a user or change their password. We use it to manage user accounts over time. The result is an updated user account.
Example: ALTER USER admin WITH PASSWORD 'newpassword';
Transactional PostgreSQL Commands
We need to ensure our data remains consistent. These PostgreSQL commands manage blocks of work that must succeed or fail together.
BEGIN
This command starts a new transaction block. All following statements are part of this transaction. We use it when we have a series of related changes. The result is a transaction mode starting.
Example: BEGIN;
COMMIT
We use this to save all changes made during a transaction. It makes the changes permanent in the database. We run it only after we verify the data is correct. The result is the transaction being finalized.
Example: COMMIT;
ROLLBACK
This command undoes all changes made in the current transaction. It reverts the database to the state before the BEGIN. We use it if an error occurs or we change our minds. The result is the cancellation of all recent changes.
Example: ROLLBACK;
SAVEPOINT
We use this to create a marker within a transaction. It allows us to roll back to a specific point instead of the start. We use it for complex error handling. The result is a named point we can revert to.
Example: SAVEPOINT my_savepoint;
Utility PostgreSQL Commands
These helpful PostgreSQL commands provide information about the system and help us understand our queries.
SHOW
This command displays the current value of a runtime parameter. We use it to check configuration settings. We run it to see how the database is currently tuned. The result is the setting value.
Example: SHOW timezone;
EXPLAIN
We use this to see the execution plan of a query. It shows how the database will retrieve the data. We use it to analyze and optimize slow queries. The result is a breakdown of the query steps.
Example: EXPLAIN SELECT * FROM users WHERE id = 1;
ANALYZE
This command collects statistics about the contents of a table. It helps the query planner make better decisions. We run it after loading large amounts of data. The result is updated statistics for the optimizer.
Example: ANALYZE users;
VACUUM
We use this to reclaim storage occupied by dead tuples. It cleans up the database and prevents bloat. We run it regularly to maintain performance. The result is a more compact and efficient database.
Example: VACUUM users;
COALESCE
This function returns the first non-null value in a list. We use it to substitute null values with a default. It helps us handle missing data gracefully. The result is a clean value without nulls.
Example: SELECT COALESCE(phone, 'Unknown') FROM users;
NULLIF
We use this to return null if two expressions are equal. Otherwise, it returns the first expression. We use it to handle specific division-by-zero errors. The result is either a null or the original value.
Example: SELECT NULLIF(amount, 0) FROM payments;
CAST
We use this to convert a value from one data type to another. We can use the double colon syntax as a shortcut. We use it to ensure data types are compatible. The result is the value in the new type.
Example: SELECT CAST('123' AS INTEGER);
CURRENT_DATE
This function returns the current date. We use it to filter or input today’s date. It helps us track events relative to today. The result is the date of the current transaction.
Example: SELECT CURRENT_DATE;
Backup and Restore PostgreSQL Commands
We must protect our data against loss. These PostgreSQL commands allow us to save copies and restore them when needed.
pg_dump
This is a utility to back up a single database. It runs at the system command line, not inside SQL. We use it to create a text file with SQL commands to rebuild the database. The result is a backup file.
Example: pg_dump mydb > mydb_backup.sql
pg_restore
We use this utility to restore a database from a backup file. It reads the archive created by pg_dump. We run it to recover data after a crash or move data. The result is a restored database.
Example: pg_restore -d mydb mydb_backup.sql
pg_dumpall
This command backs up all databases in a cluster. It includes global data like users and groups. We use it to backup the entire server instance. The result is a complete backup file for everything.
Example: pg_dumpall > full_backup.sql
Conclusion
I hope that the way I’ve explained each PostgreSQL command in detail, along with simple examples, helps you learn Postgres, build strong real-world database skills, and manage data with ease.
To advance your PostgreSQL skills, you can also check out this in-depth tutorial that covers almost every essential SQL command with detailed examples: 150+ SQL Commands Explained With Examples (2026 Update).
Reference:
https://www.postgresql.org/docs
