Types de données PostgreSQL

Dans ce chapitre, nous allons discuter des types de données PostgreSQL. Les types de données sont ce que nous définissons pour chaque champ lors de la création d'une table.

Avantages de la définition des types de données :

PostgreSQL fournit un riche ensemble de types de données. Les utilisateurs peuvent utiliser la commande CREATE TYPE pour créer de nouveaux types de données dans la base de données. PostgreSQL dispose de nombreux types de données, et nous les expliquerons en détail ci-dessous.


Types numériques

Les types numériques comprennent des entiers de deux, quatre ou huit octets, des nombres à virgule flottante de quatre ou huit octets et des décimaux à précision sélectionnable.

Le tableau suivant répertorie les types numériques disponibles.

NameTaille de stockageDescriptionRange
smallint2 octetsEntier à petite plage-32768 à +32767
entier4 octetsEntier couramment utilisé-2147483648 à +2147483647
bigint8 octetsEntier à grande plage-9223372036854775808 à +9223372036854775807
decimalLongueur variablePrécision spécifiée par l'utilisateur, exacte131072 chiffres avant la virgule ; 16383 chiffres après la virgule
numericLongueur variablePrécision spécifiée par l'utilisateur, exacte131072 chiffres avant la virgule ; 16383 chiffres après la virgule
real4 octetsPrécision variable, inexactePrécision de 6 chiffres décimaux
double precision8 octetsPrécision variable, inexactePrécision de 15 chiffres décimaux
smallserial2 octetsPetit entier auto-incrémenté1 à 32767
serial4 octetsEntier auto-incrémenté1 à 2147483647
bigserial8 octetsGrand entier auto-incrémenté1 à 9223372036854775807

Types monétaires

Le type money stocke les montants monétaires avec une précision fractionnaire fixe.

Les valeurs des types numeric, int et bigint peuvent être converties en money. L'utilisation de nombres à virgule flottante pour gérer les types monétaires n'est pas recommandée, en raison de la possibilité d'erreurs d'arrondi.

NameTaille de stockageDescriptionRange
money8 octetsMontant monétaire-92233720368547758.08 à +92233720368547758.07

Types de caractères

Le tableau suivant répertorie les types de caractères pris en charge par PostgreSQL :

Non. Nom et description
1

character varying(n), varchar(n)

Longueur variable avec limite

2

character(n), char(n)

Longueur fixe, complétée par des espaces

3

text

Longueur variable, sans limite de longueur


Types date/heure

Le tableau suivant répertorie les types de date et d'heure pris en charge par PostgreSQL.

NameTaille de stockageDescriptionValeur minimaleValeur maximaleRésolution
timestamp [ (p) ] [ without time zone ]8 octetsDate et heure (sans fuseau horaire)4713 av. J.-C.294276 ap. J.-C.1 milliseconde / 14 chiffres
timestamp [ (p) ] with time zone8 octetsDate et heure, avec fuseau horaire4713 av. J.-C.294276 ap. J.-C.1 milliseconde / 14 chiffres
date4 octetsDate uniquement4713 av. J.-C.5874897 ap. J.-C.1 day
time [ (p) ] [ without time zone ]8 octetsHeure uniquement00:00:0024:00:001 milliseconde / 14 chiffres
time [ (p) ] with time zone12 octetsHeure uniquement, avec fuseau horaire00:00:00+145924:00:00-14591 milliseconde / 14 chiffres
interval [ champs ] [ (p) ]12 octetsIntervalle de temps-178000000 années178000000 années1 milliseconde / 14 chiffres

Types booléens

PostgreSQL prend en charge le type de données booléen standard.

Le type booléen a deux états, "true" (true) ou "false" (false), et un troisième état "unknown" (unknown), représenté par NULL.

NameFormat de stockageDescription
booléen1 octettrue/false

Types énumérés

Un type énuméré est un type de données qui contient un ensemble ordonné de valeurs statiques.

Les types énumérés dans PostgreSQL sont similaires aux types enum du langage C.

Contrairement aux autres types, les types énumérés doivent être créés à l'aide de la commande CREATE TYPE.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Créez les jours d'une semaine, comme indiqué ci-dessous :

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Comme les autres types, une fois créés, les types énumérés peuvent être utilisés dans les définitions de tables et de fonctions.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

Types géométriques

Les types de données géométriques représentent des objets plans bidimensionnels.

Le tableau suivant répertorie les types géométriques pris en charge par PostgreSQL.

Le type le plus élémentaire : point. Il est le fondement des autres types.

NameTaille de stockageDescriptionReprésentation
point16 octetsPoint dans le plan(x,y)
line32 bytesDroite (infinie) (pas entièrement implémentée)((x1,y1),(x2,y2))
lseg32 bytesSegment de droite (fini)((x1,y1),(x2,y2))
box32 bytesRectangle((x1,y1),(x2,y2))
path16+16n bytesChemin fermé (semblable à un polygone)((x1,y1),...)
path16+16n bytesChemin ouvert[(x1,y1),...]
polygon40+16n bytesPolygone (similaire à un chemin fermé)((x1,y1),...)
circle24 bytescircle<(x,y),r> (centre et rayon)

Types d'adresses réseau

PostgreSQL fournit des types de données pour stocker les adresses IPv4, IPv6 et MAC.

Le stockage d'adresses réseau avec ces types de données est préférable à l'utilisation de types texte brut, car ces types fournissent une vérification des erreurs de saisie et des opérations et fonctions spéciales.

NameTaille de stockageDescription
cidr7 ou 19 bytesRéseau IPv4 ou IPv6
inet7 ou 19 bytesHôte et réseau IPv4 ou IPv6
macaddr6 octetsAdresse MAC

Lors du tri des types de données inet ou cidr, les adresses IPv4 sont toujours triées avant les adresses IPv6, y compris les adresses IPv4 encapsulées ou mappées dans des adresses IPv6, telles que ::10.2.3.4 ou ::ffff:10.4.3.2.


Types de chaînes de bits

Une chaîne de bits est une chaîne de 1 et de 0. Elles peuvent être utilisées pour stocker et visualiser des masques de bits. Nous avons deux types SQL de bits : bit(n) et bit varying(n), où n est un entier positif.

Les données du type bit doivent correspondre exactement à la longueur n ; toute tentative de stockage de données plus courtes ou plus longues est une erreur. Les données du type bit varying sont d'un type de longueur variable avec une longueur maximale de n ; les chaînes plus longues seront rejetées. Écrire un bit sans longueur équivaut à bit(1), et bit varying sans longueur signifie aucune limite de longueur.


Types de recherche textuelle

La recherche plein texte est la récupération de documents à partir d'une collection de documents en langage naturel qui correspondent à une requête.

PostgreSQL fournit deux types de données pour prendre en charge la recherche plein texte :

Number Nom et description
1

tsvector

La valeur de tsvector est une liste triée de lexèmes distincts, c'est-à-dire des formes normalisées de différentes variantes du même mot.

2

tsquery

tsquery stocke les lexèmes utilisés pour la recherche et les combine à l'aide des opérateurs booléens & (AND), | (OR) et ! (NOT) ; les parenthèses sont utilisées pour imposer le regroupement des opérateurs.


Type UUID

Le type de données uuid sert à stocker des identifiants uniques universels (UUID) tels que définis par la RFC 4122, ISO/IEF 9834-8:2005 et les normes associées. (Certains systèmes appellent ce type de données un identifiant globalement unique, ou GUID.) Cet identifiant est un identifiant de 128 bits produit par un algorithme, ce qui rend impossible qu'il soit identique à des identifiants produits par d'autres moyens parmi des modules connus pour utiliser le même algorithme. Par conséquent, pour les systèmes distribués, cet identifiant offre une meilleure garantie d'unicité que les séquences, car les séquences ne peuvent garantir l'unicité que dans une seule base de données.

Les UUID s'écrivent sous la forme d'une séquence de chiffres hexadécimaux minuscules, divisée en groupes par des traits d'union, précisément un groupe de 8 chiffres plus 3 groupes de 4 chiffres plus un groupe de 12 chiffres, soit 32 chiffres représentant 128 bits. Un exemple d'un tel UUID standard est le suivant :

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

Type XML

Le type de données xml peut être utilisé pour stocker des données XML. Son avantage par rapport au stockage de données XML dans le type text est qu'il peut vérifier que les valeurs d'entrée sont bien formées, et il prend également en charge des fonctions pour les vérifier de manière sûre du point de vue du type. Pour utiliser ce type de données, vous devez l'utiliser au moment de la compilation.configure --with-libxml。

xml peut stocker des "documents" bien formés définis par la norme XML, ainsi que des fragments de "contenu" définis parXMLDecl? contentde la norme XML ; en gros, cela signifie que les fragments de contenu peuvent avoir plusieurs éléments de niveau supérieur ou nœuds de caractères. L'expression xml value IS DOCUMENT peut être utilisée pour déterminer si une valeur xml particulière est un document complet ou un fragment de contenu.

Création de valeurs XML

Utilisez la fonction xmlparse: pour produire des valeurs de type xml à partir de données de caractères :

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

Types JSON

Le type de données json peut être utilisé pour stocker des données JSON (JavaScript Object Notation). Ces données peuvent également être stockées sous forme de texte, mais le type de données json est plus avantageux pour vérifier que chaque valeur stockée est une valeur JSON valide.

En outre, il existe des fonctions associées pour traiter les données json :

Exemple Exemple de résultat
array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}

Types de tableau

PostgreSQL permet de définir des champs comme des tableaux multidimensionnels de longueur variable.

Les types de tableau peuvent être n'importe quel type de base ou type défini par l'utilisateur, type énuméré, ou type composite.

Déclaration de tableaux

Lors de la création d'une table, nous pouvons déclarer des tableaux comme suit :

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter est un tableau d'entiers unidimensionnel, et schedule est un tableau de texte bidimensionnel.

Nous pouvons également utiliser le mot-clé « ARRAY », comme indiqué ci-dessous :

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

Insertion de valeurs

Pour insérer des valeurs, utilisez des accolades {}, avec les éléments séparés par des virgules à l'intérieur de {} :

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Accès aux tableaux

Nous pouvons maintenant exécuter quelques requêtes sur cette table.

Tout d'abord, nous montrons comment accéder à un seul élément d'un tableau. Cette requête récupère les noms des employés dont les salaires ont changé au cours du deuxième trimestre :

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

Les indices d'un tableau sont écrits entre crochets.

Modification des tableaux

Nous pouvons modifier les valeurs d'un tableau :

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

Ou utiliser la syntaxe du constructeur ARRAY :

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Recherche dans les tableaux

Pour rechercher une valeur dans un tableau, vous devez vérifier chaque valeur du tableau.

Par exemple :

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

Vous pouvez également utiliser l'instruction suivante pour trouver les lignes où toutes les valeurs des éléments du tableau sont égales à 10000 :

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Ou vous pouvez utiliser la fonction generate_subscripts. Par exemple :

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Types composites

Un type composite représente la structure d'une ligne ou d'un enregistrement ; il s'agit en fait simplement d'une liste de noms de champs et de leurs types de données. PostgreSQL permet d'utiliser les types composites de la même manière que les types de données simples. Par exemple, un champ d'une table peut être déclaré comme un type composite.

Déclaration de types composites

Voici deux exemples simples de définition de types composites :

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

La syntaxe est similaire à CREATE TABLE, sauf qu'ici seuls les noms de champs et les types peuvent être déclarés.

Une fois les types définis, nous pouvons les utiliser pour créer des tables :

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Saisie de valeur de type composite

Pour écrire une valeur de type composite sous forme de constante textuelle, placez les valeurs des champs entre parenthèses et séparez-les par des virgules. Vous pouvez mettre des guillemets doubles autour de n'importe quelle valeur de champ ; si la valeur elle-même contient des virgules ou des parenthèses, vous devez la placer entre guillemets doubles.

Le format général d'une constante de type composite est le suivant :

'( val1 , val2 , ... )'

Voici un exemple :

'("fuzzy dice",42,1.99)'

Accès aux types composites

Pour accéder à un champ d'une colonne de type composite, nous écrivons un point et le nom du champ, un peu comme pour sélectionner un champ à partir d'un nom de table. En fait, parce que cela ressemble tellement à la sélection d'un champ à partir d'un nom de table, nous devons souvent utiliser des parenthèses pour éviter de confondre l'analyseur syntaxique. Par exemple, vous pourriez avoir besoin de sélectionner certains sous-champs de la table d'exemple on_hand, comme ceci :

SELECT item.name FROM on_hand WHERE item.price > 9.99;

Cela ne fonctionnera pas, car selon la syntaxe SQL, item est sélectionné à partir d'un nom de table, et non d'un nom de champ. Vous devez l'écrire comme ceci :

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Ou, si vous devez également utiliser le nom de la table (par exemple, dans une requête multi-tables), écrivez-le comme ceci :

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Maintenant, l'objet entre parenthèses est correctement analysé comme une référence au champ item, et vous pouvez ensuite sélectionner des sous-champs à partir de celui-ci.


Types de plages

Les types de données de plage représentent une certaine plage de valeurs d'un certain type d'élément.

Par exemple, une plage de timestamp peut être utilisée pour représenter la plage horaire pendant laquelle une salle de réunion est réservée.

Les types de plage intégrés dans PostgreSQL sont :

  • int4range — plage d'entiers

  • int8range — plage de bigint

  • numrange — plage de numeric

  • tsrange — plage de timestamp sans fuseau horaire

  • tstzrange — plage de timestamp avec fuseau horaire

  • daterange — plage de date

De plus, vous pouvez définir vos propres types de plage.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

La saisie des valeurs de plage doit suivre le format suivant :

(lower bound, upper bound)
(lower bound, upper bound]
[lower bound, upper bound)
[lower bound, upper bound]
empty

Les parenthèses ou les crochets indiquent si les bornes inférieure et supérieure sont exclusives ou inclusives. Notez que le dernier format est vide, représentant une plage vide (une plage qui ne contient aucune valeur).

-- 包括3,不包括7,并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7,但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点(被标准化为‘空’)
SELECT '[4,4)'::int4range;

Types d'identificateurs d'objets

PostgreSQL utilise en interne des identificateurs d'objets (OIDs) comme clés primaires pour diverses tables système.

En même temps, le système n'ajoutera pas de champ système OID aux tables créées par l'utilisateur (sauf si WITH OIDS est déclaré lors de la création de la table, ou si le paramètre de configuration default_with_oids est défini sur on). Le type oid représente un identificateur d'objet. De plus, oid possède plusieurs alias : regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig et regdictionary.

NameRéférenceDescriptionExemple
oidanyidentificateur d'objet numérique564182
regprocpg_procnom de fonctionsum
regprocedurepg_procfonction avec types d'argumentssum(int4)
regoperpg_operatornom d'opérateur+
regoperatorpg_operatoropérateur avec types d'arguments*(integer,integer) ou -(NONE,integer)
regclasspg_classnom de relationpg_type
regtypepg_typenom de type de donnéesentier
regconfigpg_ts_configconfiguration de recherche de texteanglais
regdictionarypg_ts_dictdictionnaire de recherche de textesimple

Pseudo-types

Le système de types PostgreSQL contient un certain nombre d'entrées à usage spécial qui sont collectivement appelées pseudo-types. Les pseudo-types ne peuvent pas être utilisés comme types de données de champs, mais ils peuvent être utilisés pour déclarer le type d'argument ou de résultat d'une fonction. Les pseudo-types sont utiles lorsqu'une fonction n'accepte pas et ne renvoie pas simplement un certain type de données SQL.

Le tableau suivant liste tous les pseudo-types :

NameDescription
anyIndique qu'une fonction accepte tout type de données d'entrée.
anyelementIndique qu'une fonction accepte tout type de données.
anyarrayIndique qu'une fonction accepte tout type de données tableau.
anynonarrayIndique qu'une fonction accepte tout type de données non tableau.
anyenumIndique qu'une fonction accepte n'importe quel type de données enum.
anyrangeIndique qu'une fonction accepte n'importe quel type de données range.
cstringIndique qu'une fonction accepte ou renvoie une chaîne C terminée par un caractère nul.
internalIndique qu'une fonction accepte ou renvoie un type de données interne au serveur.
language_handlerUn gestionnaire d'appel de langage procédural est déclaré pour renvoyer language_handler.
fdw_handlerUn wrapper de données distantes est déclaré pour renvoyer fdw_handler.
recordIdentifie une fonction comme renvoyant un type de ligne non spécifié.
triggerUne fonction de déclencheur est déclarée pour renvoyer trigger.
voidIndique qu'une fonction ne renvoie pas de valeur.
opaqueUn type obsolète, utilisé auparavant pour toutes les utilisations ci-dessus.

Pour plus d'informations, consultez :Types de données PostgreSQL

Autres extensions