Stocker du texte avec PostgreSQL
Trois types avec PostgreSQL
PostgreSQL propose 3 types de données pour stocker les chaînes de caractères : CHARACTER VARYING ou VARCHAR, CHARACTER ou CHAR, TEXT.
-
Le type CHARACTER VARYING(n) présent dans le standard SQL et également appelé VARCHAR(n) permet de stocker des chaînes de caractères de longueur variable comprenant au maximum n caractères. Si vous ne précisez pas n en déclarant une colonne de type CHARACTER VARYING ou VARCHAR alors vous pouvez y stocker une chaîne de caractères de taille "illimitée".
-
Le type CHARACTER(n) présent dans le standard SQL et également appelé CHAR(n) permet de stocker des chaînes de caractères de longueur fixe. Si vous ne précisez pas n alors n=1 : CHARACTER = CHAR = CHARACTER(1) = CHAR(1).
-
Enfin le type TEXT, hors standard SQL, permet de stocker des chaînes de caractères de longueur variable et de taille "illimitée".
Il est à noter que tous ces types sont implémentés de manière similaire. Un VARCHAR(n) est simplement un VARCHAR avec une contrainte de taille maximale et un CHAR ne présente pas de meilleures performances quun VARCHAR, il présente donc un intérêt limité.
Et si on part dOracle ?
Oracle stocke principalement les chaînes de caractéres dans des VARCHAR2, des CHAR, des CLOB et pour les versions les plus anciennes dans des LONG. Avant la version 12c la taille maximale dun VARCHAR2 sous Oracle était de 4000 octets. Depuis la 12c cette valeur peut être étendue à 32767 octets. Octets et pas caractères...ce qui signifie quavec un jeu de caractères stockant certains caractères sur plus dun octet comme AL32UTF8 (lUTF8 dOracle) vous pouvez obtenir :
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 1 17:09:48 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set lines 300
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
...
NLS_CHARACTERSET AL32UTF8
...
20 rows selected.
SQL> create table t1(c1 varchar2(4));
Table created.
SQL> insert into t1 values('éééé');
insert into t1 values('éééé')
*
ERROR at line 1:
ORA-12899: value too large for column "SYSTEM"."T1"."C1" (actual: 8, maximum:4)
SQL> create table t2(c1 varchar2(4 CHAR));
Table created.
SQL> insert into t2 values('éééé');
1 row created.
La dernière syntaxe VARCHAR2(n CHAR) permet de préciser que vous voulez stocker n CARACTÈRES mais cela ne vous permettra de toute façon pas de stocker plus de 4000 octets jusquà Oracle 11.2 et 32767 octets depuis Oracle 12.1. Au delà il faudra utiliser du CLOB.
Qui peut le plus peut le moins et tout ceci ne vous posera aucun problème avec PostgreSQL, un VARCHAR(n) permet de stocker n CARACTÈRES quel que soit le jeu de caractères :
postgres=# \l+
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description
-----------+--------------+----------+-----------------+--------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | | 6732 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +| 6409 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +| 6417 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 lignes)
postgres=# create table t1 (c1 varchar(4));
CREATE TABLE
postgres=# insert into t1 values('éééé');
INSERT 0 1
De manière basique vos CHAR(n) et VARCHAR2(n) dOracle deviendront avec PostgreSQL des CHARACTER VARYING (ou TEXT) ou encore des CHARACTER VARYING(n) si vous souhaitez conserver une limite de taille pour vos chaînes de caractères.
Les CLOB et LONG dOracle deviendront aussi des CHARACTER VARYING (ou TEXT) avec PostgreSQL.
Tout est parfait alors ?
Hmmmm, maître, il y a un ou deux nota bene, heu, deux ou trois heu quiproquos... Vous NE pouvez PAS stocker le caractêre NULL dans un des types de données CHAR, VARCHAR, TEXT de PostgreSQL. Si vous passez de SQL Server, MySQL ou Oracle à PostgreSQL il vous faudra par exemple transformer la chaîne de caractères en source pour éliminer le caractère NULL.
Exemple depuis Oracle, le caractère NULL est ici remplacé par un ESPACE :
SQL_ORACLE> create table t1(c1 varchar(32));
Table created.
SQL_ORACLE> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(32)
SQL_ORACLE> insert into t1 values('GAR' || unistr('\0000') || 'GAMEL');
1 row created.
SQL_ORACLE> commit;
Commit complete.
postgres=# CREATE EXTENSION ORACLE_FDW;
CREATE EXTENSION
postgres=# CREATE SERVER ORCL_TEST FOREIGN DATA WRAPPER ORACLE_FDW OPTIONS (dbserver '//SRVTEST:1521/DBTEST');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR POSTGRES SERVER ORCL_TEST OPTIONS (user 'TEST', password 'TEST');
CREATE USER MAPPING
postgres=#
postgres=# CREATE FOREIGN TABLE orcl_t1 (
postgres(# c1 varchar(32))
postgres-# SERVER ORCL_TEST OPTIONS(table 'T1');
CREATE FOREIGN TABLE
postgres=# select * from orcl_t1;
ERREUR: séquence d'octets invalide pour lencodage UTF8 : 0x00
CONTEXTE : converting column "c1" for foreign table scan of "orcl_t1", row 1
SQL_ORACLE> update t1 Set c1 = replace(c1, unistr('\0000'), ' ') ;
1 row updated.
SQL_ORACLE> commit;
Commit complete.
postgres=# select * from orcl_t1;
c1
-----------
GAR GAMEL
(1 ligne)
Encore quelque chose ?
Oui, attention lors de lindexation de vos colonnes de type CHAR, VARCHAR ou TEXT. Si vous utilisez la locale C et que vous créez ensuite vos bases par défaut alors rien à signaler. Mais si vous utilisez la locale que je vous recommande pour la France, fr_FR.UTF-8, alors vos recherches utilisant le mot clé "LIKE" peuvent ne pas tirer parti dun index alors que vous aviez prévu le contraire.
Exemple avec un cluster initialisé avec la locale C et des bases ensuite créées par défaut :
postgres=# \! locale
LANG=C
LANGUAGE=
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_PAPER="C"
LC_NAME="C"
LC_ADDRESS="C"
LC_TELEPHONE="C"
LC_MEASUREMENT="C"
LC_IDENTIFICATION="C"
LC_ALL=
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | SQL_ASCII | C | C | | 13 MB | pg_default | default administrative connection database
template0 | postgres | SQL_ASCII | C | C | =c/postgres +| 6409 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | SQL_ASCII | C | C | =c/postgres +| 6417 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
postgres=# create table t1(c1 text);
CREATE TABLE
postgres=# do
postgres-# $$
postgres$# begin
postgres$# for i in 1..100000 loop
postgres$# insert into t1(c1) values (upper(md5(random()::text)));
postgres$# end loop;
postgres$# end$$;
DO
postgres=# create index i1 on t1(c1);
CREATE INDEX
postgres=# explain select * from t1 where c1 like 'C2E%';
QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.50..34.25 rows=10 width=33)
Filter: (c1 ~~ 'C2E%'::text)
-> Bitmap Index Scan on i1 (cost=0.00..4.50 rows=8 width=0)
Index Cond: ((c1 >= 'C2E'::text) AND (c1 < 'C2F'::text))
(4 rows)
Exemple avec un cluster initialisé avec la locale fr_FR.UTF-8 et des bases ensuite créées par défaut :
ostgres=# \! locale
LANG=fr_FR.UTF-8
LANGUAGE=
LC_CTYPE="fr_FR.UTF-8"
LC_NUMERIC="fr_FR.UTF-8"
LC_TIME="fr_FR.UTF-8"
LC_COLLATE="fr_FR.UTF-8"
LC_MONETARY="fr_FR.UTF-8"
LC_MESSAGES="fr_FR.UTF-8"
LC_PAPER="fr_FR.UTF-8"
LC_NAME="fr_FR.UTF-8"
LC_ADDRESS="fr_FR.UTF-8"
LC_TELEPHONE="fr_FR.UTF-8"
LC_MEASUREMENT="fr_FR.UTF-8"
LC_IDENTIFICATION="fr_FR.UTF-8"
LC_ALL=
postgres=# \l+
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits daccès | Taille | Tablespace | Description
-----------+--------------+----------+-----------------+--------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | | 6732 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +| 6409 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +| 6417 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 lignes)
postgres=# create table t1(c1 text);
CREATE TABLE
postgres=# do
postgres-# $$
postgres$# begin
postgres$# for i in 1..100000 loop
postgres$# insert into t1(c1) values (upper(md5(random()::text)));
postgres$# end loop;
postgres$# end$$;
DO
postgres=# create index i1 on t1(c1);
CREATE INDEX
postgres=# select * from t1 fetch first 10 rows only;
c1
----------------------------------
7D5C1A5E8ECD732C610919A6DDFF5A90
CC6F69F1C8D4302DC3185132A97A4040
B035BD953EC7460B62E427E3F10B1B76
4A61B07CC2BD2B999505EA4CCE82EE89
FB775258A8F1E8EC1390AB163E3F61FD
A633B5EB61357C283EC4468C947600C6
852A6E8C14A90230F03300EBCE6CE133
E046BEAE07CF7A8D30EDF98675EF6542
C2EB9474C17F318C49473A29B47B2F1A
FC8D00A8E9B8A68EA2DDC8FED6B393E6
(10 lignes)
postgres=# explain select * from t1 where c1 = 'C2EB9474C17F318C49473A29B47B2F1A';
QUERY PLAN
-------------------------------------------------------------------
Index Only Scan using i1 on t1 (cost=0.42..8.44 rows=1 width=33)
Index Cond: (c1 = 'C2EB9474C17F318C49473A29B47B2F1A'::text)
(2 lignes)
postgres=# explain select * from t1 where c1 <= 'C2E';
QUERY PLAN
----------------------------------------------------------
Seq Scan on t1 (cost=0.00..2084.00 rows=76248 width=33)
Filter: (c1 <= 'C2E'::text)
(2 lignes)
postgres=# select count(*) from t1 where c1 <= 'C2E';
count
-------
76182
(1 ligne)
postgres=# explain select * from t1 where c1 >= 'C2E';
QUERY PLAN
-----------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=872.50..2003.40 rows=23752 width=33)
Recheck Cond: (c1 >= 'C2E'::text)
-> Bitmap Index Scan on i1 (cost=0.00..866.56 rows=23752 width=0)
Index Cond: (c1 >= 'C2E'::text)
(4 lignes)
postgres=# select count(*) from t1 where c1 >= 'C2E';
count
-------
23818
(1 ligne)
postgres=# explain select * from t1 where c1 like 'C2E%';
QUERY PLAN
-------------------------------------------------------
Seq Scan on t1 (cost=0.00..2084.00 rows=10 width=33)
Filter: (c1 ~~ 'C2E%'::text)
(2 lignes)
postgres=# select count(*) from t1 where c1 like 'C2E%';
count
-------
26
(1 ligne)
postgres=# create index i2 on t1(c1 text_pattern_ops);
CREATE INDEX
postgres=# explain select * from t1 where c1 like 'C2E%';
QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.66..88.75 rows=10 width=33)
Filter: (c1 ~~ 'C2E%'::text)
-> Bitmap Index Scan on i2 (cost=0.00..4.66 rows=24 width=0)
Index Cond: ((c1 ~>=~ 'C2E'::text) AND (c1 ~<~ 'C2F'::text))
(4 lignes)
En dehors de cas particuliers, si vous voulez tirer parti dun index pour des recherches de type LIKE ABC%, il vous faudra ajouter une classe dopérateur (text_pattern_ops, varchar_pattern_ops ou char_pattern_ops) lors de la création de lindex. À noter que si vous voulez aller plus loin PostgreSQL dispose de capacités natives pour les recherches plein texte exploitant des index de type GIN ou GIST.
Si vous voulez tirer parti dun index pour des recherches incluant des comparaisons >= <= > < il vous faudra créer en plus un index SANS classe dopérateur.
A noter que les deux types dindex peuvent être utilisés pour des comparaisons dégalité entre une chaîne et la colonne indexée.
Mise à jour : 01/02/2016