PostgreSQL commands

PostgreSQL commands

Access the PostgreSQL server from psql with a specific user:

1	psql -U [username];

For example, the following command uses the postgres user to access the PostgreSQL database server:

1	psql -U postgres

Connect to a specific database:

1	\c database_name;

For example, the following command connects to the dvdrental database:

1 2	\c dvdrental; You are now connected to database "dvdrental" as user "postgres".

To quit the psql:

1

\q

List all databases in the PostgreSQL database server

1

\l

List all schemas:

1

\dn

List all stored procedures and functions:

1

\df

List all views:

1

\dv

Lists all tables in a current database.

1

\dt

Or to get more information on tables in the current database:

1

\dt+

Get detailed information on a table.

1	\d+ table_name

Show a stored procedure or function code:

1	\df+ function_name

Show query output in the pretty-format:

1

\x

List all users:

1

\du

Create a new role:

1	CREATE ROLE role_name;

Create a new role with a username and password:

1	CREATE ROLE username NOINHERIT LOGIN PASSWORD password;

Change role for the current session to the new_role:

1	SET ROLE new_role;

Allow role_1 to set its role as role_2:

1	GRANT role_2 TO role_1;

 

Managing databases

Create a new database:

1	CREATE DATABASE [IF NOT EXISTS] db_name;

Delete a database permanently:

1	DROP DATABASE [IF EXISTS] db_name;

 

Managing tables

Create a new table or a temporary table

1 2 3 4 5 6

CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(    pk SERIAL PRIMARY KEY,    c1 type(size) NOT NULL,    c2 type(size) NULL,    ... );

Add a new column to a table:

1	ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

Drop a column in a table:

1	ALTER TABLE table_name DROP COLUMN column_name;

Rename a column:

1	ALTER TABLE table_name RENAME column_name TO new_column_name;

Set or remove a default value for a column:

1	ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]

Add a primary key to a table.

1	ALTER TABLE table_name ADD PRIMARY KEY (column,...);

Remove the primary key from a table.

1 2	ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint_name;

Rename a table.

1	ALTER TABLE table_name RENAME TO new_table_name;

Drop a table and its dependent objects:

1	DROP TABLE [IF EXISTS] table_name CASCADE;

 

Managing views

Create a view:

1 2	CREATE OR REPLACE view_name AS query;

Create a recursive view:

1 2	CREATE RECURSIVE VIEW view_name(columns) AS SELECT columns;

Create a materialized view:

1 2 3 4

CREATE MATERIALIZED VIEW view_name AS query WITH [NO] DATA;

Refresh a materialized view:

1	REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Drop a view:

1	DROP VIEW [ IF EXISTS ] view_name;

Drop a materialized view:

1	DROP MATERIALIZED VIEW view_name;

Rename a view:

1	ALTER VIEW view_name RENAME TO new_name;

 

Managing indexes

Creating an index with the specified name on a table

1 2	CREATE [UNIQUE] INDEX index_name ON table (column,...)

Removing a specified index from a table

1	DROP INDEX index_name;

 

Querying data from tables

Query all data from a table:

1	SELECT * FROM table_name;

Query data from specified columns of all rows in a table:

1 2	SELECT column, column2…. FROM table;

Query data and select only unique rows:

1 2	SELECT DISTINCT (column) FROM table;

Query data from a table with a filter:

1 2 3

SELECT * FROM table WHERE condition;

Assign an alias to a column in the result set:

1 2	SELECT column_1 AS new_column_1, ... FROM table;

Query data using the LIKE operator:

1 2	SELECT * FROM table_name WHERE column LIKE '%value%'

Query data using the BETWEEN operator:

1 2	SELECT * FROM table_name WHERE column BETWEEN low AND high;

Query data using the IN operator:

1 2	SELECT * FROM table_name WHERE column IN (value1, value2,...);

Constrain the returned rows with the LIMIT clause:

1 2 3

SELECT * FROM table_name LIMIT limit OFFSET offset ORDER BY column_name;

Query data from multiple using the inner join, left join, full outer join, cross join and natural join:

1 2 3

SELECT * FROM table1 INNER JOIN table2 ON conditions

 

1 2 3

SELECT * FROM table1 LEFT JOIN table2 ON conditions

 

1 2 3

SELECT * FROM table1 FULL OUTER JOIN table2 ON conditions

 

1 2 3

SELECT * FROM table1 CROSS JOIN table2;

 

1 2 3

SELECT * FROM table1 NATURAL JOIN table2;

Return the number of rows of a table.

1 2	SELECT COUNT (*) FROM table_name;

Sort rows in ascending or descending order

1 2 3

SELECT column, column2, ... FROM table ORDER BY column ASC [DESC], column2 ASC [DESC],...;

Group rows using GROUP BY clause.

1 2 3

SELECT * FROM table GROUP BY column_1, column_2, ...;

Filter groups using the HAVING clause.

1 2 3 4

SELECT * FROM table GROUP BY column_1 HAVING condition;

 

Set operations

Combine the result set of two or more queries with UNION operator:

1 2 3

SELECT * FROM table1 UNION SELECT * FROM table2;

Minus a result set using EXCEPT operator:

1 2 3

SELECT * FROM table1 EXCEPT SELECT * FROM table2;

Get intersection of the result sets of two queries:

1 2 3

SELECT * FROM table1 INTERSECT SELECT * FROM table2;

 

Modifying data

Insert a new row into a table:

1 2	INSERT INTO table(column1,column2,...) VALUES(value_1,value_2,...);

Insert multiple rows into a table:

1 2 3 4

INSERT INTO table_name(column1,column2,...) VALUES(value_1,value_2,...),       (value_1,value_2,...),       (value_1,value_2,...)...

Update data for all rows:

1 2 3

UPDATE table_name SET column_1 = value_1,     ...;

Update data for a set of rows specified by a condition in the WHERE clause.

1 2 3 4

UPDATE table SET column_1 = value_1,     ... WHERE condition;

Delete all rows of a table:

1	DELETE FROM table_name;

Delete specific rows based on a condition:

1 2	DELETE FROM table_name WHERE condition;

 

Performance

Show the query plan for a query:

1	EXPLAIN query;

Show and execute the query plan for a query:

1	EXPLAIN ANALYZE query;

Collect statistics:

1	ANALYZE table_name;