This site will work and look better in a browser that supports web standards, but it should be accessible for any browser or Internet device.
If you're seeing this message, you may have to scroll to the bottom of the page to see the navigation links.

PostgreSQL Cheat Sheet

Command Summary

createdb This is a commandline program to create a database (PostgreSQL only). The database will hold various tables.
Example: # createdb groceries
createuser This is the commandline program to setup postgreSQL users. Typically, you'll have already used useradd to add them as regular users in the system.
# createuser luser
Enter user's postgress ID or RETURN to use unix user ID: 500 ->
Is user "luser" allowed to create databases (y/n) y
Is user "luser" allowed to add users? (y/n) n
createuser: luser was successfully added
psql This is the commandline program to access and manipulate PostgreSQL databases. Note: you have to be logged in as a user with appropriate privaledges.
Example: # psql grocery
Create Table This command creates a table with its columns.
The syntax looks like this:
CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
[INHERITS (inherits, ...)]
[CONSTRAINT constraints CHECK (test), CHECK (test)] );
And an example:
    item TEXT,
    vendorcode INT,
    quantity TEXT
name Is the name that is given to the table and how it is referenced by any command
Column: Is the name of the column
Type: Is the type of data(varchar, char, int, date, time, timestamp), Postgres has other types ofdata, but they aren't compatible with ANSI SQL (full list).
char(range): Alfa-numeric data of fixed length 30 bytes.
varchar(rango): Alfa-numeric data of variable length up to 30 bytes.
int2: Numeric binary data of 2 bytes: 2**-15 hasta 2**15
int4: Numeric binary data of 4 bytes: 2**-31 - 2**31
money: Fixed point data number, ex: money(6,3) is a number of six digits, 3 of which are decimal (3 integers and 3 decimals).
time: Temporal data containing hours, minutes, seconds and hundreds of second, HH:MM:SS:CCC
date: Date data containing yer, month and day, AAAA/MM/DD
timestamp: Date and time data as AAAA/MM/DD:HH:MM:SS:CCC
float(n): Real data of single precision.
float3: Real data of double precision.
The definitions of the type of data are specific for every kind of SQL manager, nevertheless there is an SQL standard (the last one is ANSI/92 or SQL/3) that defines a few types with its characteristics. This course we will only see few types specific of PostgreSQL. 
Value The value that it will have by default
Inherits:  This is Postgres's own. It defines an inheritance from another table. This will create an entity that contains the columns of the table that we are creating and the ones inherited
Nom_cons This defines a rule of integrity to meet each time that a row is modified 
Test: Conditions to check
DROP TABLE Erases a table.
INSERT This command adds a record to the database.
Example 1 :
    VALUES ('Root Beer', 100, 3);
With example 1, you have to be absolutely certain that the fields are in the right order and that none will be added
Example 2:
INSERT INTO vendors (vendorcode, vendorname)
    VALUES (101, 'General Department Store');
A little more complicated, but dependable Example 3:
        $conn = pg_connect( $stuff);
        $query = "INSERT INTO item (f1, f2) VALUEs ('val1', 'val2')";
        $result = pg_exec( $conn, $query);
        $pg_oid = pg_getlastoid($result);
UPDATE This command updates an existing record.
    SET item = 'African Violets'
    WHERE vendorcode = 103; Change the field item to African Violets for every record whose vendorcode is 103.
SELECT This command is the foundation of SQL, allows us to create a temporal table containing the necessary data items only. SELECT can take as parameters functions or complex statements, as well as sub_selects:
SELECT * FROM vendors;
displays every field and every record in vendors.
SELECT item, quantity FROM vendors;
displays the quantity and item fields from every record.
SELECT item, quanity FROM vendors
    where vendor = 'Home Depot'
This time, just the ones sold by Home Depot.
SELECT list.item, vendors.vendorname, list.quantity
    FROM list, vendors
    WHERE list.vendorcode = vendors.vendorcode
This time, we'll show fields from list and vendors for all records, joining by vendorcode.
    ORDER by item;
Now, we sort by item.
    WHERE item ~ '^[I-N]';
This time, a regular expression search. In this case, find every item beginning with the letters I through N.
select * from table where field like '%somestring%'
    % is the wildcard for zero or more characters and _ is the wildcard for 1 or more characters
DELETE deletes rows of a table.
DELETE FROM list WHERE item = 'African Violet';
deletes every African Violet from list.
deletes everything from list.
CREATE INDEX Indexes the contents of a column
CREATE INDEX listtab ON list (vendorcode); Indexes the field vendorcode
CREATE UNIQUE INDEX vendortab ON vendors (vendorcode)
Same idea, but the vendorcode field is unique.
VACUUM Clean database of inactive data. For example. deleted records. Solves many miscellaneous problems in pre-7.0 version of postgreSQL if done now and then.
VACCUM list;
Vaccum the database list.
REVOKE Removes privaleges for a certain list from a certain user.
REVOKE ALL ON list FROM luser;
Strip luser of all ability to work on the list table.
GRANT Adds privaleges for a certain list for a certain user.
GRANT ALL ON list TO luser;
Give luser full access to the list table. Note: besides ALL, you can grant partially priveledges, such as SELECT
pg_dump Dumps the schema and data from a database out to a text file.
# pg_dump grocery > db.out
Dumps the schema and data for the grocery database into a file calle db.out.
psql -e Load or reload database from file
psql -e grocery < db.out
loads or reloads the schema and data in db.out into the database grocery
BEGIN WORK This is another fundamental command. It tells the DB manager to commit ALL the modifications given since BEGIN WORK. In our particular DB manager a BEGIN WORK marks the initialisation of a transaction, in other managers the beginning of a transaction is mark by the first command that alters something in the database. In postgreSQL all commands that alter data will operate directly unless there was a previous BEGIN WORK. 

NOTE: commands that modify the scheme of the database execute a COMMIT WORK, therefore if a transaction is opened and any such commands is executed our transaction will be closed immediately and it will be impossible to launch a ROLLBACK WORK. 

While a user has an open transaction he can declare the access type to his data by other users: 

  • Modified data
  • Original Data previous to the transaction
  • Block data access
  • COMMIT WORK Closes a transaction leaving the committing the modifications introduced. The command ROLLBACK WORK returns the data to their state previous the current transaction.
    Drop Table deletes an entire table (not database)
    drop table table;
    removes the table table.
    Alter Table Alter or add columns in existing tables or modify existing tables as a whole. In PostgreSQL before 7.0, modification only deals with adding new columns. In 7.0+, modification can alter existng columns and drop them.

    Add a Column
    ALTER TABLE table ADD name type
    Table: Name of the table to modify Name: Name of the column to add Type: Type of data (see CREATE TABLE)

    Change an existing (integer) field into a serial field
    create sequence myTable_idField_seq; create unique index myTable_idField_key on myTable (idField); alter table myTable alter column myField set default nextval('myTable_myField_seq');
    myTable: Name of the table to modify idField: Name of the column to change

    Rename a Table
    Alter table X rename to Y
    renames table X to Y. can sometimes fix problems when 'RestrictionClauseSelectivity' is reported after a query
    ALTER TABLE table RENAME TO table2;
    Table: Name of the table to modify Name: Name of the column to add Type: Type of data (see CREATE TABLE)

    Rename an Existing Field
    Alter table myTable rename [column] X to Y
    renames column X to Y in table myTable

    Select Operators

    Operator Description Example
    <, >, <=, >= Standard Boolean comparison operators for numeric and date data. Where Salary > 25000
    =, <> Standard Boolean equality operators for numeric, date, and text data. Where Salary >= 25000
    IS Logical operator for testing equality (equivalent to Boolean =).
    AND Logical AND operator connects criteria to narrow a search. Criteria connected by an AND must both be true to return a TRUE result.
    OR Logical OR operator connects criteria for broadening a search. One or more of the criteria connected by an OR must be true to return a TRUE result.
    NOT Logical NOT operator excludes criteria from search.
    IN Used to check if a particular expression is located in a list of possible values. WHERE POSITION IN ('Manager', 'Staff')
    BETWEEN Offers a range of values for expression. WHERE SALARY BETWEEN 30000 AND 50000
    LIKE Used to check for part of a text expression inside of another.
    Percent sign (%) indicates that any character(s)
    Underscore (_): indicates any character (not on all DBs!)
    WHERE LASTNAME LIKE 'S%': finds every LASTNAME beginning with 'S'
    WHERE LASTNAME NOT LIKE 'S%':> finds every LASTNAME not beginning with 'S'
    Port PostgreSQL can communicate with PostgreSQL clients and other tools --e.g. PHP, TCL, etc.-- via TCP/IP on port 5432. It may communicate via UDP in current or prior versions, but I'm not certain. WHERE LASTNAME LIKE 'S%': finds every LASTNAME beginning with 'S'
    WHERE LASTNAME NOT LIKE 'S%':> finds every LASTNAME not beginning with 'S'


    Linux, SQL and the Web by Charles Fisher

    SQL for Web Nerds by Philip Greenspun

    Resources: Official PostgreSQL Documents

    SQL command reference




    Guide (intoduction and list of related tools)

    RDBMS How-To

    Resources: Official PHP Resources

    PostgreSQL Functions

    Other SQL Resources

    PostgreSQL: Introduction and Concepts by Bruce Momjian (Addison-Wesley)

    Introduction to Structured Query Language

    DB2 Cookbook Downloads

    Teach Yourself SQL in 21 Days (Que)

    SQL Tutorial

    SQL Articles at Database Central