PostgreSQL 数据类型

Dans cette section, nous discuterons des types de données de PostgreSQL, les types de données étant ceux que nous définissons pour chaque champ lors de la création d'une table.

Les avantages de la définition du type de données :

PostgreSQL offre une riche gamme 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. Il y a de nombreux types de données dans PostgreSQL, nous allons les expliquer en détail ci-dessous.

Types numériques

Les types numériques sont composés de 2 Octets,4 Octet ou 8 Entier en octets ainsi que 4 Octet ou 8 Nombre à virgule flottante en octets et nombre décimal avec précision optionnelle composés.

Le tableau suivant liste les types numériques disponibles :

Type monétaire

Le type money stocke des montants monétaires avec une précision décimale fixe.

Les valeurs des types numeric, int et bigint peuvent être converties en money, il est déconseillé d'utiliser des nombres à virgule flottante pour traiter les types monétaires, car il existe un risque d'arrondi.

Types de caractères

Le tableau suivant liste les types de caractères pris en charge par PostgreSQL :

Date/Types de temps

Le tableau suivant liste les types de date et heure pris en charge par PostgreSQL.

Type boolean

PostgreSQL supporte le type de données boolean standard.

boolean a deux états : "true" (vrai) ou "false" (faux), et un troisième état "unknown" (inconnu), représenté par NULL.

Type d'énumération

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

Les types d'énumération dans PostgreSQL sont similaires aux types enum du langage C.

Les types d'énumération, contrairement aux autres types, doivent être créés à l'aide de la commande CREATE TYPE.

CREATE　TYPE　mood　AS　ENUM　('triste',　'bon',　'heureux');

La création de jours de la semaine peut être effectuée comme suit:

CREATE　TYPE　week　AS　ENUM　('Lun',　'Mar',　'Mer',　'Jeu',　'Ven',　'Sam',　'Dim');

Comme d'autres types, une fois créée, une type d'énumération peut être utilisée pour la définition de tables et de fonctions.

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

Type géométrique

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

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

Le type le plus basique : point. Il est la base des autres types.

type d'adresse réseau

PostgreSQL fournit des types de données pour stocker l'IPv4 de type IPv6 de type MAC Address.

Il est préférable d'utiliser ces types de données pour stocker des adresses réseau que les types de texte purs, car ces types fournissent des vérifications d'erreur d'entrée et des opérations et fonctionnalités spéciales.

lors du tri des types de données inet ou cidr, l'IPv4 l'adresse est toujours après l'IPv6 devant l'adresse, y compris celles encapsulées ou mappées sur l'IPv6 L'IPv4 adresse, par exemple ::10.2.3.4 ou ::ffff:10.4.3.2.

type de chaîne de bits

Une chaîne de bits est une séquence 1 et une chaîne de caractères composée de 0.

Les données de type bit doivent correspondre exactement à la longueur n, toute tentative de stocker des données plus courtes ou plus longues est incorrecte. Le type de données bit varying est un type variable de longueur maximale n; les chaînes plus longues sont rejetées. Écrire un bit sans longueur est équivalent à bit(1) signifie qu'il n'y a pas de limite de longueur.

type de recherche de texte

Recherche全文 consiste à trouver les documents correspondant à une requête dans une collection de documents naturels.

PostgreSQL fournit deux types de données pour soutenir la recherche全文

Type UUID

le type de données uuid est utilisé pour stocker RFC 4122, ISO/IEF 9834-8:2005 ainsi que les UUID (Universally Unique Identifier) définis par les normes associées. (Certains systèmes considèrent ce type de données comme un identificateur universellement unique, ou GUID.) Cet identificateur est généré par un algorithme 128 d'identificateur, ce qui rend impossible qu'un identificateur généré par le même algorithme dans d'autres modules soit identique. Par conséquent, pour un système distribué, cet identificateur fournit une meilleure garantie d'unicité que la séquence, car la séquence ne peut garantir l'unicité que dans une seule base de données.

Un UUID est écrit sous forme de séquence de nombres hexadécimaux minuscules, divisé en plusieurs groupes, en particulier un groupe8de nombres binaires+3groupes4de nombres binaires+un ensemble12de nombres binaires, au total 32 de nombres représentent 128 Un nombre de bits, un exemple d'UUID de ce type de norme est le suivant :

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

Type XML

Les types de données xml peuvent être utilisés pour stocker des données XML. L'avantage de stocker des données XML dans un type de données text est qu'il permet de vérifier la bonhomie structurelle des valeurs d'entrée, et il supporte également les vérifications de sécurité de type de fonctions. Pour utiliser ce type de données, il est nécessaire d'utiliser configure lors de la compilation. --with-libxml.

xml peut stocker un "document" bien formé défini par la norme XML, ainsi que par XMLDecl? content 定义的"内容"片段， 大致上，这意味着内容片段可以有多个顶级元素或字符节点。 xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。

创建XML值

使用函数 xmlparse: 来从字符数据产生 xml 类型的值：

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

JSON 类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

此外还有相关的函数来处理 json 数据：

数组类型

PostgreSQL 允许将字段定义成变长的多维数组。

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组

创建表的时候，我们可以声明数组，方式如下：

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

pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。

我们也可以使用 "ARRAY" 关键字，如下所示：

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

插入值

插入值使用花括号 {}，元素在 {} 使用逗号隔开：

INSERT INTO sal_emp
　　　　VALUES ('Bill',
　　　　{10000,　10000,　10000,　10000}
　　　　'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
　　　　VALUES ('Carol',
　　　　{2　25000,　25000,　25000}
　　　　'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Accès au tableau

Maintenant, nous pouvons exécuter des requêtes sur ce tableau.

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

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
　name
-------
　Carol
(1　row)

Les indices numériques des tableaux sont écrits entre crochets.

Modification de tableau

Nous pouvons modifier les valeurs des tableaux :

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

Ou utilisez la syntaxe du constructeur ARRAY :

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

Recherche dans un tableau

Pour rechercher une valeur dans un tableau, vous devez vérifier chaque élément 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;

De plus, vous pouvez utiliser la requête suivante pour trouver tous les éléments d'un tableau qui ont la valeur égale à 10000 lignes :

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;

Type composé

Un type composé représente la structure d'une ligne ou d'un enregistrement ; il s'agit en réalité d'une liste de noms de champs et de leurs types de données. PostgreSQL permet d'utiliser des types composés 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 composé.

Déclaration de type composé

Voici deux exemples simples de définition de types composés :

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, mais ici, seuls les noms de champs et les types peuvent être déclarés.

Défini le type, nous pouvons l'utiliser pour créer une table :

CREATE TABLE on_hand (
　　　　item inventory_item,
　　　　count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice',　42,　1.99),　1000);

Entrée de valeurs de type composite

Pour écrire une valeur de type composite en tant que constante de texte, enveloppez les valeurs de champ entre des encadrés en forme de cercle et séparez-les par des virgules. Vous pouvez mettre des guillemets doubles autour de toute valeur de champ, si la valeur elle-même contient des virgules ou des encadrés en forme de cercle, vous devez les envelopper dans des guillemets doubles.

Le format général des constantes de type composite est le suivant :

'( val1　, val2　, ... )'

Un exemple est :

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

Accès aux types composés

Pour accéder à un domaine d'un champ de type composite, nous écrivons un point suivi du nom du domaine, ce qui est très similaire à la sélection d'un champ à partir d'un nom de table. En réalité, car cela ressemble tellement à la sélection d'un champ à partir d'un nom de table, nous avons souvent besoin d'utiliser des encadrés pour éviter les ambiguïtés de l'analyseur. Par exemple, vous pourriez avoir besoin de sélectionner certains sous-domaines à partir de la table d'instances on_hand, comme suit :

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 écrire comme suit :

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

Ou si vous avez besoin d'utiliser des noms de tables (par exemple, dans une requête de jointure de plusieurs tables), écrivez ainsi :

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

Actuellement, l'objet d'encadrement en forme de cercle est correctement interprété comme une référence au champ item, puis on peut en extraire des sous-domaines.

Type de plage

Le type de données de plage représente les valeurs d'un type d'élément dans une plage déterminée.

Par exemple, la plage de timestamp peut être utilisée pour représenter la plage de temps d'une salle de réunion réservée.

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

• int4range — plage de integer

• int8range — plage de bigint

• numrange — plage numérique

• tsrange — plage de timestamp sans fuseau horaire

• tstzrange — plage de timestamp avec fuseau horaire

• daterange — plage de date

De plus, vous pouvez définir votre propre type de plage.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
　　　　(1108, '[2010-01-01　14:30,　2010-01-01　15:30)');
--　Inclut
SELECT int4range(10,　20) @>　3;
--　Chevauchement
SELECT numrange(11.1,　22.2) && numrange(20.0,　30.0);
--　Extraire la borne supérieure
SELECT upper(int8range(15,　25));
--　Calculer l'intersection
SELECT int4range(10,　20)　*　int4range(15,　25);
--　Est-ce que la plage est vide
SELECT isempty(numrange(1,　5));

L'entrée des valeurs de plage doit suivre le format suivant :

( borne inférieure, borne supérieure )
( borne inférieure, borne supérieure ]
[ borne inférieure, borne supérieure )
[ borne inférieure, borne supérieure ]
Vide

Les parenthèses圆圆括号 ou carrées montrent si la borne inférieure et la borne supérieure sont incluses ou non. Notez que le format final est vide, ce qui représente une plage vide (une plage sans valeur).

--　Inclut3Ne comprend pas7Et inclut tous les points entre eux
SELECT '[3,7)::int4range;
--　Ne comprend pas3Et7Mais inclut tous les points entre eux
SELECT '('3,7)::int4range;
--　Ne comprend qu'une seule valeur4
SELECT '[4,4]'::int4range;
--　Ne comprend pas de point (normalisé en 'vide')
SELECT '[4,4)::int4range;

Type d'identificateur d'objet

PostgreSQL utilise des identificateurs d'objet (OID) en interne comme clés primaires de diverses tables système.

En même temps, le système ne crée pas de champ OID système supplémentaire pour les 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 activé). Le type oid représente un identificateur d'objet. De plus, oid a plusieurs alias : regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig et regdictionary.

Types pseudonymes

Le système de types PostgreSQL contient une série d'entrées à usage spécial, appelées types pseudonymes. Les types pseudonymes ne peuvent pas être utilisés comme types de données de champ, mais ils peuvent être utilisés pour déclarer le type de paramètre ou de résultat d'une fonction. Les types pseudonymes sont très utiles lorsque une fonction ne fait que recevoir et renvoyer un type de données SQL.

Le tableau suivant liste tous les types pseudonymes :