hosted by Hero Network LLC

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.

Example:
# 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:

# CREATE TABLE list (
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 215
`int4:`	Numeric binary data of 4 bytes: 2-31 - 231
`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 :
INSERT INTO vendors
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.

UPDATE list
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.
SELECT * FROM list
ORDER by item;
Now, we sort by item.
SELECT * FROM list
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.
DELETE 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

Where

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');

Where

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;

Where

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 WHERE SALARY NOT 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'