PostgreSQL – Type de données

Dans ce chapitre, nous allons aborder les types de données utilisés dans PostgreSQL. Lors de la création d’une table, pour chaque colonne, vous spécifiez un type de données, c’est-à-dire, quel type de données vous voulez stocker dans les champs de la table.

Cela permet plusieurs avantages –

• Consistance – Les opérations contre les colonnes de même type de données donnent des résultats cohérents et sont généralement les plus rapides.

• Validation – L’utilisation appropriée des types de données implique la validation du format des données et le rejet des données hors du champ d’application du type de données.

• Compacité – Comme une colonne peut stocker un seul type de valeur, elle est stockée de manière compacte.

• Performance – L’utilisation appropriée des types de données donne le stockage le plus efficace des données. Les valeurs stockées peuvent être traitées rapidement, ce qui améliore les performances.

PostgreSQL supporte un large ensemble de types de données. En outre, les utilisateurs peuvent créer leur propre type de données personnalisé en utilisant la commande SQL CREATE TYPE. Il existe différentes catégories de types de données dans PostgreSQL. Ils sont discutés ci-dessous.

Types numériques

Les types numériques consistent en des entiers de deux octets, quatre octets et huit octets, des nombres à virgule flottante de quatre octets et huit octets, et des décimales à précision sélectionnable. Le tableau suivant répertorie les types disponibles.

Nom	Taille de stockage	Description	Gamme
smallint	2 bytes	small-gamme integer	-32768 à +32767
integer	4 octets	choix typique pour integer	-2147483648 à +2147483647
bigint	8 octets	entier de grande portée	-9223372036854775808 à 9223372036854775807
décimal	variable	précision spécifiée par l’utilisateur,exact	jusqu’à 131072 chiffres avant la virgule ; jusqu’à 16383 chiffres après la virgule
numérique	variable	précision spécifiée par l’utilisateur, exacte	jusqu’à 131072 chiffres avant la virgule ; jusqu’à 16383 chiffres après la virgule
réel	4 octets	précision variable,inexacte	6 chiffres décimaux précision
double précision	8 octets	précision variable,inexacte	15 chiffres décimaux précision
smallserial	2 octets	small autoincrémentation nombre entier	1 à 32767
série	4 octets	autoincrémentation nombre entier	1 à 2147483647
bigserial	8 octets	grand entier autoincrémenté	1 à 9223372036854775807

Types monétaires

Le type monétaire stocke un montant monétaire avec une précision fractionnaire fixe. Les valeurs des types de données numeric, int et bigint peuvent être coulées en money. L’utilisation de nombres à virgule flottante n’est pas recommandée pour gérer l’argent en raison du risque d’erreurs d’arrondi.

Nom	Taille de stockage	Description	Range
money	8 octets	montant de la monnaie	-92233720368547758.08 à +92233720368547758.07

Types de caractères

Le tableau ci-dessous liste les types de caractères à usage général disponibles dans PostgreSQL.

S. Non.	Nom &Description
1	caractère variable(n), varchar(n) longueur variable avec limite
2	caractère(n), char(n) longueur fixe, blanc padded
3	texte variable longueur illimitée

Types de données binaires

Le type de données bytea permet le stockage de chaînes binaires comme dans le tableau donné ci-dessous.

Nom	Taille de stockage	Description
bytea	1 ou 4 octets plus la chaîne binaire réelle	chaîne binaire de longueur variable	.chaîne binaire de longueur

Types de date/heure

PostgreSQL supporte un ensemble complet de types de date et d’heure SQL, comme le montre le tableau ci-dessous. Les dates sont comptées selon le calendrier grégorien. Ici, tous les types ont une résolution de 1 microseconde / 14 chiffres, sauf le type de date, dont la résolution est le jour.

.

Nom	Taille de stockage	Description	Valeur basse	Valeur haute
historique	8 octets	à la fois date et heure (sans fuseau horaire)	4713 BC	294276 AD
TIMESTAMPTZ	8 octets	à la fois date et heure, avec fuseau horaire	4713 BC	294276 AD
date	4 octets	date (sans heure du jour)	4713 BC	5874897 AD
time	8 octets	heure du jour (pas de date)	00 :00:00	24:00:00
heure avec fuseau horaire	12 octets	heure du jour seulement, avec fuseau horaire	00:00:00+1459	24:00 :00-1459
intervalle	12 octets	intervalle de temps	-178000000 années	178000000 années

Type booléen

PostgreSQL fournit le type SQL standard Booléen. Le type de données booléen peut avoir les états true, false, et un troisième état, unknown, qui est représenté par la valeur SQL null.

.

Nom	Taille de stockage	Description
booléen	1 octet	état de vrai ou faux

Type énuméré

Les types énumérés (enum) sont des types de données qui comprennent un ensemble statique, ensemble ordonné de valeurs. Ils sont équivalents aux types énumérés pris en charge dans un certain nombre de langages de programmation.

Contrairement aux autres types, les types énumérés doivent être créés à l’aide de la commande CREATE TYPE. Ce type est utilisé pour stocker un ensemble statique et ordonné de valeurs. Par exemple les directions de la boussole, c’est-à-dire NORD, SUD, EST et OUEST ou les jours de la semaine comme indiqué ci-dessous –

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

Les types énumérés, une fois créés, peuvent être utilisés comme tous les autres types.

Type géométrique

Les types de données géométriques représentent des objets spatiaux à deux dimensions. Le type le plus fondamental, le point, constitue la base de tous les autres types.

Nom	Taille de stockage	Représentation	Description
point	16 octets	Point sur un plan	(x,y)
ligne	32 octets	Ligne infinie (pas complètement implémentée)	((x1,y1),(x2,y2))
lseg	32 octets	Section de ligne infinie	((x1,y1),(x2,y2))
box	32 octets	Boîte rectangulaire	((x1,y1),(x2,y2))
chemin	16+16n octets	Chemin fermé (similaire au polygone)	((x1,y1),…)
chemin	16+16n octets	Chemin ouvert
polygone	40+16n	Polygone (similaire au chemin fermé)	((x1,y1),….)
cercle	24 octets	Cercle	<(x,y),r> (point central et rayon)

Type d’adresse réseau

PostgreSQL propose des types de données pour stocker les adresses IPv4, IPv6 et MAC. Il est préférable d’utiliser ces types plutôt que des types de texte brut pour stocker les adresses réseau, car ces types offrent une vérification des erreurs d’entrée et des opérateurs et fonctions spécialisés.

Nom	Taille de stockage	Description
cidr	7 ou 19 octets	Réseaux IPv4 et IPv6
inet	7 ou 19 bytes	Hôtes et réseaux IPv4 et IPv6
macaddr	6 bytes	Adresses MAC

Type de chaîne de bits

Les types de chaîne de bits sont utilisés pour stocker les masques de bits. Ils sont soit 0, soit 1. Il existe deux types de bits SQL : bit(n) et bit variant(n), où n est un nombre entier positif.

Type de recherche de texte

Ce type prend en charge la recherche de texte intégral, qui est l’activité de recherche dans une collection de documents en langage naturel pour localiser ceux qui correspondent le mieux à une requête. Il existe deux types de données pour cela –

S. No.	Nom &Description
1	tsvector C’est une liste triée de mots distincts qui ont été normalisés pour fusionner les différentes variantes d’un même mot, appelées « lexèmes ».
2	tsquery Ceci stocke les lexèmes qui doivent être recherchés, et les combine en honorant les opérateurs booléens & (AND), | (OR), et ! (NOT). Les parenthèses peuvent être utilisées pour imposer le regroupement des opérateurs.

UUID Type

Un UUID (Universally Unique Identifiers) s’écrit comme une séquence de chiffres hexadécimaux minuscules, en plusieurs groupes séparés par des tirets, plus précisément un groupe de huit chiffres, suivi de trois groupes de quatre chiffres, suivis d’un groupe de 12 chiffres, pour un total de 32 chiffres représentant les 128 bits.

Un exemple d’UUID est – 550e8400-e29b-41d4-a716-446655440000

Type XML

Le type de données XML peut être utilisé pour stocker des données XML. Pour stocker des données XML, vous devez d’abord créer des valeurs XML en utilisant la fonction xmlparse comme suit –

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title> <topics>...</topics></tutorial>')XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Type 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 présente l’avantage de vérifier que chaque valeur stockée est une valeur JSON valide. Il existe également des fonctions de support associées, qui peuvent être utilisées directement pour manipuler le type de données JSON comme suit .

Exemple	Exemple Résultat
array_to_json(‘{{1,5},{99,100}}’ ::int)	,]
row_to_json(row(1, ‘foo’))	{« f1 » :1, « f2 » : « foo »}

Type de tableau

PostgreSQL donne la possibilité de définir une colonne d’une table comme un tableau multidimensionnel de longueur variable. Des tableaux de tout type de base intégré ou défini par l’utilisateur, de type enum ou de type composite peuvent être créés.

Déclaration des tableaux

Un type de tableau peut être déclaré comme

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer, scheme text);

ou en utilisant le mot-clé « ARRAY » comme

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer ARRAY, scheme text);

Insertion de valeurs

Les valeurs d’un tableau peuvent être insérées comme une constante littérale, en enfermant les valeurs des éléments entre accolades et en les séparant par des virgules. Un exemple est présenté ci-dessous –

INSERT INTO monthly_savings VALUES ('Manisha', '{20000, 14600, 23500, 13250}', '{{"FD", "MF"}, {"FD", "Property"}}'); 

Accéder aux tableaux

Un exemple d’accès aux tableaux est présenté ci-dessous. La commande donnée ci-dessous sélectionnera les personnes dont l’épargne est plus importante au deuxième trimestre qu’au quatrième trimestre.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modifier des tableaux

Un exemple de modification de tableaux est donné ci-dessous.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';

ou en utilisant la syntaxe d’expression ARRAY –

UPDATE monthly_savings SET saving_per_quarter = ARRAYWHERE name = 'Manisha';

Recherche de tableaux

Un exemple de recherche de tableaux est présenté ci-dessous.

SELECT * FROM monthly_savings WHERE saving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000;

Si la taille du tableau est connue, la méthode de recherche donnée ci-dessus peut être utilisée. Sinon, l’exemple suivant montre comment rechercher lorsque la taille n’est pas connue.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Types composites

Ce type représente une liste de noms de champs et leurs types de données, c’est-à-dire, structure d’une ligne ou d’un enregistrement d’une table.

Déclaration de types composites

L’exemple suivant montre comment déclarer un type composite

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

Ce type de données peut être utilisé dans les tables de création comme ci-dessous –

CREATE TABLE on_hand ( item inventory_item, count integer);

Saisie de valeurs composites

Les valeurs composites peuvent être insérées comme une constante littérale, en enfermant les valeurs de champ entre parenthèses et en les séparant par des virgules. Un exemple est présenté ci-dessous –

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

Ceci est valable pour l’inventaire_item défini ci-dessus. Le mot-clé ROW est en fait facultatif tant que vous avez plus d’un champ dans l’expression.

Accéder aux types composites

Pour accéder à un champ d’une colonne composite, utilisez un point suivi du nom du champ, un peu comme pour sélectionner un champ à partir d’un nom de table. Par exemple, pour sélectionner certains sous-champs de notre table d’exemple on_hand, la requête serait comme indiqué ci-dessous –

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

Vous pouvez même utiliser le nom de la table également (par exemple dans une requête multitable), comme ceci –

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

Types de plage

Les types de plage représentent les types de données qui utilisent une plage de données. Le type de plage peut être des plages discrètes (par exemple, toutes les valeurs entières de 1 à 10) ou des plages continues (par exemple, tout point dans le temps entre 10:00 et 11:00).

Les types de plages intégrées disponibles comprennent les plages suivantes :

.dans les types de plages disponibles incluent les plages suivantes –

• int4range – Plage d’entier

• int8range – Plage de bigint

• numrange – Plage de numérique

• tsrange – Plage d’horodatage sans fuseau horaire

• tstzrange – Plage d’horodatage avec fuseau horaire

• daterange – Plage de date

Des types de plage personnalisés peuvent être créés pour rendre de nouveaux types de plage disponibles, comme des plages d’adresses IP utilisant le type inet comme base, ou des plages de valeurs flottantes utilisant le type de données float comme base.

Les types de plage prennent en charge les limites de plage inclusives et exclusives en utilisant les caractères et ( ), respectivement. Par exemple, ‘[4,9)’ représente tous les entiers à partir de et incluant 4 jusqu’à mais n’incluant pas 9.

Types d’identificateurs d’objets

Les identificateurs d’objets (OID) sont utilisés en interne par PostgreSQL comme clés primaires pour diverses tables système. Si WITH OIDS est spécifié ou si la variable de configuration default_with_oids est activée, alors seulement, dans ce cas, les OID sont ajoutés aux tables créées par l’utilisateur. Le tableau suivant répertorie plusieurs types d’alias. Les types d’alias OID n’ont pas d’opérations propres, à l’exception des routines d’entrée et de sortie spécialisées.

.

.

.

.

Nom		Description	Valeur Exemple
oid	any	numeric identificateur d’objet	564182
regproc	pg_proc	nom de la fonction	sum
regprocédure	pg_proc	fonction avec types d’arguments	sum(int4)
regoper	pg_operator	Nom de l’opérateur	+
regoperator	pg_operator	opérateur avec des types d’arguments	*(integer,entier) ou -(NONE,entier)
regclass	pg_class	nom de la relation	pg_type
regtype	pg_type	nom du type de données	intégral
regconfig	pg_ts_config	configuration de la recherche textuelle	Anglais
regdictionary	pg_ts_dict	dictionnaire de recherche textuelle	simple

Pseudo Types

Le système de types de PostgreSQL contient un certain nombre d’entrées à usage spécial.purpose entries that are collectively called pseudo-types. Un pseudo-type ne peut pas être utilisé comme type de données de colonne, mais il peut être utilisé pour déclarer le type d’argument ou de résultat d’une fonction.

Le tableau donné ci-dessous liste les pseudo-types existants.

S. No.	Nom & Description
1	any Indique qu’une fonction accepte n’importe quel type de données en entrée.
2	anyelement Indique qu’une fonction accepte tout type de données.
3	anyarray Indique qu’une fonction accepte tout type de données de tableau.
4	anynonarray Indique qu’une fonction accepte tout type de données non-réseau.
5	anyenum Indique qu’une fonction accepte tout type de données enum.
6	anyrange Indique qu’une fonction accepte tout type de données de plage.
7	cstring Indique qu’une fonction accepte ou renvoie une chaîne C à terminaison nulle.
8	internal Indique qu’une fonction accepte ou renvoie un type de données interne au serveur.
9	language_handler Un gestionnaire d’appel de langage procédural est déclaré pour retourner language_handler.
10	fdw_handler Un gestionnaire d’emballage de données étrangères est déclaré pour retourner fdw_handler.
11	record Identifie une fonction retournant un type de ligne non spécifié.
12	trigger Une fonction de déclenchement est déclarée pour retourner trigger.
13	void Indique qu’une fonction ne retourne aucune valeur.

.