sqlcheatsheet.md

PostgreSQL Cheat Sheet

Some things to note about SQL:

• All SQL statements end in a semicolon.
• You can separate statements into separate lines, for readability, as long as you declare the end with a semicolon.
• Capitalizing commands is optional, but highly recommended for readability.
• Need help, or more explanations? Try the tutorials at PG Exercises or Schemaverse.

PSQL Commands

\list           - list all available databases
\dt             - list all tables in the current database
\d+ tablename   - look at a table's structure
\connect testdb - connect to database (specify name)
\c testdb       - connect to database (shorthand)
\conninfo       - check connection info
\?              - all psql commands
\help           - all PostgreSQL commands
\q              - quit

SQL Commands

Create a database

CREATE DATABASE databasename;

Create a table

CREATE TABLE tablename (
  id SERIAL PRIMARY KEY,
  column1 INTEGER REFERENCES table2 (table2_id),
  column2 VARCHAR(15),
  column3 TEXT,
  column4 DATE NOT NULL
);

INSERT data into a table

INSERT INTO tablename (column1, column2, column3, column4)
VALUES (30, 'A test', 'A lot more text than varchar', '2015-07-15');

SELECT data from a table

Select all columns (with wildcard)

SELECT * FROM tablename;

SELECT specific columns

SELECT column1 FROM tablename;
SELECT column1, column2 FROM tablename;

Select distinct values from a column

SELECT DISTINCT column1 FROM tablename;

Selecting using WHERE

SELECT using a WHERE clause

SELECT * FROM tablename
WHERE column1 = 30;

NOT EQUAL

SELECT * FROM tablename
WHERE column1 <> 1;

LIKE (usually uses a wildcard, '%')

SELECT * FROM tablename
WHERE column2 LIKE '%test%';

ILIKE (case insensitive)

SELECT * FROM tablename
WHERE column2 ILIKE '%test%';

ORDER BY

SELECT * FROM tablename
ORDER BY name DESC;

SELECT * FROM tablename
ORDER BY name ASC;

AND/OR

SELECT * FROM tablename
WHERE column1 = 30 AND column2 = 'test' OR column3 = 'woah';

IN/NOT IN

SELECT * FROM tablename
WHERE column1 IN (30, 40) AND column2 NOT IN ('taco', 'burrito');

LIMIT (returns the first rows)

Example: Limit the query results by returning the first 3 results.

SELECT * FROM tablename LIMIT 3;

LIMIT + OFFSET

Example: Return results 4-6

SELECT * FROM tablename
LIMIT 3 OFFSET 3;

Select an aggregate

COUNT

SELECT count(*) from tablename;

MAX/MIN values

SELECT max(*) from tablename;
SELECT min(*) from tablename;

UPDATE data in a table

UPDATE tablename SET column1 = 40
WHERE column2 = 'A test';

ALTER table columns and constraints

ALTER TABLE table1 ADD CONSTRAINT table1_id
FOREIGN KEY (table1_id) REFERENCES table2 (table2_id)
ON DELETE NO ACTION;

ALTER TABLE books ADD COLUMN year_released INTEGER;

ALTER TABLE books ALTER COLUMN name SET NOT NULL;

--

DELETE data from a table

DELETE from tablename
WHERE column1 = 40;

DROP a table

DROP TABLE tablename;

JOINing Tables

It's good to know the differences between JOINs, but you'll usually use plain JOIN, which performs an INNER JOIN by default.

SELECT * FROM person
JOIN librarycard
ON person.id = librarycard.person_id;

GROUP BY

SELECT COUNT(rating) FROM movies
GROUP BY rating;