2021-01-11 21:22:34 +08:00
|
|
|
---
|
|
|
|
|
title: PostgreSQL
|
2021-12-15 10:48:31 +08:00
|
|
|
background: bg-[#3d6488]
|
2021-01-11 21:22:34 +08:00
|
|
|
tags:
|
2024-05-22 02:08:50 -07:00
|
|
|
- DB
|
|
|
|
|
- RDBMS
|
2021-01-11 21:22:34 +08:00
|
|
|
categories:
|
|
|
|
|
- Database
|
|
|
|
|
date: 2021-01-11 14:19:24
|
|
|
|
|
intro: |
|
2024-05-22 02:08:50 -07:00
|
|
|
The [PostgreSQL](https://www.postgresql.org/docs/current/) cheat sheet provides you with the common PostgreSQL commands and statements.
|
2023-03-06 16:19:19 +08:00
|
|
|
plugins:
|
2024-05-22 02:08:50 -07:00
|
|
|
- copyCode
|
2021-01-11 21:22:34 +08:00
|
|
|
---
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
## Getting Started
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Getting started
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Switch and connect
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
$ sudo -u postgres psql
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
List all databases
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
postgres=# \l
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
Connect to the database named postgres
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
postgres=# \c postgres
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
Disconnect
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
postgres=# \q
|
|
|
|
|
postgres=# \!
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### psql commands {.col-span-2}
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
| Option | Example | Description |
|
2024-05-22 02:08:50 -07:00
|
|
|
| ------------------- | -------------------------------------------- | :----------------------------- |
|
2021-01-11 21:22:34 +08:00
|
|
|
| `[-d] <database>` | psql -d mydb | Connecting to database |
|
|
|
|
|
| `-U` | psql -U john mydb | Connecting as a specific user |
|
|
|
|
|
| `-h` `-p` | psql -h localhost -p 5432 mydb | Connecting to a host/port |
|
|
|
|
|
| `-U` `-h` `-p` `-d` | psql -U admin -h 192.168.1.5 -p 2506 -d mydb | Connect remote PostgreSQL |
|
|
|
|
|
| `-W` | psql -W mydb | Force password |
|
|
|
|
|
| `-c` | psql -c '\c postgres' -c '\dt' | Execute a SQL query or command |
|
|
|
|
|
| `-H` | psql -c "\l+" -H postgres > database.html | Generate HTML report |
|
|
|
|
|
| `-l` | psql -l | List all databases |
|
|
|
|
|
| `-f` | psql mydb -f file.sql | Execute commands from a file |
|
|
|
|
|
| `-V` | psql -V | Print the psql version |
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
{.show-header}
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Getting help
|
|
|
|
|
|
2021-09-14 13:03:55 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| ----------- | :----------------------------- |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\h` | Help on syntax of SQL commands |
|
|
|
|
|
| `\h` DELETE | DELETE SQL statement syntax |
|
|
|
|
|
| `\?` | List of PostgreSQL command |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
Run in PostgreSQL console
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
## PostgreSQL Working
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Recon
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show version
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```
|
|
|
|
|
SHOW SERVER_VERSION;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show system status
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\conninfo
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show environmental variables
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SHOW ALL;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
List users
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT rolname FROM pg_roles;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show current user
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT current_user;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show current user's permissions
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```
|
|
|
|
|
\du
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show current database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT current_database();
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show all tables in database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\dt
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
List functions
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\df <schema>
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Databases
|
|
|
|
|
|
|
|
|
|
List databases
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\l
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Connect to database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\c <database_name>
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Show current database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT current_database();
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Create database](http://www.postgresql.org/docs/current/static/sql-createdatabase.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```sql {.wrap}
|
|
|
|
|
CREATE DATABASE <database_name> WITH OWNER <username>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Drop database](http://www.postgresql.org/docs/current/static/sql-dropdatabase.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```sql {.wrap}
|
|
|
|
|
DROP DATABASE IF EXISTS <database_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Rename database](http://www.postgresql.org/docs/current/static/sql-alterdatabase.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```sql {.wrap}
|
|
|
|
|
ALTER DATABASE <old_name> RENAME TO <new_name>;
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Tables
|
|
|
|
|
|
|
|
|
|
List tables, in current db
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\dt
|
|
|
|
|
|
|
|
|
|
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
List tables, globally
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\dt *.*.
|
|
|
|
|
|
|
|
|
|
SELECT * FROM pg_catalog.pg_tables
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
List table schema
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\d <table_name>
|
|
|
|
|
\d+ <table_name>
|
|
|
|
|
|
|
|
|
|
SELECT column_name, data_type, character_maximum_length
|
|
|
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
|
|
|
WHERE table_name = '<table_name>';
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Create table](http://www.postgresql.org/docs/current/static/sql-createtable.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
CREATE TABLE <table_name>(
|
|
|
|
|
<column_name> <column_type>,
|
|
|
|
|
<column_name> <column_type>
|
|
|
|
|
);
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Create table, with an auto-incrementing primary key
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
CREATE TABLE <table_name> (
|
|
|
|
|
<column_name> SERIAL PRIMARY KEY
|
|
|
|
|
);
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Delete table](http://www.postgresql.org/docs/current/static/sql-droptable.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
DROP TABLE IF EXISTS <table_name> CASCADE;
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Permissions
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Become the postgres user, if you have permission errors
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell
|
|
|
|
|
sudo su - postgres
|
|
|
|
|
psql
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Grant](http://www.postgresql.org/docs/current/static/sql-grant.html) all permissions on database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Grant connection permissions on database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Grant permissions on schema
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT USAGE ON SCHEMA public TO <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Grant permissions to functions
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Grant permissions to select, update, insert, delete, on a all tables
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Grant permissions, on a table
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Grant permissions, to select, on a table
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Columns
|
|
|
|
|
|
|
|
|
|
[Add column](http://www.postgresql.org/docs/current/static/sql-altertable.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
ALTER TABLE <table_name> IF EXISTS
|
|
|
|
|
ADD <column_name> <data_type> [<constraints>];
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Update column
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
ALTER TABLE <table_name> IF EXISTS
|
|
|
|
|
ALTER <column_name> TYPE <data_type> [<constraints>];
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Delete column
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
ALTER TABLE <table_name> IF EXISTS
|
|
|
|
|
DROP <column_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Update column to be an auto-incrementing primary key
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
ALTER TABLE <table_name>
|
|
|
|
|
ADD COLUMN <column_name> SERIAL PRIMARY KEY;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Insert into a table, with an auto-incrementing primary key
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
INSERT INTO <table_name>
|
|
|
|
|
VALUES (DEFAULT, <value1>);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO <table_name> (<column1_name>,<column2_name>)
|
|
|
|
|
VALUES ( <value1>,<value2> );
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2023-03-07 23:27:26 +08:00
|
|
|
[Select](http://www.postgresql.org/docs/current/static/sql-select.html) all data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT * FROM <table_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Read one row of data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT * FROM <table_name> LIMIT 1;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Search for data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT * FROM <table_name> WHERE <column_name> = <value>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Insert](http://www.postgresql.org/docs/current/static/sql-insert.html) data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Update](http://www.postgresql.org/docs/current/static/sql-update.html) data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
UPDATE <table_name>
|
|
|
|
|
SET <column_1> = <value_1>, <column_2> = <value_2>
|
|
|
|
|
WHERE <column_1> = <value>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Delete](http://www.postgresql.org/docs/current/static/sql-delete.html) all data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
DELETE FROM <table_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Delete specific data
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
DELETE FROM <table_name>
|
|
|
|
|
WHERE <column_name> = <value>;
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Users
|
|
|
|
|
|
|
|
|
|
List roles
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
SELECT rolname FROM pg_roles;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Create user](http://www.postgresql.org/docs/current/static/sql-createuser.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```sql {.wrap}
|
|
|
|
|
CREATE USER <user_name> WITH PASSWORD '<password>';
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Drop user](http://www.postgresql.org/docs/current/static/sql-dropuser.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
DROP USER IF EXISTS <user_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Alter](http://www.postgresql.org/docs/current/static/sql-alterrole.html) user password
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```sql {.wrap}
|
|
|
|
|
ALTER ROLE <user_name> WITH PASSWORD '<password>';
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Schema
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
List schemas
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
\dn
|
|
|
|
|
|
|
|
|
|
SELECT schema_name FROM information_schema.schemata;
|
|
|
|
|
|
|
|
|
|
SELECT nspname FROM pg_catalog.pg_namespace;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Create schema](http://www.postgresql.org/docs/current/static/sql-createschema.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
CREATE SCHEMA IF NOT EXISTS <schema_name>;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
[Drop schema](http://www.postgresql.org/docs/current/static/sql-dropschema.html)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2021-01-11 21:22:34 +08:00
|
|
|
DROP SCHEMA IF EXISTS <schema_name> CASCADE;
|
|
|
|
|
```
|
|
|
|
|
|
2023-01-25 15:39:03 -03:00
|
|
|
### Dates
|
|
|
|
|
|
|
|
|
|
Show [current date](https://www.postgresql.org/docs/15/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) YYYY-MM-DD
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2023-01-25 15:39:03 -03:00
|
|
|
SELECT current_date;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
Calculate
|
|
|
|
|
[age](<https://www.postgresql.org/docs/15/functions-datetime.html#:~:text=age%20(%20timestamp%2C%20timestamp%20)%20%E2%86%92%20interval>)
|
|
|
|
|
between two dates
|
|
|
|
|
|
2023-01-25 15:39:03 -03:00
|
|
|
```sql {.wrap}
|
|
|
|
|
SELECT age(timestamp, timestamp);
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
Show [current time](https://www.postgresql.org/docs/15/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) with time
|
|
|
|
|
zone
|
|
|
|
|
|
|
|
|
|
```sql {.wrap}
|
2023-01-25 15:39:03 -03:00
|
|
|
SELECT current_time;
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
|
|
|
|
[Make](<https://www.postgresql.org/docs/15/functions-datetime.html#:~:text=make_date%20(%20year%20int%2C%20month%20int%2C%20day%20int%20)%20%E2%86%92%20date>)
|
|
|
|
|
dates using integers
|
|
|
|
|
|
2023-01-25 15:39:03 -03:00
|
|
|
```sql {.wrap}
|
|
|
|
|
SELECT make_date(2021,03,25);
|
|
|
|
|
```
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
## PostgreSQL Commands
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Tables
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| ---------------- | :------------------------------ |
|
2021-01-11 21:22:34 +08:00
|
|
|
| `\d <table>` | Describe table |
|
|
|
|
|
| `\d+ <table>` | Describe table with details |
|
|
|
|
|
| `\dt` | List tables from current schema |
|
|
|
|
|
| `\dt *.*` | List tables from all schemas |
|
|
|
|
|
| `\dt <schema>.*` | List tables for a schema |
|
|
|
|
|
| `\dp` | List table access privileges |
|
|
|
|
|
| `\det[+]` | List foreign tables |
|
|
|
|
|
|
|
|
|
|
### Query buffer
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-09-14 13:03:55 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| ------------ | :--------------------------------- |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\e [FILE]` | Edit the query buffer (or file) |
|
|
|
|
|
| `\ef [FUNC]` | Edit function definition |
|
|
|
|
|
| `\p` | Show the contents |
|
|
|
|
|
| `\r` | Reset (clear) the query buffer |
|
|
|
|
|
| `\s [FILE]` | Display history or save it to file |
|
|
|
|
|
| `\w FILE` | Write query buffer to file |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Informational {.row-span-4}
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-09-14 13:03:55 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| --------------- | :------------------------------ |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\l[+]` | List all databases |
|
|
|
|
|
| `\dn[S+]` | List schemas |
|
|
|
|
|
| `\di[S+]` | List indexes |
|
|
|
|
|
| `\du[+]` | List roles |
|
|
|
|
|
| `\ds[S+]` | List sequences |
|
|
|
|
|
| `\df[antw][S+]` | List functions |
|
|
|
|
|
| `\deu[+]` | List user mappings |
|
|
|
|
|
| `\dv[S+]` | List views |
|
|
|
|
|
| `\dl` | List large objects |
|
|
|
|
|
| `\dT[S+]` | List data types |
|
|
|
|
|
| `\da[S]` | List aggregates |
|
|
|
|
|
| `\db[+]` | List tablespaces |
|
|
|
|
|
| `\dc[S+]` | List conversions |
|
|
|
|
|
| `\dC[+]` | List casts |
|
|
|
|
|
| `\ddp` | List default privileges |
|
|
|
|
|
| `\dd[S]` | Show object descriptions |
|
|
|
|
|
| `\dD[S+]` | List domains |
|
|
|
|
|
| `\des[+]` | List foreign servers |
|
|
|
|
|
| `\dew[+]` | List foreign-data wrappers |
|
|
|
|
|
| `\dF[+]` | List text search configurations |
|
|
|
|
|
| `\dFd[+]` | List text search dictionaries |
|
|
|
|
|
| `\dFp[+]` | List text search parsers |
|
|
|
|
|
| `\dFt[+]` | List text search templates |
|
|
|
|
|
| `\dL[S+]` | List procedural languages |
|
|
|
|
|
| `\do[S]` | List operators |
|
|
|
|
|
| `\dO[S+]` | List collations |
|
|
|
|
|
| `\drds` | List per-database role settings |
|
|
|
|
|
| `\dx[+]` | List extensions |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
`S`: show system objects, `+`: additional detail
|
|
|
|
|
|
|
|
|
|
### Connection
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-09-14 13:03:55 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| ---------------------- | :-------------------------- |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\c [DBNAME]` | Connect to new database |
|
|
|
|
|
| `\encoding [ENCODING]` | Show or set client encoding |
|
|
|
|
|
| `\password [USER]` | Change the password |
|
|
|
|
|
| `\conninfo` | Display information |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Formatting
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
| - | - |
|
|
|
|
|
| ------------------------- | :----------------------------------------- |
|
|
|
|
|
| `\a` | Toggle between unaligned and aligned |
|
|
|
|
|
| `\C [STRING]` | Set table title, or unset if none |
|
|
|
|
|
| `\f [STRING]` | Show or set field separator for unaligned |
|
|
|
|
|
| `\H` | Toggle HTML output mode |
|
|
|
|
|
| <code>\t [on\|off]</code> | Show only rows |
|
|
|
|
|
| `\T [STRING]` | Set or unset HTML \<table\> tag attributes |
|
|
|
|
|
| <code>\x [on\|off]</code> | Toggle expanded output |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Input/Output
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| ----------------- | :------------------------------------------------------------- |
|
2021-01-11 21:22:34 +08:00
|
|
|
| `\copy ...` | Import/export table<br> _See also:_ [copy](#import-export-csv) |
|
|
|
|
|
| `\echo [STRING]` | Print string |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\i FILE` | Execute file |
|
2021-01-11 21:22:34 +08:00
|
|
|
| `\o [FILE]` | Export all results to file |
|
|
|
|
|
| `\qecho [STRING]` | String to output stream |
|
|
|
|
|
|
|
|
|
|
### Variables
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-09-14 13:03:55 +08:00
|
|
|
| - | - |
|
2024-05-22 02:08:50 -07:00
|
|
|
| --------------------- | :-------------------------------------------- |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\prompt [TEXT] NAME` | Set variable |
|
2021-01-11 21:22:34 +08:00
|
|
|
| `\set [NAME [VALUE]]` | Set variable _(or list all if no parameters)_ |
|
2021-09-14 13:03:55 +08:00
|
|
|
| `\unset NAME` | Delete variable |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Misc
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
| - | - |
|
|
|
|
|
| ------------------------------ | :------------------- |
|
|
|
|
|
| `\cd [DIR]` | Change the directory |
|
|
|
|
|
| <code>\timing [on\|off]</code> | Toggle timing |
|
|
|
|
|
| `\! [COMMAND]` | Execute in shell |
|
|
|
|
|
| `\! ls -l` | List all in shell |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Large Objects
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
- `\lo_export LOBOID FILE`
|
|
|
|
|
- `\lo_import FILE [COMMENT]`
|
|
|
|
|
- `\lo_list`
|
|
|
|
|
- `\lo_unlink LOBOID`
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
{.marker-none}
|
2021-01-11 21:22:34 +08:00
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
## Miscellaneous
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
### Backup
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Use pg_dumpall to backup all databases
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
$ pg_dumpall -U postgres > all.sql
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Use pg_dump to backup a database
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
$ pg_dump -d mydb -f mydb_backup.sql
|
|
|
|
|
```
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
| - | - |
|
|
|
|
|
| ---- | :--------------------------------------------- |
|
|
|
|
|
| `-a` | Dump only the data, not the schema |
|
|
|
|
|
| `-s` | Dump only the schema, no data |
|
|
|
|
|
| `-c` | Drop database before recreating |
|
|
|
|
|
| `-C` | Create database before restoring |
|
|
|
|
|
| `-t` | Dump the named table(s) only |
|
|
|
|
|
| `-F` | Format (`c`: custom, `d`: directory, `t`: tar) |
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
Use `pg_dump -?` to get the full list of options
|
|
|
|
|
|
|
|
|
|
### Restore
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Restore a database with psql
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
$ psql -U user mydb < mydb_backup.sql
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Restore a database with pg_restore
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
$ pg_restore -d mydb mydb_backup.sql -c
|
|
|
|
|
```
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
| - | - |
|
|
|
|
|
| ---- | :--------------------------------------------------------------------------- |
|
|
|
|
|
| `-U` | Specify a database user |
|
|
|
|
|
| `-c` | Drop database before recreating |
|
|
|
|
|
| `-C` | Create database before restoring |
|
|
|
|
|
| `-e` | Exit if an error has encountered |
|
|
|
|
|
| `-F` | Format (`c`: custom, `d`: directory, `t`: tar, `p`: plain text sql(default)) |
|
|
|
|
|
|
2022-11-20 14:18:58 +08:00
|
|
|
{.marker-none}
|
2021-01-11 21:22:34 +08:00
|
|
|
|
|
|
|
|
Use `pg_restore -?` to get the full list of options
|
|
|
|
|
|
|
|
|
|
### Remote access
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Get location of postgresql.conf
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
$ psql -U postgres -c 'SHOW config_file'
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Append to postgresql.conf
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
listen_addresses = '*'
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Append to pg_hba.conf (Same location as postgresql.conf)
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
host all all 0.0.0.0/0 md5
|
|
|
|
|
host all all ::/0 md5
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Restart PostgreSQL server
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
2022-05-21 16:57:32 -04:00
|
|
|
$ sudo systemctl restart postgresql
|
2021-01-11 21:22:34 +08:00
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### Import/Export CSV
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Export table into CSV file
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
\copy table TO '<path>' CSV
|
|
|
|
|
\copy table(col1,col1) TO '<path>' CSV
|
|
|
|
|
\copy (SELECT...) TO '<path>' CSV
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
Import CSV file into table
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
```shell script
|
|
|
|
|
\copy table FROM '<path>' CSV
|
|
|
|
|
\copy table(col1,col1) FROM '<path>' CSV
|
|
|
|
|
```
|
2024-05-22 02:08:50 -07:00
|
|
|
|
2021-01-11 21:22:34 +08:00
|
|
|
See also: [Copy](https://www.postgresql.org/docs/current/sql-copy.html)
|
|
|
|
|
|
2024-05-22 02:08:50 -07:00
|
|
|
## Also see
|
|
|
|
|
|
2022-05-21 16:57:32 -04:00
|
|
|
- [Posgres-cheatsheet](https://gist.github.com/apolloclark/ea5466d5929e63043dcf#posgres-cheatsheet) _(gist.github.com)_
|
