Connect to the Postgres REPL

$ psql

Postgres REPL Commands

\q Quit Database

\l List Databases

\i import

CREATE DATABASE database_name; Create a database

\c database_name Connect to a database

\d Display Relations

\dt List tables in database

CTRL + C Kill the current command

CMD + K Clears the screen

SHOW data_directory; Shows location of database

CREATE TABLE table_name ( id BIGSERIAL PRIMARY KEY, column_2 INTEGER, column_3 VARCHAR(255), column_4 BOOLEAN, column_5 INTEGER REFERENCES other_table(id) );

\d table_name View table column info

SELECT * FROM <table name>;

The asterisk or star symbol (*) means all columns.

The semi-colon (;) terminates the statement like a period in sentence or question mark in a question.

Examples: SELECT * FROM books; SELECT * FROM products; SELECT * FROM users; SELECT * FROM countries; Finding the Data You Want

SELECT <columns> FROM <table> WHERE <condition>;

Find all rows that a given value matches a column's value.

SELECT <columns> FROM <table> WHERE <column name> = <value>;

Examples:

SELECT * FROM contacts WHERE first_name = "Andrew";

SELECT first_name, email FROM users WHERE last_name = "Chalkley";

SELECT name AS "Product Name" FROM products WHERE stock_count = 0;

SELECT title "Book Title" FROM books WHERE year_published = 1999;

Inserting a single row:

INSERT INTO <table> VALUES (<value 1>, <value 2>, ...);

This will insert values in the order of the columns prescribed in the schema.

Examples:

INSERT INTO users VALUES  (1, "chalkers", "Andrew", "Chalkley");
INSERT INTO users VALUES  (2, "ScRiPtKiDdIe", "Kenneth", "Love");

INSERT INTO movies VALUES (3, "Starman", "Science Fiction", 1984);
INSERT INTO movies VALUES (4, "Moulin Rouge!", "Musical", 2001);

An update statement for all rows:

UPDATE <table> SET <column> = <value>;

The = sign is different from an equality operator from a WHERE condition. It's an assignment operator because you're assigning a new value to something.

Examples:

UPDATE users SET password = "thisisabadidea";
UPDATE products SET price = 2.99;

An update statement for specific rows:

UPDATE <table> SET <column> = <value> WHERE <condition>;

Examples:

UPDATE users SET password = "thisisabadidea" WHERE id = 3;
UPDATE blog_posts SET view_count = 1923 WHERE title = "SQL is Awesome";

To delete all rows from a table:

DELETE FROM <table>;

Examples:

DELETE FROM logs;
DELETE FROM users;
DELETE FROM products;

To delete specific rows from a table:

DELETE FROM <table> WHERE <condition>;

Examples:

DELETE FROM users WHERE email = "[email protected]";
DELETE FROM movies WHERE genre = "Musical";
DELETE FROM products WHERE stock_count = 0;

Placing the percent symbol (%) any where in a string in conjunction with the LIKE keyword will operate as a wildcard. Meaning it can be substituted by any number of characters, including zero!

SELECT <columns> FROM <table> WHERE <column> LIKE <pattern>;

Examples:

SELECT title FROM books WHERE title LIKE "Harry Potter%Fire";
SELECT title FROM movies WHERE title LIKE "Alien%";
SELECT * FROM contacts WHERE first_name LIKE "%drew";
SELECT * FROM books WHERE title LIKE "%Brief History%";

To count rows you can use the COUNT() function.

SELECT COUNT(*) FROM <table>;

To count unique entries use the DISTINCT keyword too:

SELECT COUNT(DISTINCT <column>) FROM <table>;

To count aggregated rows with common values use the GROUP BY keywords:

SELECT COUNT(<column>) FROM <table> GROUP BY <column with common value>;

To get the average value of a numeric column use the AVG() function.

SELECT AVG(<numeric column>) FROM <table>;
SELECT AVG(<numeric column>) FROM <table> GROUP BY <other column>;

Ordering by a single column criteria:

SELECT * FROM <table name> ORDER BY <column> [ASC|DESC];

ASC is used to order results in ascending order.

DESC is used to order results in descending order.

Examples:

SELECT * FROM books ORDER BY title ASC;
SELECT * FROM products WHERE name = "Sonic T-Shirt" ORDER BY stock_count DESC;
SELECT * FROM users ORDER BY signed_up_on DESC;
SELECT * FROM countries ORDER BY population DESC;