PostgreSQL Commands Cheat Sheet

Author:

Introduction

PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its robustness, extensibility, and compliance with SQL standards. Mastering PostgreSQL commands is essential for database administration, querying, and management. This cheat sheet provides a quick reference to some of the most commonly used PostgreSQL commands.

PostgreSQL Commands Cheat Sheet

Here's a handy cheat sheet of the most commonly used PostgreSQL commands:

Command Description
\l Lists all databases on the server.
CREATE DATABASE db_name; Creates a new database.
\c db_name Connects to the specified database.
DROP DATABASE db_name; Deletes a database and all of its tables.
\dt Lists all tables in the current database.
CREATE TABLE tbl_name (column_definitions); Creates a new table.
DROP TABLE tbl_name; Deletes a table from the database.
\d tbl_name Displays the structure of a table.
ALTER TABLE tbl_name ADD column_name column_definition; Adds a new column to a table.
ALTER TABLE tbl_name DROP COLUMN column_name; Deletes a column from a table.
SELECT * FROM tbl_name; Selects all records from a table.
SELECT column1, column2 FROM tbl_name WHERE condition; Selects specific columns and records that meet a condition.
INSERT INTO tbl_name (column1, column2) VALUES (value1, value2); Inserts new data into a table.
UPDATE tbl_name SET column1 = value1, column2 = value2 WHERE condition; Updates existing data in a table.
DELETE FROM tbl_name WHERE condition; Deletes records from a table that meet a condition.
CREATE INDEX index_name ON tbl_name (column_name); Creates an index on a table.
DROP INDEX index_name; Deletes an index from a table.
CREATE USER username WITH PASSWORD 'password'; Creates a new user.
GRANT ALL PRIVILEGES ON DATABASE db_name TO username; Grants all privileges to a user on a database.
REVOKE ALL PRIVILEGES ON DATABASE db_name FROM username; Revokes all privileges from a user on a database.
\du Lists all roles and users.
DROP USER username; Deletes a user.
\conninfo Shows the current connection information.
SELECT version(); Displays the PostgreSQL version.
SHOW ALL; Displays all server configuration parameters.
EXPLAIN SELECT * FROM tbl_name; Shows how PostgreSQL executes a SELECT statement.
VACUUM; Cleans up the database and recovers space.
ANALYZE; Collects statistics about the contents of tables in the database.
BEGIN; Starts a new transaction.
COMMIT; Commits the current transaction.
ROLLBACK; Rolls back the current transaction.
\q Exits the PostgreSQL command-line tool.

Explanation of Key Commands with Examples

\l

Lists all databases on the server.

\l

Output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 myDatabase| postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

This command lists all the databases present on the PostgreSQL server.

CREATE DATABASE db_name;

Creates a new database.

CREATE DATABASE myDatabase;

Output:

CREATE DATABASE

This command creates a new database named myDatabase.

\c db_name

Connects to the specified database.

\c myDatabase

Output:

You are now connected to database "myDatabase" as user "postgres".

This command switches the current database context to myDatabase.

DROP DATABASE db_name;

Deletes a database and all of its tables.

DROP DATABASE myDatabase;

Output:

DROP DATABASE

This command deletes the database myDatabase along with all its tables and data.

\dt

Lists all tables in the current database.

\dt

Output:

                List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | employees     | table | postgres

This command lists all the tables present in the currently connected database.

CREATE TABLE tbl_name (column_definitions);

Creates a new table.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary NUMERIC(10, 2)
);

Output:

CREATE TABLE

This command creates a new table named employees with columns id, name, position, and salary.

DROP TABLE tbl_name;

Deletes a table from the database.

DROP TABLE employees;

Output:

DROP TABLE

This command deletes the employees table from the database.

\d tbl_name

Displays the structure of a table.

\d employees

Output:

Table "public.employees"
 Column  |         Type          | Collation | Nullable |              Default
---------+-----------------------+-----------+----------+------------------------------------
 id      | integer               |           | not null | nextval('employees_id_seq'::regclass)
 name    | character varying(100)|           |          |
 position| character varying(100)|           |          |
 salary  | numeric(10,2)         |           |          |
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)

This command shows the structure of the employees table, including column names, types, and constraints.

ALTER TABLE tbl_name ADD column_name column_definition;

Adds a new column to a table.

ALTER TABLE employees ADD birthdate DATE;

Output:

ALTER TABLE

This command adds a new column birthdate of type DATE to the employees table.

ALTER TABLE tbl_name DROP COLUMN column_name;

Deletes a column from a table.

ALTER TABLE employees DROP COLUMN birthdate;

Output:

ALTER TABLE

This command removes the birthdate column from the employees table.

SELECT * FROM tbl_name;

Selects all records from a table.

SELECT * FROM employees;

Output:

 id |   name    | position |  salary
----+-----------+----------+----------
  1 | John Doe  | Manager  | 75000.00
(1 row)

This command retrieves all records from the employees table.

SELECT column1, column2 FROM tbl_name WHERE condition;

Selects specific columns and records that meet a condition.

SELECT name, position FROM employees WHERE salary > 50000;

Output:

   name    | position
-----------+----------
 John Doe  | Manager
(1 row)

This command retrieves the name and position of employees whose salary is greater than 50,000.

INSERT INTO tbl_name (column1, column2) VALUES (value1, value2);

Inserts new data into a table.

INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Manager', 75000);

Output:

INSERT 0 1

This command inserts a new record into the employees table with the specified values.

UPDATE tbl_name SET column1 = value1, column2 = value2 WHERE condition;

Updates existing data in a

table.

UPDATE employees SET salary = 80000 WHERE name = 'John Doe';

Output:

UPDATE 1

This command updates the salary of John Doe to 80,000 in the employees table.

DELETE FROM tbl_name WHERE condition;

Deletes records from a table that meet a condition.

DELETE FROM employees WHERE name = 'John Doe';

Output:

DELETE 1

This command deletes the record of John Doe from the employees table.

CREATE INDEX index_name ON tbl_name (column_name);

Creates an index on a table.

CREATE INDEX idx_salary ON employees (salary);

Output:

CREATE INDEX

This command creates an index named idx_salary on the salary column of the employees table.

DROP INDEX index_name;

Deletes an index from a table.

DROP INDEX idx_salary;

Output:

DROP INDEX

This command deletes the index idx_salary from the employees table.

CREATE USER username WITH PASSWORD 'password';

Creates a new user.

CREATE USER newuser WITH PASSWORD 'password';

Output:

CREATE ROLE

This command creates a new user newuser with the specified password.

GRANT ALL PRIVILEGES ON DATABASE db_name TO username;

Grants all privileges to a user on a database.

GRANT ALL PRIVILEGES ON DATABASE myDatabase TO newuser;

Output:

GRANT

This command grants all privileges on myDatabase to the user newuser.

REVOKE ALL PRIVILEGES ON DATABASE db_name FROM username;

Revokes all privileges from a user on a database.

REVOKE ALL PRIVILEGES ON DATABASE myDatabase FROM newuser;

Output:

REVOKE

This command revokes all privileges on myDatabase from the user newuser.

\du

Lists all roles and users.

\du

Output:

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 newuser   |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

This command lists all roles and users present in the PostgreSQL server.

DROP USER username;

Deletes a user.

DROP USER newuser;

Output:

DROP ROLE

This command deletes the user newuser.

\conninfo

Shows the current connection information.

\conninfo

Output:

You are connected to database "myDatabase" as user "postgres" on host "localhost" at port "5432".

This command shows information about the current database connection.

SELECT version();

Displays the PostgreSQL version.

SELECT version();

Output:

                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

This command retrieves the version information of the PostgreSQL server.

SHOW ALL;

Displays all server configuration parameters.

SHOW ALL;

Output:

               name               |                                            setting                                            |                                        description
----------------------------------+-----------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------
 allow_system_table_mods          | off                                                                                           | Allows modifications of the structure of system tables.
 application_name                 | psql                                                                                          | Sets the application name to be reported in statistics and logs.
 archive_command                  | (disabled)                                                                                     | Sets the shell command that will be called to archive a WAL file.
 archive_mode                     | off                                                                                           | Allows archiving of WAL files using archive_command.
(10 rows)

This command displays the current values of all server configuration parameters.

EXPLAIN SELECT * FROM tbl_name;

Shows how PostgreSQL executes a SELECT statement.

EXPLAIN SELECT * FROM employees;

Output:

                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on employees  (cost=0.00..12.00 rows=200 width=32)
(1 row)

This command provides information on how PostgreSQL executes the specified SELECT statement.

VACUUM;

Cleans up the database and recovers space.

VACUUM;

Output:

VACUUM

This command cleans up the database by reclaiming storage occupied by dead tuples.

ANALYZE;

Collects statistics about the contents of tables in the database.

ANALYZE;

Output:

ANALYZE

This command collects statistics about the contents of tables, which helps the query planner to make efficient decisions.

BEGIN;

Starts a new transaction.

BEGIN;

Output:

BEGIN

This command starts a new transaction block.

COMMIT;

Commits the current transaction.

COMMIT;

Output:

COMMIT

This command commits the current transaction, making all changes made in the transaction permanent.

ROLLBACK;

Rolls back the current transaction.

ROLLBACK;

Output:

ROLLBACK

This command rolls back the current transaction, undoing all changes made in the transaction.

\q

Exits the PostgreSQL command-line tool.

\q

Output:

(Note: This command simply exits the psql shell without any output.)

This command exits the PostgreSQL interactive terminal.

Conclusion

Mastering PostgreSQL commands is essential for efficiently managing databases and performing administrative tasks. This cheat sheet provides a quick reference to some of the most commonly used commands, helping you navigate and operate your PostgreSQL databases more effectively. Keep this guide handy to make the most of PostgreSQL. Happy coding!

By understanding and using these commands, you can simplify your database management processes, enhance your efficiency, and ensure your databases are well-maintained.

Comments

Post a Comment

Leave Comment