Creating the database

Main commands:

CREATE DATABASE database_name;
DROP DATABASE database_name;

Making tables:

CREATE TABLE "table_name" 
  ("column_1" "data_type_for_column_1",
   "column_2" "data_type_for_column_2",
   ... );
DROP TABLE "table_name";

The main types are:

• INTEGER(size)
• INT(size)
• SMALLINT(size)
• TINYINT(size)
• DECIMAL(size,d)
• NUMERIC(size,d)
• CHAR(size)
• VARCHAR(size)
• DATE(yyyymmdd)

size is the number of characters.

Modifiers can be added to the type:

• NOT NULL
• PRIMARY KEY
• UNIQUE

Alternatively:

CREATE INDEX index_name
ON table_name (column_name_1, column_name_2, ...);

CREATE UNIQUE INDEX index_name
ON table_name (column_name_1, column_name_2, ...)

DROP INDEX table_name.index_name;

Manipulating data

New data can be added:

INSERT INTO table_name
VALUES (value_1, value_2,....);

INSERT INTO table_name (column1, column2,...)
VALUES (value_1, value_2,....);

We can also update values:

UPDATE table_name
SET column_name_1 = new_value_1, column_name_2 = new_value_2
WHERE column_name = some_value;

Or delete:

DELETE FROM table_name
WHERE column_name = some_value;

Use truncate to drop all data on a table

TRUNCATE TABLE table_name;

Getting data

Select statments read data:

SELECT column_name(s) FROM table_name;
SELECT * FROM table_name;

Data can be filtered:

SELECT column_name(s) FROM table_name
  WHERE column operator value
  AND column operator value
  OR column operator value
  AND (... OR ...)
  ...;

Main operators are the normal ones plus BETWEEN and LIKE.

Data can be summarized:

SELECT column_1, ..., SUM(group_column_name)
  FROM table_name
  GROUP BY group_column_n

Grouping operations are:

• AVG
• COUNT
• SUM
• MIN
• MAX

Two or more tables can be joined with INNER, RIGHT or LEFT:

SELECT column_1_name, column_2_name, ...
  FROM first_table_name
  INNER JOIN second_table_name
  ON first_table_name.keyfield = second_table_name.foreign_keyfield

PostgreSQL

Commands:

\list # list databases (or \l, \l+)
\dt # List tables in current database
\connect database # Connect/switch database (or \c)

You can also get information from the databases from SQL:

SELECT datname FROM pg_database
WHERE datistemplate = false;

This lists tables in the current database

SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

Example

CREATE DATABASE my_app;
CREATE TABLE users(
  uid serial primary key,                                                 firstname varchar(100) not null,                                       lastname VARCHAR(100) not null, email VARCHAR(100) not null,
  pwdhash VARCHAR(100) not null);