Skip to content

Instantly share code, notes, and snippets.

@BolajiAyodeji
Last active April 28, 2020 08:50
Show Gist options
  • Save BolajiAyodeji/b29af3e364d6fc77c8939ce9f0db5e89 to your computer and use it in GitHub Desktop.
Save BolajiAyodeji/b29af3e364d6fc77c8939ce9f0db5e89 to your computer and use it in GitHub Desktop.

POSTGRESQL CHEATSHEET

Basic commands

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition including triggers
  • \d+ __table__: More detailed table definition including description and physical disk size
  • \l: List databases
  • \dy: List events
  • \df: List functions
  • \di: List indexes
  • \dn: List schemas
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \dT+: List all data types
  • \dv: List views
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables
  • \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV

User Related:

  • \du: List users
  • \du __username__: List a username if present.
  • create role __test1__: Create a role with an existing username.
  • create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
  • set role __test__;: Change role for current session to __test__.
  • grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.

Configuration

  • Service management commands:
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

CRUD – Create Read Update Delete

Create – Creating table & inserting the values.

  • DB Creation
createdb 'projectx'
  • Table creation
CREATE TABLE users (name, age, birthday);
  • Add values to table
INSERT INTO users (name, age, birthday) VALUES ('Smith', 69, '2000-12-18');

Read – Retrieve table data.

  • Retrieve all table data
SELECT * FROM users
  • Retrive all data with some conditional
SELECT * FROM users WHERE name LIKE 'B%';
  • Retrive all data and sort
SELECT * FROM users ORDER BY score DESC;
SELECT * FROM users ORDER BY score ASC;

Update - Update table data.

  • Update table values
UPDATE users SET age=10 WHERE name='Bolaji';
  • Update table column
ALTER TABLE users ADD score smallint;
  • Update multiple row values
UPDATE users SET score = 50 WHERE name ='Bolaji' OR name='Sally';

Delete - Delete table data.

DELETE FROM users WHERE name=Bolaji;
DROP TABLE users;

SQL Functions

SELECT AVG(score) FROM users;
SELECT SUM(score) FROM users;
SELECT COUNT(score) FROM users;

CREATE TABLE login (
          ID serial NOT NULL PRIMARY KEY,
          secret VARCHAR (100) NOT NULL,
          name text UNIQUE NOT NULL
);
INSERT INTO login (secret, name) values ('123abbc', 'Bolaji');
SELECT * FROM users JOIN login ON users.name = login.name;
@vicradon
Copy link

Thank you Bolaji

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment