PostgreSQL commands

╰+攻爆jí腚メ 2023-10-18 21:27 6阅读 0赞

### PostgreSQL commands ###

Access the PostgreSQL server from **psql** with a specific user:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>psql -U [username];</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>psql -U postgres</p> </td> 
  </tr> 
 </tbody> 
</table>

Connect to a specific database:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>\c database_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>\c dvdrental;</p> <p>You are now connected to database "dvdrental" as user "postgres".</p> </td> 
  </tr> 
 </tbody> 
</table>

To quit the psql:

[List all databases][] in the PostgreSQL database server

List all schemas:

List all [stored procedures][] and functions:

List all [views][]:

[Lists all tables][] in a current database.

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

Get detailed information on a table.

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>\d+ table_name</p> </td> 
  </tr> 
 </tbody> 
</table>

Show a [stored procedure][stored procedures] or function code:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>\df+ function_name</p> </td> 
  </tr> 
 </tbody> 
</table>

Show query output in the pretty-format:

List all users:

Create a new [role][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>CREATE ROLE role_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Create a new role with a `username` and `password`:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>CREATE ROLE username NOINHERIT LOGIN PASSWORD password;</p> </td> 
  </tr> 
 </tbody> 
</table>

Change role for the current session to the `new_role`:

Allow `role_1` to set its role as `role_2:`

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>GRANT role_2 TO role_1;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Managing databases ###

[Create a new database][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>CREATE DATABASE [IF NOT EXISTS] db_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Delete a database permanently][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>DROP DATABASE [IF EXISTS] db_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Managing tables ###

[Create a new table][] or a [temporary table][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> </td> 
   <td> <p>CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(</p> <p>&nbsp;&nbsp; pk SERIAL PRIMARY KEY,</p> <p>&nbsp;&nbsp; c1 type(size) NOT NULL,</p> <p>&nbsp;&nbsp; c2 type(size) NULL,</p> <p>&nbsp;&nbsp; ...</p> <p>);</p> </td> 
  </tr> 
 </tbody> 
</table>

[Add a new column][] to a table:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER TABLE table_name ADD COLUMN new_column_name TYPE;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Drop a column][] in a table:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER TABLE table_name DROP COLUMN column_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Rename a column][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER TABLE table_name RENAME column_name TO new_column_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Set or remove a default value for a column:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]</p> </td> 
  </tr> 
 </tbody> 
</table>

Add a [primary key ][primary key]to a table.

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER TABLE table_name ADD PRIMARY KEY (column,...);</p> </td> 
  </tr> 
 </tbody> 
</table>

Remove the primary key from a table.

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>ALTER TABLE table_name</p> <p>DROP CONSTRAINT primary_key_constraint_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Rename a table][].

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER TABLE table_name RENAME TO new_table_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Drop a table][] and its dependent objects:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>DROP TABLE [IF EXISTS] table_name CASCADE;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Managing views ###

[Create a view][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>CREATE OR REPLACE view_name AS</p> <p>query;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Create a recursive view][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>CREATE RECURSIVE VIEW view_name(columns) AS</p> <p>SELECT columns;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Create a materialized view][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p>CREATE MATERIALIZED VIEW view_name</p> <p>AS</p> <p>query</p> <p>WITH [NO] DATA;</p> </td> 
  </tr> 
 </tbody> 
</table>

Refresh a materialized view:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Drop a view:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>DROP VIEW [ IF EXISTS ] view_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Drop a materialized view:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>DROP MATERIALIZED VIEW view_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Rename a view:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ALTER VIEW view_name RENAME TO new_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Managing indexes ###

Creating an index with the specified name on a table

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>CREATE [UNIQUE] INDEX index_name</p> <p>ON table (column,...)</p> </td> 
  </tr> 
 </tbody> 
</table>

Removing a specified index from a table

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>DROP INDEX index_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Querying data from tables ###

Query all data from a table:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>SELECT * FROM table_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT column, column2….</p> <p>FROM table;</p> </td> 
  </tr> 
 </tbody> 
</table>

Query data and select only unique rows:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT DISTINCT (column)</p> <p>FROM table;</p> </td> 
  </tr> 
 </tbody> 
</table>

Query data from a table with a filter:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table</p> <p>WHERE condition;</p> </td> 
  </tr> 
 </tbody> 
</table>

Assign an [alias][] to a column in the result set:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT column_1 AS new_column_1, ...</p> <p>FROM table;</p> </td> 
  </tr> 
 </tbody> 
</table>

Query data using the [`LIKE`][LIKE] operator:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT * FROM table_name</p> <p>WHERE column LIKE '%value%'</p> </td> 
  </tr> 
 </tbody> 
</table>

Query data using the [BETWEEN][] operator:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT * FROM table_name</p> <p>WHERE column BETWEEN low AND high;</p> </td> 
  </tr> 
 </tbody> 
</table>

Query data using the [IN][] operator:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT * FROM table_name</p> <p>WHERE column IN (value1, value2,...);</p> </td> 
  </tr> 
 </tbody> 
</table>

Constrain the returned rows with the [`LIMIT`][LIMIT] clause:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT * FROM table_name</p> <p>LIMIT limit OFFSET offset</p> <p>ORDER BY column_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table1</p> <p>INNER JOIN table2 ON conditions</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table1</p> <p>LEFT JOIN table2 ON conditions</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table1</p> <p>FULL OUTER JOIN table2 ON conditions</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table1</p> <p>CROSS JOIN table2;</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table1</p> <p>NATURAL JOIN table2;</p> </td> 
  </tr> 
 </tbody> 
</table>

Return the number of rows of a table.

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>SELECT COUNT (*)</p> <p>FROM table_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Sort rows in ascending or descending order

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT column, column2, ...</p> <p>FROM table</p> <p>ORDER BY column ASC [DESC], column2 ASC [DESC],...;</p> </td> 
  </tr> 
 </tbody> 
</table>

Group rows using [`GROUP BY`][GROUP BY] clause.

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table</p> <p>GROUP BY column_1, column_2, ...;</p> </td> 
  </tr> 
 </tbody> 
</table>

Filter groups using the [`HAVING`][HAVING] clause.

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p>SELECT *</p> <p>FROM table</p> <p>GROUP BY column_1</p> <p>HAVING condition;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Set operations ###

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT * FROM table1</p> <p>UNION</p> <p>SELECT * FROM table2;</p> </td> 
  </tr> 
 </tbody> 
</table>

Minus a result set using [`EXCEPT`][EXCEPT] operator:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT * FROM table1</p> <p>EXCEPT</p> <p>SELECT * FROM table2;</p> </td> 
  </tr> 
 </tbody> 
</table>

Get intersection of the result sets of two queries:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>SELECT * FROM table1</p> <p>INTERSECT</p> <p>SELECT * FROM table2;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Modifying data ###

[Insert a new row into a table][]:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>INSERT INTO table(column1,column2,...)</p> <p>VALUES(value_1,value_2,...);</p> </td> 
  </tr> 
 </tbody> 
</table>

Insert multiple rows into a table:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p>INSERT INTO table_name(column1,column2,...)</p> <p>VALUES(value_1,value_2,...),</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(value_1,value_2,...),</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(value_1,value_2,...)...</p> </td> 
  </tr> 
 </tbody> 
</table>

[Update][] data for all rows:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p>UPDATE table_name</p> <p>SET column_1 = value_1,</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;...;</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p>UPDATE table</p> <p>SET column_1 = value_1,</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;...</p> <p>WHERE condition;</p> </td> 
  </tr> 
 </tbody> 
</table>

[Delete all rows][] of a table:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>DELETE FROM table_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

Delete specific rows based on a condition:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p>DELETE FROM table_name</p> <p>WHERE condition;</p> </td> 
  </tr> 
 </tbody> 
</table>

### Performance ###

Show the query plan for a query:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>EXPLAIN query;</p> </td> 
  </tr> 
 </tbody> 
</table>

Show and execute the query plan for a query:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>EXPLAIN ANALYZE query;</p> </td> 
  </tr> 
 </tbody> 
</table>

Collect statistics:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ANALYZE table_name;</p> </td> 
  </tr> 
 </tbody> 
</table>

[List all databases]: http://www.postgresqltutorial.com/postgresql-show-databases/
[stored procedures]: http://www.postgresqltutorial.com/postgresql-stored-procedures/
[views]: http://www.postgresqltutorial.com/postgresql-views/
[Lists all tables]: http://www.postgresqltutorial.com/postgresql-show-tables/
[role]: http://www.postgresqltutorial.com/postgresql-roles/
[Create a new database]: http://www.postgresqltutorial.com/postgresql-create-database/
[Delete a database permanently]: http://www.postgresqltutorial.com/postgresql-drop-database/
[Create a new table]: http://www.postgresqltutorial.com/postgresql-create-table/
[temporary table]: http://www.postgresqltutorial.com/postgresql-temporary-table/
[Add a new column]: http://www.postgresqltutorial.com/postgresql-add-column/
[Drop a column]: http://www.postgresqltutorial.com/postgresql-drop-column/
[Rename a column]: http://www.postgresqltutorial.com/postgresql-rename-column/
[primary key]: http://www.postgresqltutorial.com/postgresql-primary-key/
[Rename a table]: http://www.postgresqltutorial.com/postgresql-rename-table/
[Drop a table]: http://www.postgresqltutorial.com/postgresql-drop-table/
[Create a view]: http://www.postgresqltutorial.com/managing-postgresql-views/
[Create a recursive view]: http://www.postgresqltutorial.com/postgresql-recursive-view/
[Create a materialized view]: http://www.postgresqltutorial.com/postgresql-materialized-views/
[alias]: http://www.postgresqltutorial.com/postgresql-alias/
[LIKE]: http://www.postgresqltutorial.com/postgresql-like/
[BETWEEN]: http://www.postgresqltutorial.com/postgresql-between/
[IN]: http://www.postgresqltutorial.com/postgresql-in/
[LIMIT]: http://www.postgresqltutorial.com/postgresql-limit/
[inner join]: http://www.postgresqltutorial.com/postgresql-inner-join/
[left join]: http://www.postgresqltutorial.com/postgresql-left-join/
[full outer join]: http://www.postgresqltutorial.com/postgresql-full-outer-join/
[cross join]: http://www.postgresqltutorial.com/postgresql-cross-join/
[natural join]: http://www.postgresqltutorial.com/postgresql-natural-join/
[GROUP BY]: http://www.postgresqltutorial.com/postgresql-group-by/
[HAVING]: http://www.postgresqltutorial.com/postgresql-having/
[UNION]: http://www.postgresqltutorial.com/postgresql-union/
[EXCEPT]: http://www.postgresqltutorial.com/postgresql-tutorial/postgresql-except/
[Insert a new row into a table]: http://www.postgresqltutorial.com/postgresql-insert/
[Update]: http://www.postgresqltutorial.com/postgresql-update/
[Delete all rows]: http://www.postgresqltutorial.com/postgresql-delete/