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.

      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 qu’un VARCHAR, il présente donc un intérêt limité.

Et si on part d’Oracle ?

      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 d’un 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 qu’avec un jeu de caractères stockant certains caractères sur plus d’un octet comme AL32UTF8 (l’UTF8 d’Oracle) 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) d’Oracle 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 d’Oracle 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 l’encodage 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 l’indexation 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 d’un 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 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 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 d’un index pour des recherches de type LIKE ’ABC%’, il vous faudra ajouter une classe d’opérateur (text_pattern_ops, varchar_pattern_ops ou char_pattern_ops) lors de la création de l’index. À 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 d’un index pour des recherches incluant des comparaisons >= <= > < il vous faudra créer en plus un index SANS classe d’opérateur.
      A noter que les deux types d’index peuvent être utilisés pour des comparaisons d’égalité entre une chaîne et la colonne indexée.

Mise à jour : 01/02/2016