Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
postgres cheatsheet

Postgres Cheatsheet

This is a collection of the most common commands I run while administering Postgres databases. The variables shown between the open and closed tags, "<" and ">", should be replaced with a name you choose. Postgres has multiple shortcut functions, starting with a forward slash, "". Any SQL command that is not a shortcut, must end with a semicolon, ";". You can use the keyboard UP and DOWN keys to scroll the history of previous commands you've run.

Setup

installation, Ubuntu

http://www.postgresql.org/download/linux/ubuntu/ https://help.ubuntu.com/community/PostgreSQL

sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > \
  /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5

sudo su - postgres
psql
connect

http://www.postgresql.org/docs/current/static/app-psql.html

psql

psql -U <username> -d <database> -h <hostname>

psql --username=<username> --dbname=<database> --host=<hostname>
disconnect
\q
\!
clear the screen
(CTRL + L)
info
\conninfo
configure

http://www.postgresql.org/docs/current/static/runtime-config.html

sudo nano $(locate -l 1 main/postgresql.conf)
sudo service postgresql restart
debug logs
# print the last 24 lines of the debug log
sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')




Recon

show version
SHOW SERVER_VERSION;
show system status
\conninfo
show environmental variables
SHOW ALL;
list users
SELECT rolname FROM pg_roles;
show current user
SELECT current_user;
show current user's permissions
\du
list databases
\l
show current database
SELECT current_database();
show all tables in database
\dt
list functions
\df <schema>




Databases

list databasees
\l
connect to database
\c <database_name>
show current database
SELECT current_database();
create database

http://www.postgresql.org/docs/current/static/sql-createdatabase.html

CREATE DATABASE <database_name> WITH OWNER <username>;
delete database

http://www.postgresql.org/docs/current/static/sql-dropdatabase.html

DROP DATABASE IF EXISTS <database_name>;
rename database

http://www.postgresql.org/docs/current/static/sql-alterdatabase.html

ALTER DATABASE <old_name> RENAME TO <new_name>;




Users

list roles
SELECT rolname FROM pg_roles;
create user

http://www.postgresql.org/docs/current/static/sql-createuser.html

CREATE USER <user_name> WITH PASSWORD '<password>';
drop user

http://www.postgresql.org/docs/current/static/sql-dropuser.html

DROP USER IF EXISTS <user_name>;
alter user password

http://www.postgresql.org/docs/current/static/sql-alterrole.html

ALTER ROLE <user_name> WITH PASSWORD '<password>';




Permissions

become the postgres user, if you have permission errors
sudo su - postgres
psql
grant all permissions on database

http://www.postgresql.org/docs/current/static/sql-grant.html

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
grant connection permissions on database
GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
grant permissions on schema
GRANT USAGE ON SCHEMA public TO <user_name>;
grant permissions to functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
grant permissions to select, update, insert, delete, on a all tables
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
grant permissions, on a table
GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
grant permissions, to select, on a table
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;




Schema

list schemas
\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;
create schema

http://www.postgresql.org/docs/current/static/sql-createschema.html

CREATE SCHEMA IF NOT EXISTS <schema_name>;
drop schema

http://www.postgresql.org/docs/current/static/sql-dropschema.html

DROP SCHEMA IF EXISTS <schema_name> CASCADE;




Tables

list tables, in current db
\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
list tables, globally
\dt *.*.

SELECT * FROM pg_catalog.pg_tables
list table schema
\d <table_name>
\d+ <table_name>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';
create table

http://www.postgresql.org/docs/current/static/sql-createtable.html

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);
create table, with an auto-incrementing primary key
CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);
delete table

http://www.postgresql.org/docs/current/static/sql-droptable.html

DROP TABLE IF EXISTS <table_name> CASCADE;




Columns

add column

http://www.postgresql.org/docs/current/static/sql-altertable.html

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];
update column
ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];
delete column
ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;
update column to be an auto-incrementing primary key
ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;
insert into a table, with an auto-incrementing primary key
INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);


INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );




Data

read all data

http://www.postgresql.org/docs/current/static/sql-select.html

SELECT * FROM <table_name>;
read one row of data
SELECT * FROM <table_name> LIMIT 1;
search for data
SELECT * FROM <table_name> WHERE <column_name> = <value>;
insert data

http://www.postgresql.org/docs/current/static/sql-insert.html

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
edit data

http://www.postgresql.org/docs/current/static/sql-update.html

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;
delete all data

http://www.postgresql.org/docs/current/static/sql-delete.html

DELETE FROM <table_name>;
delete specific data
DELETE FROM <table_name>
WHERE <column_name> = <value>;




Scripting

run local script, on remote host

http://www.postgresql.org/docs/current/static/app-psql.html

psql -U <username> -d <database> -h <host> -f <local_file>

psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>
backup database data, everything

http://www.postgresql.org/docs/current/static/app-pgdump.html

pg_dump <database_name>

pg_dump <database_name>
backup database, only data
pg_dump -a <database_name>

pg_dump --data-only <database_name>
backup database, only schema
pg_dump -s <database_name>

pg_dump --schema-only <database_name>
restore database data

http://www.postgresql.org/docs/current/static/app-pgrestore.html

pg_restore -d <database_name> -a <file_pathway>

pg_restore --dbname=<database_name> --data-only <file_pathway>
restore database schema
pg_restore -d <database_name> -s <file_pathway>

pg_restore --dbname=<database_name> --schema-only <file_pathway>
export table into CSV file

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> TO '<file_path>' CSV
export table, only specific columns, to CSV file
\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' CSV
import CSV file into table

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> FROM '<file_path>' CSV
import CSV file into table, only specific columns
\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' CSV




Debugging

http://www.postgresql.org/docs/current/static/using-explain.html

http://www.postgresql.org/docs/current/static/runtime-config-logging.html


Advanced Features

http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm

@ghost

This comment has been minimized.

@dantheman213

This comment has been minimized.

Copy link

dantheman213 commented Nov 14, 2016

Great guide! I just wanted to add a tool that saved me valuable time in DB administration and source control.

For some reason PostgreSQL doesn't have an easy way to export all table schemas or stored functions. I wanted a tool in PostgreSQL that is convenient and will work well with external management tools or use with source control like git.. I couldn't find one!

So, I made an export tool that does all this!

If anyone is interested in easy export of your PostgreSQL database table schemas and stored functions, check out my one-click script:
https://gist.github.com/dantheman213/aff70ee42a11f2d1fa46983878cd62e1

@AurinoJunior

This comment has been minimized.

Copy link

AurinoJunior commented Apr 28, 2019

Thank you dude, this helped me a lot.

@andysims

This comment has been minimized.

Copy link

andysims commented Jan 24, 2020

Super helpful. Thank you, Sir.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.