PostgreSQL Commands
Mastering Database Management
Accessing PostgreSQL
Access the PostgreSQL server from psql with a specific user:
psql -U [username];
Connect to a specific database:
\c database_name;
Quit psql:
\q
Get Your Linux Course!
Join our Linux Course and discover the power of open-source technology. Enhance your skills and boost your career! Start learning Linux today for only $1! Common psql Commands
| Command | Description |
|---|---|
| psql -h host -U username -d dbname | Connect to a specified database |
| psql -U username -d dbname | Connect to a local database |
| psql -h host -p 5432 -U username -d dbname | Connect specifying a port |
| psql -h host -U username -d dbname -f file.sql | Execute commands from an SQL file |
| psql -c “SELECT * FROM tablename;” -o output.txt | Export query results to a file |
| psql -c “COPY (SELECT * FROM tablename) TO STDOUT WITH CSV” > data.csv | Export data in CSV format |
| psql -c “\COPY tablename FROM ‘data.csv’ WITH CSV” | Import data from a CSV file |
| createdb -h host -U username newdbname | Create a new database |
| dropdb -h host -U username dbname | Drop an existing database |
| psql -c “\db” | View tablespaces |
Listing Databases and Schemas
| Command | Description |
|---|---|
| \l | List all databases |
| \dn | List all schemas |
| \df | List all functions |
| \dv | List all views |
| \dt | List all tables |
| \dt+ | Get more information on tables |
| \d+ table_name | Get detailed information on a table |
| \df+ function_name | Show a stored procedure or function code |
| \du | List all users |
Managing Roles
| Command | Effect |
|---|---|
| CREATE ROLE role_name; | Create a new role |
| CREATE ROLE username NOINHERIT LOGIN PASSWORD password; | Create a new role with a username and password |
| SET ROLE new_role; | Change the role for the current session |
| GRANT role_2 TO role_1; | Allow role_1 to set its role as role_2 |
Managing Databases
| Command | Description |
|---|---|
| CREATE DATABASE [IF NOT EXISTS] db_name; | Create a new database |
| DROP DATABASE [IF EXISTS] db_name; | Delete a database |
Managing Tables
| Command | Description |
|---|---|
| CREATE [TEMP] TABLE [IF NOT EXISTS] table_name (…); | Create a new table or a temporary table |
| ALTER TABLE table_name ADD COLUMN new_column_name TYPE; | Add a new column to a table |
| ALTER TABLE table_name DROP COLUMN column_name; | Drop a column in a table |
| ALTER TABLE table_name RENAME column_name TO new_column_name; | Rename a column |
| ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]; | Set or remove a default value for a column |
| ALTER TABLE table_name ADD PRIMARY KEY (column,…); | Add a primary key to a table |
| ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint_name; | Remove the primary key from a table |
| ALTER TABLE table_name RENAME TO new_table_name; | Rename a table |
| DROP TABLE [IF EXISTS] table_name CASCADE; | Drop a table and its dependent objects |
Managing Views
| Command | Description |
|---|---|
| CREATE OR REPLACE VIEW view_name AS query; | Create a view |
| CREATE RECURSIVE VIEW view_name(column_list) AS SELECT column_list; | Create a recursive view |
| CREATE MATERIALIZED VIEW view_name AS query WITH [NO] DATA; | Create a materialized view |
| REFRESH MATERIALIZED VIEW CONCURRENTLY view_name; | Refresh a materialized view |
| DROP VIEW [IF EXISTS] view_name; | Drop a view |
| DROP MATERIALIZED VIEW view_name; | Drop a materialized view |
| ALTER VIEW view_name RENAME TO new_name; | Rename a view |
Managing Indexes
| Command | Description |
|---|---|
| CREATE [UNIQUE] INDEX index_name ON table (column,…); | Create an index |
| DROP INDEX index_name; | Remove a specified index from a table |
Querying Data
| Command | Description |
|---|---|
| SELECT * FROM table_name; | Query all data from a table |
| SELECT column_list FROM table; | Query data from specified columns of all rows in a table |
| SELECT DISTINCT (column) FROM table; | Query data and select unique rows |
| SELECT * FROM table WHERE condition; | Query data from a table with a filter |
| SELECT column_1 AS new_column_1, … FROM table; | Assign an alias to a column in the result set |
| SELECT * FROM table_name WHERE column LIKE ‘%value%’; | Query data using the LIKE operator |
| SELECT * FROM table_name WHERE column BETWEEN low AND high; | Query data using the BETWEEN operator |
| SELECT * FROM table_name WHERE column IN (value1, value2,…); | Query data using the IN operator |
| SELECT * FROM table_name LIMIT limit OFFSET offset ORDER BY column_name; | Constrain the returned rows with the LIMIT clause |
Joining Tables
| Command | Description |
|---|---|
| SELECT * FROM table1 INNER JOIN table2 ON conditions; | Perform an inner join |
| SELECT * FROM table1 LEFT JOIN table2 ON conditions; | Perform a left join |
| SELECT * FROM table1 FULL OUTER JOIN table2 ON conditions; | Perform a full outer join |
| SELECT * FROM table1 CROSS JOIN table2; | Perform a cross join |
| SELECT * FROM table1 NATURAL JOIN table2; | Perform a natural join |
Set Operations
| Command | Description |
|---|---|
| SELECT * FROM table1 UNION SELECT * FROM table2; | Combine the result set of two or more queries with UNION |
| SELECT * FROM table1 EXCEPT SELECT * FROM table2; | Minus a result set using EXCEPT |
| SELECT * FROM table1 INTERSECT SELECT * FROM table2; | Get the intersection of the result sets of two queries |
Modifying Data
| Command | Description |
|---|---|
| INSERT INTO table(column1,column2,…) VALUES(value_1,value_2,…); | Insert a new row into a table |
| INSERT INTO table_name(column1,column2,…) VALUES(value_1,value_2,…), …; | Insert multiple rows into a table |
| UPDATE table_name SET column_1 = value_1, …; | Update data for all rows |
| UPDATE table SET column_1 = value_1, … WHERE condition; | Update data for a set of rows specified by a condition in the WHERE clause |
| DELETE FROM table_name; | Delete all rows of a table |
| DELETE FROM table_name WHERE condition; | Delete specific rows based on a condition |
Performance
| Command | Description |
|---|---|
| EXPLAIN query; | Show the query plan for a query |
| EXPLAIN ANALYZE query; | Show and execute the query plan for a query |
| ANALYZE table_name; | Collect statistics |