PostgreSQL Cheatsheet
If you need to connect to a remote server, Ubuntu 16.04 users can install the PostgreSQL client with:
sudo apt install postgresql-client-common postgresql-client-9.5
Logging In
Enter the CLI as Master/Root User
postgres
is the master user, (and comes with their own database by default).
sudo -u postgres psql
Enter the CLI Utility As Normal User
psql \
--user $USER \
--password \
-d $DATABASE \
--host $HOST
- If the user has their own database and you wish to connect to that, you can skip
-D $DATABASE
as you would automatically connect to that. - if you wish to connect to localhost, you can skip
--host
- If the user doesnt need a password, you can skip
--password
- If your current BASH user is the same name as the user in psql you wish to connect as, you can skip
--user
.
CLI Utility
Exiting the CLI Utility
\q
Ctrl
+ D
.
Switching Database
Technically this command does not "switch" database, you are just closing one connection and opening another.
\connect DBNAME
Quotation Marks
Double quotes ("
) are used to denote column names, and single quotes ('
) are also only used to denote values. When working with UUIDs, its best to always wrap in single quotes.
UPDATE my_table
SET "my_column" = 'f68b8ef1-ed46-42bd-9619-4ae37dae3eb3'
WHERE "uuid"='97c681fc-d217-4bcb-970d-e54793d8fd94';
when
.
User Management
CREATE USER programster WITH PASSWORD 'thisismypassword';
programster
database, and they will have the ability to see and create tables in the other databases.
Create User With New Role
CREATE ROLE $MY_USER with password '$USER_PASSWORD' login;
login
if you don't want that to be loginable.
Grant User Access To Database
GRANT ALL ON database $DATABASE_NAME TO $ROLE_OR_USER;
Check Role Privileges
\du
Change User Password
\password [user]
sudo -u postgres psql postgres
Databases
Create A Database
CREATE DATABASE my_database_name
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;
If you don't need utf8 and are fine with LATIN encoding, then you can just use:
CREATE DATABASE my_database_name;
Delete/Drop Database
DROP DATABASE my_database_name;
Tables
Create a table
CREATE TABLE broker (
id serial NOT NULL,
name varchar(255) NOT NULL,
redirect_url text NOT NULL,
secret varchar(30) NOT NULL,
modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)
AUTO_INCREMENT
but a special serial
and big_serial
type.
Creating Tables With Foreign Keys
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int
);
By default, foreign keys will be set to use NO ACTION
for both update and delete actions (resulting in the same behavioiur as RESTRICT
). You can manually specify what you want like so:
CREATE TABLE weather (
city varchar(80) references cities(city) ON UPDATE CASCADE ON DELETE RESTRICT,
temp_lo int
);
Creating Tables With Enums
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
Creating Tables With Unique Column
CREATE TABLE users (
name varchar(255) NOT NULL,
username varchar(255) UNIQUE NOT NULL
);
Creating Combined Unique Column
CREATE TABLE myTable (
id uuid PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
UNIQUE (col1, col2)
)
Creating Tables With Indexes
You cannot specify an INDEX
in the create table definition. However, if you specify a column as unique, then an index is automatically created for that column.
If you have a column that is not unique but also needs to be indexed for quick selections, then you can add an index after the table has been created with:
CREATE INDEX on tableName ("column_name");
Show Tables
If you are using the psql cli tool:
\dt
If you are connecting using something like PHP, then you need to do:
SELECT tablename FROM pg_catalog.pg_tables
Describe Table
If you want to show a table, use:
\d+ tablename
Example output:
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+------------------------+-----------+----------+---------+----------+--------------+-------------
name | character varying(255) | | not null | | extended | |
username | character varying(255) | | not null | | extended | |
Indexes:
"users_username_key" UNIQUE CONSTRAINT, btree (username)
... or you can use the following to get just the basic information:
\d tablename
Table "public.users"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
name | character varying(255) | | not null |
username | character varying(255) | | not null |
Indexes:
"users_username_key" UNIQUE CONSTRAINT, btree (username)
SHOW CREATE TABLE tablename
.
Show Databases
\list
Inserting Data
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
INSERT INTO cities (city, location)
VALUES
('Houston', '29.7604, -95.3698'),
('Dallas', '32.7767, -96.7970')
;
Updating Data
UPDATE cities
SET city='Washington'
WHERE city='Houston';
Deleting Data
DELETE FROM cities
WHERE city='Houston';
Add Column
ALTER TABLE my_table ADD COLUMN "new_column" varchar NOT NULL;
Drop/Remove Type (Enum)
If you want to remove a type (such as an Enum you created), then you would do it like so:
DROP TYPE my_type;
Rename Column
ALTER TABLE my_table
RENAME COLUMN "original_name"
TO "new_column_name";
Removing Constraint
If you want to remove a constraint, such as an index or a foreign key, then you would do it like so:
ALTER TABLE my_table DROP CONSTRAINT "my_constraint_name";
Working With JSON
If you use the JSON data type for a column, there are special operations you can do with it.
For example, I created a server_dump
column that holds the json_encode
of the PHP $_SERVER superglobal and can then select the requestor's IP address from this with:
SELECT server_dump->'REMOTE_ADDR' FROM api_requests;
Show Running Queries
SELECT * FROM pg_stat_activity;
Misc
Ouput of Nulls
Unlike MySQL which will show "null" as a value when showing data, like so:
mysql> select * from cities;
+---------+----------+
| city | location |
+---------+----------+
| Houston | NULL |
+---------+----------+
1 row in set (0.00 sec)
...PostgreSQL will just show emptiness like so:
postgres=# select * from cities;
city | location
------------+--------------------
Dallas | (32.7767,-96.797)
Washington | (29.7604,-95.3698)
London |
(3 rows)
Scrolling Results - Turn Off Paging
If there are a lot of results, PostgreSQL will implement scrolling by default. To scroll down through the results, one presses spacebar
. However, if like me, you find this annoying, you can turn off paging with:
\pset pager off
Dump Database
pg_dump \
--host $HOST \
--port "5432" \
--username $USERNAME \
--file /path/to/dump/file.sql \
$DB_NAME
Restore Database
To restore a dump taken using the way we created a pg_dump, you would just run the SQL file like so:
psql \
--host $HOST \
--port "5432" \
--username $USERNAME \
-d $DATABASE \
-f $FILEPATH
pg_restore
command, but it wouldn't work on a plain SQL dump like we created.
Show Settings / Variables
If you want to see all the settings for postgresql, then run:
SHOW ALL;
If you want to see a specific setting then specify it instead of all, like:
SHOW max_connections;
No Wrap Output
If you have lots of columns in your tables and you don't want word wrapping in your output, you can run the following command inside PostgreSQL to use less
as your pager, and tell it not to wrap lines.
\setenv PAGER 'less -S'
If you are using a PostgreSQL docker container it won't have less installed, so you would need to install less
, and then set the environment variable like so:
PAGER="/usr/bin/less -S"
Getting Table Stats
The command below is a good way to get some stats about your tables. It can be quite useful when you want to see how "active" your tables are, or see how many "dead tuples" are taking up space and need deleting by the vacuum process. (Dead tuples are rows that have been deleted but have not been removed from disk yet by the vacuum process).
SELECT
relname as table_name,
n_tup_ins as "inserts",
n_tup_upd as "updates",
n_tup_del as "deletes",
n_live_tup as "live_tuples",
n_dead_tup as "dead_tuples",
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
FROM pg_stat_user_tables
ORDER BY table_name;
Converting Unix Timestamp Integer to Timestamp
Use the to_timestamp
function.
SELECT to_timestamp(my_unix_timestamp_column) as my_timestamp FROM my_table;
To convert that to a date (such as to group by a day of the year) wrap it again like so:
SELECT DATE(to_timestamp(my_unix_timestamp_column)) as my_date FROM my_table;
References
- Stack Overflow - In Postgresql, Force unique on combination of two columns
- postgresqltutorial.com - PostgreSQL Foreign Key
- Stack Overflow - Show tables in PostgreSQL
- Why unsigned integer is not available in PostgreSQL?
- How to configure postgresql for the first time?
- Stack Overflow - How do you create a read-only user in PostgreSQL?
- Stack Overflow - Amazon RDS PostgreSQL: how to create user?
- Stack Overflow - Check Postgres access for a user
- PostgreSQL Docs - Enumerated Types
- PostgreSQL: Can you create an index in the CREATE TABLE definition?
- Stack Overflow - How to be able to vertically scroll terminal to see all data from a large table
- PostgreSQLDocs - JSON Functions and Operators
- Stack Overflow - input file appears to be a text format dump. Please use psql
- Ask Ubuntu - Set PostgreSQL pager to less?
First published: 16th August 2018