wiki/HowtoPostgreSQL/Utilisation.md

---
title: Howto PostgreSQL: utilisation
---


# Utilisation PostgreSQL

## Connexion

On peut maintenant "utiliser" notre base de données avec le client en ligne PostgreSQL en ligne de commande :

~~~
$ psql base login
ma_base=>
~~~

Voici quelques commandes de base :

~~~
\l = liste des bases
\d = liste des tables
\q = quitter
\h = aide
SELECT version(); = version PostgreSQL
SELECT current_date; = date actuelle
\i fichier.sql = lit les instructions du fichier fichier.sql
\d table = décrit une table (comme DESCRIBE avec MySQL)
~~~

Pour changer de base de données :

~~~
=> \c base;
~~~

## Création de table

Voici les différents types de données pour les champs d'une table :

~~~
char(n)
varchar(n)
int
real
double precision
date
time
timestamp
interval
~~~

Remarque : on peut aussi définir ses propres types de données

La syntaxe classique pour créer/supprimer une table :

~~~
=> CREATE TABLE ma_table (col1 type, […], coln type);
=> DROP TABLE ma_table;
~~~

Pour la forme un petit exemple tiré de la doc de PostgreSQL :

~~~
=> CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);
~~~

Note : deux tirets `--` introduisent des commentaires.

Pour mettre à jour des tables :

~~~
=> ALTER TABLE evosondage_admin ADD cycle varchar(25);
=> ALTER TABLE evosondage_admin ALTER column cycle SET not null;
=> ALTER TABLE evosondage_admin DROP column annee;
~~~

## Insertion de données

Insertion de tous les champs d'une table :

~~~
=> INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
~~~

Insertion en précisant les champs :

~~~
=> INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
~~~

Insertion à partir d'un fichier externe :

~~~
=> COPY weather FROM '/home/user/weather.txt';
~~~

Note : voir http://www.postgresql.org/docs/current/interactive/sql-copy.html

## Gestion des indexes

~~~
=> CREATE INDEX mytable_idx1 ON mytable(col1);
=> REINDEX TABLE mytable;
=> DROP INDEX mytable_idx1;
~~~

Pour réindexer une base de données complète :

~~~
$ psql mydb
=> REINDEX DATABASE mydb;
~~~

## Extraction de données

Rien ne vaut des exemples :

~~~
=> SELECT * FROM weather;
=> SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
=> SELECT * FROM weatherWHERE city = 'San Francisco' AND prcp > 0.0;
=> SELECT DISTINCT city FROM weather ORDER BY city;
~~~

Avec des jointures :

~~~
=> SELECT * FROM weather, cities WHERE city = name;
=> SELECT weather.city, weather.temp_lo, cities.location FROM weather, cities WHERE cities.name = weather.city;
=> SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
=> SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
=> SELECT * FROM weather w, cities c WHERE w.city = c.name;
~~~

Avec des fonctions (Aggregate Functions) :

~~~
=> SELECT max(temp_lo) FROM weather;
~~~

Attention, les "Aggregate Functions" ne peuvent être utilisées dans la clause WHERE.
Ainsi la requête suivante est fausse :

~~~
=> SELECT city FROM weather WHERE temp_lo = max(temp_lo);
~~~

On devra donc faire :

~~~
=> SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
~~~

On pourra bien sûr utilise _GROUP BY…_, _HAVING…_, etc.

Pour envoyer le résultat dans un fichier :

~~~
=> SELECT * FROM weather WHERE temp_lo=10 \g '/tmp/output'
~~~

## Mise à jour des données

Toujours avec un exemple :

~~~
=> UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';
~~~

## Suppression des données

Encore avec un exemple :

~~~
=> DELETE FROM weather WHERE city = 'Hayward';
~~~

Pour effacer toutes les données d'une table :

~~~
=> DELETE FROM weather;
~~~

## Schémas

Par défaut, PostgreSQL utilise le schéma *public* mais il est possible d'utiliser d'autres schémas.

* Créer un nouveau schéma :

~~~
=> CREATE SCHEMA foo;
~~~

* Lister les schémas d'une base :

~~~
=> \dn
~~~

* Lister les tables/indexes dans un schéma :

~~~
=> \dt foo.
=> \di foo.
~~~

## Développement

Voir <https://masteringpostgresql.com/>