PostgreSQL 7 manual:
Using PostgreSQL from PHP

postgresql is case sensitive when doing string comparisons

Use the correct case in your query.  (i.e. WHERE lname='Smith') 
Use a conversion function, like lower() to search.  (i.e. WHERE lower(lname)='smith') 
Use a case-insensitive operator, like ILIKE or *~
	   

Creating a database

createdb mydatabase1 -U postgres //(will ask password for user postgres)

Using the Database

psql mydatabase1 postgres

Delete a Database

destroydb mydatabase1

Basic Sql Examples

CREATING A TABLE:
-----------------
			
CREATE TABLE CUSTOMER (
  CUSTOMER_ID serial,
  CUSTOMER_NAME varchar(100),
  PRIMARY KEY (CUSTOMER_ID)
);
			

PostgreSQL table inheritance

INHERITANCE of tables !!
--------------------------

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int            -- (in ft)
);

CREATE TABLE capitals (
    state           char2
) INHERITS (cities);		
		

Primary keys

- PRIMARY KEY is the same as UNIQUE and not NULL, for example:
		
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

- Primary keys can also constrain more than one column; the syntax is similar to 
  unique constraints:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

CREATE TABLE CUSTOMER (
    CUSTOMER_ID serial,
    CUSTOMER_NAME varchar(100),
    PRIMARY KEY (CUSTOMER_ID)
);
		

Basic Commands

Many commands inside psql begin with \    , example \h  for help

\g to execute a query.  ; at the end would also work.
\i to read from a file. ie:   #  \i filename
\q to exit
   
show all databases
-------------------
\l  show all database

show all tables
----------------
\dt   show all the tables

\di list indexes
\dv list views
   
show all fields of a table
---------------------------
\d customer   OR the query below

SELECT
	a.attnum,
	a.attname AS field,
	t.typname AS type,
	a.attlen AS length,
	a.atttypmod AS lengthvar,
	a.attnotnull AS notnull
FROM
	pg_class c,
	pg_attribute a,
	pg_type t
WHERE
	c.relname = 'customer'
	and a.attnum > 0
	and a.attrelid = c.oid
	and a.atttypid = t.oid
ORDER BY a.attnum;

	  

Primary keys

postgresql uses '' to quote strings, not ""
The quotations are used to quote system identifiers(table, database and column names)

example: 
WHERE "last_name" = 'Smith'
  		

Additional features of Postgresql

views 
procedural languages 
triggers 
customizable aggregates 
transactions 
		

Views

--  || is for concatenation

CREATE VIEW staff_having_goals AS
SELECT staffid, firstname || lastname as fullname 
FROM Staff
WHERE datefired ISNULL and seniorstaff = TRUE
ORDER BY lastname, firstname
	  
	

Autoincrement columns

For sequences we can use a sequence to create a column that increments automatically

1. Create the sequence

CREATE SEQUENCE TEST1_SEQUENCE INCREMENT 1 START 5;

2. The table will use the sequence next value as a default value

CREATE TABLE TEST1(
	TEST1_ID int not null default nextval('TEST1_SEQUENCE'),
	TEST1_NAME varchar(120) default '',
	primary key( TEST1_ID )	
);