MOT CLES EN MAJUSCULES
attributs et nom de tables en minuscules

strings: 
'voici une chaine'
'voici une chaine '' avec des '' quotes '
$$ Voici une chaine avec des '  $$
$motcle$ ma chaine $motcle$

types:
'string'::type
CAST (expression AS type)
typename(expression)


Persmission d utilisation de:
	\b backslash
	\f form feed
	\n new line
	\r return
	\t tab

select 'foo' 'bar' => select 'foobar'
select 'foo'
'bar'; => select 'foobar'
max 1 espace ou 1 entree.

caracteres speciaux: [] utilises pour selectionner les elements d un tableau
                     : pour selectionner des arrays ?

-- commentaire sql standard


CREATE FUNCTION dept(text) RETURNS dept
    AS $$ SELECT * FROM dept WHERE name = $1 $$
    LANGUAGE SQL;


CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c) -- couple d unicite
);

PRIMARY KEY => Unique NOT NULL

product_no integer REFERENCES products <=>  product_no integer, FOREIGN KEY (product_no) REFERENCES table(product_no)

+ ON DELETE CASCADE
+ ON DELETE RESTRICT



ALTER TABLE products ADD COLUMN description text;
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE products DROP CONSTRAINT some_name;

pour enlever le NOT NULL=> ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
Changer valeur par defaut: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
Enlever la valeur par defaut: ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
Changer le type: ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
Renommer colonne: ALTER TABLE products RENAME COLUMN product_no TO product_number;
Renommer table: ALTER TABLE products RENAME TO items;





Inheritance

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

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);


Exclure les capitales de la requete
SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;



SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 and c.tableoid = p.oid;

which returns:

 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 capitals | Madison   |      845


CREATE TABLE measurement_yy04mm02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);


CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);

CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
    INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

CREATE RULE measurement_insert_yy06mm01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
DO INSTEAD
    INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );


CREATE VIEW measurement AS
          SELECT * FROM measurement_yy04mm02
UNION ALL SELECT * FROM measurement_yy04mm03
...
UNION ALL SELECT * FROM measurement_yy05mm11
UNION ALL SELECT * FROM measurement_yy05mm12
UNION ALL SELECT * FROM measurement_yy06mm01;


contraintes d exclusion: SET constraint_exclusion = on;
pour ne pas scanner des tables dont on sait par avance qu il n y aura pas de resultats

voir ce que fait une requete sql:
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

DROP TABLE products CASCADE; si des elements sont lies, ils seront supprimés


FROM:
SELECT * FROM t1 CROSS JOIN t2; (tout elements t1 avec chaque elements t2)
SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
SELECT * FROM t1 INNER JOIN t2 USING (num);
voir http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html

ALIAS:
FROM table_reference AS alias   ou bien   FROM table_reference alias
si il y a un alias, on doit l utiliser, le nom de la table n est plus valide

SUBQUERY 
FROM (SELECT * FROM table1) AS alias_name





exemples  where
SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

reutilisation de variables:
SELECT a AS value, b + c AS sum FROM ...


query1 UNION [ALL] query2 => APPEND
query1 INTERSECT [ALL] query2 => DANS LES DEUX
query1 EXCEPT [ALL] query2 => DANS 1 mais pas dans 2

LIMIT nombre_a_voir debut_de_row

types SQL: bit, bit varying, boolean, char, character varying, character, varchar, date, 
double precision, integer, interval, numeric, decimal, real, smallint, time (with or without time zone), 
timestamp (with or without time zone).


NUMERIC(precision, scale) => (max chiffres, virgules)
 In addition to ordinary numeric values, the floating-point types have several special values:
Infinity
-Infinity
NaN


CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

typage a l insertion
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation


Table 8-13. Special Date/Time Inputs
epoch	date, timestamp	1970-01-01 00:00:00+00 (Unix system time zero)
infinity	timestamp	later than all other time stamps
-infinity	timestamp	earlier than all other time stamps
now	date, time, timestamp	current transaction's start time
today	date, timestamp	midnight today
tomorrow	date, timestamp	midnight tomorrow
yesterday	date, timestamp	midnight yesterday
allballs	time	00:00:00.00 UTC





Booleean:
True: TRUE 't' 'true' 'y' 'yes' '1'
FALSE: FALSE 'f' 'false' 'n' 'no' '0'

New array values can also be constructed by using the concatenation operator, ||.
SELECT ARRAY[1,2] || ARRAY[3,4];


Recherche dans des arrays:
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;
s ecrit :  SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);