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.
| Name | Taille de stockage | Description | Range |
|---|---|---|---|
| smallint | 2 octets | Entier à petite plage | -32768 à +32767 |
| entier | 4 octets | Entier couramment utilisé | -2147483648 à +2147483647 |
| bigint | 8 octets | Entier à grande plage | -9223372036854775808 à +9223372036854775807 |
| decimal | Longueur variable | Précision spécifiée par l'utilisateur, exacte | 131072 chiffres avant la virgule ; 16383 chiffres après la virgule |
| numeric | Longueur variable | Précision spécifiée par l'utilisateur, exacte | 131072 chiffres avant la virgule ; 16383 chiffres après la virgule |
| real | 4 octets | Précision variable, inexacte | Précision de 6 chiffres décimaux |
| double precision | 8 octets | Précision variable, inexacte | Précision de 15 chiffres décimaux |
| smallserial | 2 octets | Petit entier auto-incrémenté | 1 à 32767 |
| serial | 4 octets | Entier auto-incrémenté | 1 à 2147483647 |
| bigserial | 8 octets | Grand 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.
| Name | Taille de stockage | Description | Range |
|---|---|---|---|
| money | 8 octets | Montant 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.
| Name | Taille de stockage | Description | Valeur minimale | Valeur maximale | Résolution |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 octets | Date et heure (sans fuseau horaire) | 4713 av. J.-C. | 294276 ap. J.-C. | 1 milliseconde / 14 chiffres |
| timestamp [ (p) ] with time zone | 8 octets | Date et heure, avec fuseau horaire | 4713 av. J.-C. | 294276 ap. J.-C. | 1 milliseconde / 14 chiffres |
| date | 4 octets | Date uniquement | 4713 av. J.-C. | 5874897 ap. J.-C. | 1 day |
| time [ (p) ] [ without time zone ] | 8 octets | Heure uniquement | 00:00:00 | 24:00:00 | 1 milliseconde / 14 chiffres |
| time [ (p) ] with time zone | 12 octets | Heure uniquement, avec fuseau horaire | 00:00:00+1459 | 24:00:00-1459 | 1 milliseconde / 14 chiffres |
| interval [ champs ] [ (p) ] | 12 octets | Intervalle de temps | -178000000 années | 178000000 années | 1 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.
| Name | Format de stockage | Description |
|---|---|---|
| booléen | 1 octet | true/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.
| Name | Taille de stockage | Description | Représentation |
|---|---|---|---|
| point | 16 octets | Point dans le plan | (x,y) |
| line | 32 bytes | Droite (infinie) (pas entièrement implémentée) | ((x1,y1),(x2,y2)) |
| lseg | 32 bytes | Segment de droite (fini) | ((x1,y1),(x2,y2)) |
| box | 32 bytes | Rectangle | ((x1,y1),(x2,y2)) |
| path | 16+16n bytes | Chemin fermé (semblable à un polygone) | ((x1,y1),...) |
| path | 16+16n bytes | Chemin ouvert | [(x1,y1),...] |
| polygon | 40+16n bytes | Polygone (similaire à un chemin fermé) | ((x1,y1),...) |
| circle | 24 bytes | circle | <(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.
| Name | Taille de stockage | Description |
|---|---|---|
| cidr | 7 ou 19 bytes | Réseau IPv4 ou IPv6 |
| inet | 7 ou 19 bytes | Hôte et réseau IPv4 ou IPv6 |
| macaddr | 6 octets | Adresse 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.
| Name | Référence | Description | Exemple |
|---|---|---|---|
| oid | any | identificateur d'objet numérique | 564182 |
| regproc | pg_proc | nom de fonction | sum |
| regprocedure | pg_proc | fonction avec types d'arguments | sum(int4) |
| regoper | pg_operator | nom d'opérateur | + |
| regoperator | pg_operator | opérateur avec types d'arguments | *(integer,integer) ou -(NONE,integer) |
| regclass | pg_class | nom de relation | pg_type |
| regtype | pg_type | nom de type de données | entier |
| regconfig | pg_ts_config | configuration de recherche de texte | anglais |
| regdictionary | pg_ts_dict | dictionnaire de recherche de texte | simple |
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 :
| Name | Description |
|---|---|
| any | Indique qu'une fonction accepte tout type de données d'entrée. |
| anyelement | Indique qu'une fonction accepte tout type de données. |
| anyarray | Indique qu'une fonction accepte tout type de données tableau. |
| anynonarray | Indique qu'une fonction accepte tout type de données non tableau. |
| anyenum | Indique qu'une fonction accepte n'importe quel type de données enum. |
| anyrange | Indique qu'une fonction accepte n'importe quel type de données range. |
| cstring | Indique qu'une fonction accepte ou renvoie une chaîne C terminée par un caractère nul. |
| internal | Indique qu'une fonction accepte ou renvoie un type de données interne au serveur. |
| language_handler | Un gestionnaire d'appel de langage procédural est déclaré pour renvoyer language_handler. |
| fdw_handler | Un wrapper de données distantes est déclaré pour renvoyer fdw_handler. |
| record | Identifie une fonction comme renvoyant un type de ligne non spécifié. |
| trigger | Une fonction de déclencheur est déclarée pour renvoyer trigger. |
| void | Indique qu'une fonction ne renvoie pas de valeur. |
| opaque | Un type obsolète, utilisé auparavant pour toutes les utilisations ci-dessus. |
Autres extensionsPour plus d'informations, consultez :Types de données PostgreSQL