Table des matières

PostgreSQL

Accueil
KB

PostgreSQL

Acces

sur le serveur se logger en postgres

<sudo> su postgres

lancer psql

\h pour les commandes sql \? pour les commandes psql

\q pour quitter \l pour lister les DBs

Créer une DB

On crée une base et un compte propriétaire de cette base.

Créer un role

createuser -D -R -l -E -e -P -U pgsql mon_user

man createuser pour en savoir plus …

ou avant faire un

su pgsql

voire

sudo -u pgsql createuser -D -R -l -E -e -P -U pgsql mon_user

De façon a avoir les droits associés au compte root du serveur (ici pgsql)

Entrer ensuite le password, puis répondre à cette question

Shall the new role be a superuser? (y/n)

>CREATE ROLE mon_user ENCRYPTED PASSWORD 'toto' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
-->CREATE ROLE

Créer la base

sudo createdb -E latin1 -O mon_user -e -U pgsql ma_db

ou plutôt

sudo createdb -E utf8 -O mon_user -e -U pgsql ma_db
>CREATE DATABASE ma_db OWNER mon_user ENCODING 'utf8';
-->CREATE DATABASE

Supprimer une DB

sudo dropdb -U mon_user ma_db

Quelques éléments de la syntaxe SQL de PostgreSQL.

Soit la table “mots”, avec les colonnes “id”, “mot”, “definition”

SELECT

Doc

Afficher tous les mots et toutes les colonnes :

SELECT * FROM mots;
ou encore
SELECT * FROM "mots";

Idem en affichant seulement la colonne id :

SELECT id FROM mots;
ou encore
SELECT id FROM "nom_du_shema"."mots";

L'enregistrement dont l'id est 15 (on affiche toutes les colonnes)

SELECT * FROM mots WHERE id = 15;

Les enregistrements dont le mot est "toto"

SELECT * FROM mots WHERE mot = 'toto';<br> ou<br> SELECT * FROM mots WHERE mot LIKE 'toto';

Les enregistrements dont le mot contient toto :

SELECT * FROM mots WHERE mots ~* 'toto'

Les enregistrements dont le mot commence par toto :

SELECT * FROM mots WHERE mots ~* '^toto'

Changer la valeur d'une sequence

Pour mettre la valeur actuelle de la sequence matable_monid_seq à 100 ALTER SEQUENCE matable_monid_seq RESTART WITH 100

Il peut être bien vu de faire regarder avant quelle est la plus grande valeur dans la table qui utilise la sequence. SELECT MAX(monid) FROM matable;

le SHOW COLUMNS sous Postgresql

SELECT

  a.attname AS field,
  pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
  a.attnotnull AS null,
  a.atthASdef AS default,
  adef.adsrc AS value,
  pg_catalog.col_description(a.attrelid, a.attnum) AS comment

FROM

  pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef
  ON a.attrelid=adef.adrelid
  AND a.attnum=adef.adnum

WHERE

  a.attrelid = (SELECT oid FROM pg_catalog.pg_clASs WHERE relname='ma_table'
      AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE
      nspname = 'public'))
  AND a.attnum > 0 AND NOT a.attisdropped

ORDER BY a.attnum

Majuscules et minuscules

LOWER(my_field) UPPER (my_field)

= Divers =

Migrer l'encodage vers UTF-8

Créer une nouvelle database en lui donnant l'encodage UTF8
Faire un export (dump) sans compression (en plain text) de l'ancienne DB
Dans le fichier de dump, couper / coller toutes les données (en principe encadrées par COPY) dans un nouveau fichier texte data.sql
couper coller ensuite toutes les contraintes (placées à la fin du fichier dump) dans un fichier constraints.sql
Sous Unix, lancer la commande :

iconv -f ancien_encodage -t utf-8 data.sql  > data_utf8.sql

(sous windows … bon courage)

Importer dans la nouvelle DB le shéma (en principe le fichier dump ne contient plus que le shéma)
Importer dans la nouvelle DB les données UTF-8
Importer dans la nouvelle DB les contraintes
Renommer l'ancienne DB
Renommer la nouvelle DB avec le nom de l'ancienne
Si vous utilisez un site web qui tape dans ces données, n'oubliez pas de préciser dans le head le nouvel encodage :