This list is not comprehensive but tries to cover at least 80%.
*Uses GEOS, Requires Geos 3.1+ to take advantage of new or improved featuresG3.1
Most commonly used functions and operators
Measurement functions return in same units geometry SRID except
for the *sphere and *spheroid versions which return in meters
Denotes a new item in this version 1
Denotes automatically uses spatial indexes 2
Denotes enhanced in this version 3

Geometry Types - WKT Representation

POINT(0 0)
LINESTRING(0 0,1 1,1 2)
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
MULTIPOINT(0 0,1 2)
MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ..)
GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))

BBox and Geometry Operators

A &< B (A overlaps or is to the left of B) 2
A &> B (A overlaps or is to the right of B) 2
A << B (A is strictly to the left of B) 2
A >> B (A is strictly to the right of B)2
A &<| B (A overlaps B or is below B)2
A |&> B (A overlaps or is above B)2
A <<| B (A strictly below B)2
A |>> B (A strictly above B)2
A = B (A bbox same as B bbox)
A @ B (A completely contained by B)2
A ~ B (A completely contains B)2
A && B (A and B bboxes intersect)2
A ~= B  - true if A and B geometries are geometrically equal2

Common Use SFSQL Examples

 --Create a geometry column named the_geom in a
--table called testtable located in schema public
-- to hold point geometries of dimension 2 in WGS84 longlat
SELECT AddGeometryColumn('public', 'testtable', 'the_geom', 4326, 'POINT', 2);

--Insert a record into the new table
 INSERT INTO testtable(description, the_geom)
VALUES('center of boston',
	ST_GeomFromText('POINT(-71.0891380310059 42.3123226165771)', 4326));

--Insert a point record into the new table - faster than st_geomfromtext for points
 INSERT INTO testtable(description, the_geom)
VALUES('center of boston',
	ST_SetSRID(ST_MakePoint(-71.0891380310059, 42.3123226165771), 4326));

--Create a spatial index on the new geometry column
 ALTER TABLE testtable ALTER COLUMN the_geom SET NOT NULL;
 CREATE INDEX idx_testtable_the_geom ON testtable USING gist(the_geom);
 ALTER TABLE testtable CLUSTER ON idx_testtable_the_geom;

--Find the neighborhood with the smallest area
 SELECT neigh_name, ST_Area(the_geom)
	FROM neighborhoods
	ORDER BY ST_Area(the_geom) limit 1;

--Find the total area of each ward in square feet of wards in Boston,
--the extent (bounding box) of each ward, average sqft per precinct in each ward

 SELECT ward, sum(ST_Area(ST_Transform(the_geom,2249))) as totarea,
  avg(ST_Area(ST_Transform(the_geom,2249))) as avgarea_precinct,
	ST_Extent(ST_Transform(the_geom,2249)) as wardextent
	FROM wardprecincts WHERE city = 'Boston'
	GROUP BY ward;

--Find all land parcels within 100 units of a specific parcel.
SELECT l2.parcel_id, l2.st_num, l2.st_name
 FROM landparcels l , landparcels l2
  WHERE ST_DWithin(l.the_geom, l2.the_geom, 100)
	AND l.parcel_id = '1234560000';

--Break up multipolygons into individual polygons
SELECT neigh_name,
(ST_Dump(the_geom)).geom  As polygeom
	FROM neighborhoods;

--Take individual polygons and create one multipolygon for each neighborhood
 --Note if you have a mixed collection of geometries, will return a geometry collection
SELECT neigh_name, ST_Collect(polygeom) as the_geom
	FROM neighborhoods
	GROUP BY neigh_name;

Using Shape Dumper/Loader Commandline Tools

Load data into PostgreSQL from ESRI shape file
shp2pgsql -s 4326 neighborhoods public.neighborhoods > neighborhoods.sql
psql -h myserver -d mydb -U myuser -f neighborhoods.sql

Exporting data from PostgreSQL to ESRI Shape file
pgsql2shp -f jpnei -h myserver -u apguser -P apgpassword mygisdb
	"SELECT neigh_name, the_geom FROM neighborhoods WHERE neigh_name = 'Jamaica Plain'"
			
			Boston GIS     Paragon Corporation
		    Postgres OnLine Journal PostGIS in Action