# Cette ligne sert a laisser les droits a un user postgres 
# utilise pour la maintenance automatique (autovacuum and cie),
local   all         postgres                          ident sameuser

# nom_utilisateur utilise les bases sans password en local uniquement.
local   all         nom_utilisateur                                        trust
host    all         nom_utilisateur    127.0.0.1         255.255.255.255   trust

#necessite un /etc/init.d/postgres restart pour la prise en compte de modif

Plus de détail ici :
http://docs.postgresqlfr.org/8.1/client-authentication.html#example-pg-hba.conf

Commande pour créer ou détruire un user :

createuser nom_utilisateur
createuser -d -a nom_utilisateur -P # demande un mot de passe, man pour plus d'info
dropuser nom_utilisateur

Config avancé

http://docs.postgresqlfr.org/8.1/runtime-config.html

Commandes

liste des bases de données

psql -l

créer base :

createdb base -O owner -E UNICODE

inserer un script sql

psql -d base < script

se connecter

psql -d base

Apres s'être connecté, on peut utiliser des requêtes sql ou des commandes psql

Quelques commandes dans psql :

\?   /* aide psql */
\h   /* aide sql */
\dt  /* liste des tables */
\di  /* liste des indexs */
\ds  /* liste des sequences */
\du  /* liste des users */
\l  /* liste des bases */


\d objet /*description objet (table, index, sequence...)


\dS /* liste des tables systèmes  */
\df /* liste des fonctions*/
\dd /* liste des commentaires (entre autre pour les fonctions)*/
\do /* liste des operateurs*/
\dT+ /* liste des types*/


\timing /* active le chorometrage des commandes */

\i file /*executer les commande du fichier */
\o file /*ecrire les resultats dans le fichier */


\q  /* ou ctr-D pour quitter */

Exemples de commande SQL de base:


SELECT * FROM MA_TABLE;
SELECT * FROM MA_TABLE WHERE MON_CHAMP = 0;
UPDATE MA_TABLE SET MON_CHAMP = 1;
INSERT INTO MA_TABLE VALUES (1, 'text');

Exemple de commande SQL de manipulation de table

CREATE TABLE ma_table (
    champ_old text,
    champ_autre integer
);

ALTER TABLE ma_table ADD COLUMN mon_champ integer;
UPDATE ma_table set mon_champ = 0;
ALTER TABLE ma_table ALTER COLUMN mon_champ SET NOT NULL;

ALTER TABLE ma_table ALTER COLUMN mon_champ DROP NOT NULL;
ALTER TABLE ma_table DROP COLUMN mon_champ;
ALTER TABLE ma_table RENAME COLUMN champ_old TO champ_new;

ALTER TABLE ma_table ADD CHECK (mon_champ <> 5);
ALTER TABLE ma_table ADD CONSTRAINT autre_nom UNIQUE (no_produit);
ALTER TABLE ma_table ADD FOREIGN KEY (id_produit) REFERENCES produits;
ALTER TABLE ma_table DROP CONSTRAINT autre_nom;

DROP TABLE ma_table;
DROP TABLE ma_table CASCADE;

ALTER TABLE table_old RENAME TO table_new;

/* exemple augmentation taille de colonne */
ALTER TABLE ma_table ADD COLUMN name2 character varying(300);
update ma_table set name2=name;
ALTER TABLE ma_table DROP COLUMN name;
ALTER TABLE ma_table rename COLUMN name2 to name;
ALTER TABLE ma_table ALTER COLUMN name SET NOT NULL;

Sauvegarde et restauration.

archivage d'une base posgressql avec :

pg_dump nombase -f nomfichier

remplissage base avec :

$ psql -d nombase -f nomfichier

même chose en compressé
archivage

pg_dump -Ft -b mydb > db.tar

rechargement

pg_restore -d newdb db.tar

Sequences

Quelques requetes sql.
Creation de compteur :

CREATE SEQUENCE moncompteur;

Increment et renvoie la valeur du compteur :

SELECT nextval('moncompteur');

Renvoie la valeur du compteur :

SELECT currval('moncompteur');

Reinitialise un compteur :

SELECT setval('moncompteur',valeur);

Destruction du compteur :

DROP SEQUENCE moncompteur;

Destruction du compteur (ne provoque pas d'erreur si le
compteur n'existe pas) :

delete from pg_type where typname='myseq';
delete from pg_class where relname='myseq';

Indexation full texte

J'utilise ici tsearch 2
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html

SYSTEM

Installation

apt-get install postgresql-contrib-8.1 ifrench-gut wfrench

Initialisation

psql pressblog < /usr/share/postgresql/8.1/contrib/tsearch2.sql

SQL

Index simple

ALTER TABLE ma_table ADD COLUMN mon_index_fti tsvector;
UPDATE ma_table SET mon_index_fti=to_tsvector('default', mon_champ);
VACUUM FULL ANALYZE;
CREATE INDEX mon_index_fti_idx ON ma_table USING gist(mon_index_fti);
VACUUM FULL ANALYZE;

Trigger pour indexation a la volée

CREATE TRIGGER mon_trigger BEFORE UPDATE OR INSERT ON ma_table
FOR EACH ROW EXECUTE PROCEDURE tsearch2(mon_index_fti, mon_champ);

Index multiple sur une table

ALTER TABLE ma_table ADD COLUMN mon_index_fti tsvector;
UPDATE ma_table SET mon_index_fti=to_tsvector('default', coalesce(champ_1,'') ||' '|| coalesce(champ_2,''));
VACUUM FULL ANALYZE;
CREATE INDEX mon_index_fti_idx ON ma_table USING gist(mon_index_fti);
VACUUM FULL ANALYZE;

Trigger pour indexation a la volée

CREATE TRIGGER mon_trigger BEFORE UPDATE OR INSERT ON ma_table
FOR EACH ROW EXECUTE PROCEDURE tsearch2(mon_index_fti, champ_1, champ_2);

Pour que le trigger fonctionne, avant un UPDATE ou INSERT,
il faudra lancer la requete suivante

select set_curcfg('default');

Recherches

SELECT * FROM ma_table WHERE mon_index_fti @@ to_tsquery('texteachercher')::tsquery;

SELECT * FROM ma_table WHERE mon_index_fti @@ to_tsquery('mot1 | mot2')::tsquery;

SELECT * FROM ma_table WHERE mon_index_fti @@ to_tsquery('mot1 & mot2')::tsquery;

SELECT * FROM ma_table WHERE mon_champ ~* '.*phrase a chercher.*';