PostgreSQL - Type de données
Dans ce chapitre, nous discuterons des types de données utilisés dans PostgreSQL. Lors de la création de la table, pour chaque colonne, vous spécifiez un type de données, c'est-à-dire le type de données que vous souhaitez stocker dans les champs de la table.
Cela permet plusieurs avantages -
Consistency - Les opérations sur des colonnes du même type de données donnent des résultats cohérents et sont généralement les plus rapides.
Validation - Une utilisation appropriée des types de données implique la validation du format des données et le rejet des données en dehors de la portée du type de données.
Compactness - Comme une colonne peut stocker un seul type de valeur, elle est stockée de manière compacte.
Performance- Une utilisation correcte des types de données permet un stockage des données le plus efficace. Les valeurs stockées peuvent être traitées rapidement, ce qui améliore les performances.
PostgreSQL prend en charge un large ensemble de types de données. En outre, les utilisateurs peuvent créer leur propre type de données personnalisé à l'aide de 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 se composent d'entiers de deux, quatre et huit octets, de nombres à virgule flottante de quatre et huit octets et de décimales à précision sélectionnable. Le tableau suivant répertorie les types disponibles.
| Nom | Taille de stockage | La description | Intervalle |
|---|---|---|---|
| smallint | 2 octets | entier petit intervalle | -32768 à +32767 |
| entier | 4 octets | choix typique pour entier | -2147483648 à +2147483647 |
| bigint | 8 octets | entier large | -9223372036854775808 à 9223372036854775807 |
| décimal | variable | précision spécifiée par l'utilisateur, exacte | jusqu'à 131072 chiffres avant la virgule décimale; jusqu'à 16383 chiffres après la virgule décimale |
| numérique | variable | précision spécifiée par l'utilisateur, exacte | jusqu'à 131072 chiffres avant la virgule décimale; jusqu'à 16383 chiffres après la virgule décimale |
| réel | 4 octets | précision variable, inexacte | Précision à 6 chiffres décimaux |
| double precision | 8 octets | précision variable, inexacte | Précision de 15 chiffres décimaux |
| petite série | 2 octets | petit entier auto-incrémenté | 1 à 32767 |
| en série | 4 octets | entier auto-incrémenté | 1 à 2147483647 |
| grande série | 8 octets | grand entier auto-incrémenté | 1 à 9223372036854775807 |
Types monétaires
Le type de monnaie stocke un montant en devise avec une précision fractionnaire fixe. Les valeurs des types de données numeric, int et bigint peuvent être converties en argent . 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 | La description | Intervalle |
|---|---|---|---|
| argent | 8 octets | montant en devise | -92233720368547758.08 au +92233720368547758.07 |
Types de caractères
Le tableau ci-dessous répertorie les types de caractères à usage général disponibles dans PostgreSQL.
| S. Non. | Nom et description |
|---|---|
| 1 | character varying(n), varchar(n) longueur variable avec limite |
| 2 | character(n), char(n) longueur fixe, rembourré blanc |
| 3 | text longueur illimitée variable |
Types de données binaires
Le type de données bytea permet le stockage de chaînes binaires comme dans le tableau ci-dessous.
| Nom | Taille de stockage | La description |
|---|---|---|
| bytea | 1 ou 4 octets plus la chaîne binaire réelle | chaîne binaire de longueur variable |
Types de date / heure
PostgreSQL prend en charge un ensemble complet de types de date et d'heure SQL, comme indiqué dans le tableau ci-dessous. Les dates sont comptées selon le calendrier grégorien. Ici, tous les types ont une résolution de1 microsecond / 14 digits sauf date type, dont la résolution est day.
| Nom | Taille de stockage | La description | Faible valeur | Haute valeur |
|---|---|---|---|---|
| horodatage [(p)] [sans fuseau horaire] | 8 octets | la date et l'heure (pas de fuseau horaire) | 4713 avant JC | 294276 AD |
| TIMESTAMPTZ | 8 octets | date et heure, avec fuseau horaire | 4713 avant JC | 294276 AD |
| Date | 4 octets | date (aucune heure de la journée) | 4713 avant JC | 5874897 AD |
| heure [(p)] [sans fuseau horaire] | 8 octets | heure de la journée (sans date) | 00:00:00 | 24:00:00 |
| heure [(p)] avec fuseau horaire | 12 octets | heures de la journée uniquement, avec fuseau horaire | 00: 00: 00 + 1459 | 24: 00: 00-1459 |
| intervalle [champs] [(p)] | 12 octets | intervalle de temps | -178000000 ans | 178000000 ans |
Type booléen
PostgreSQL fournit le type SQL standard Boolean. Le type de données Boolean peut avoir les états vrai , faux et un troisième état, inconnu , qui est représenté par la valeur SQL null.
| Nom | Taille de stockage | La description |
|---|---|---|
| booléen | 1 octet | état de vrai ou faux |
Type énuméré
Les types énumérés (énumération) sont des types de données qui comprennent un ensemble de valeurs statiques et ordonnées. Ils sont équivalents aux types enum 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 de valeurs statiques et ordonnées. Par exemple, les directions de la boussole, c'est-à-dire NORD, SUD, EST et OUEST ou jours de la semaine comme indiqué ci-dessous -
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');Enumerated, une fois créé, peut être utilisé comme n'importe quel autre type.
Type géométrique
Les types de données géométriques représentent des objets spatiaux bidimensionnels. Le type le plus fondamental, le point, forme la base de tous les autres types.
| Nom | Taille de stockage | Représentation | La description |
|---|---|---|---|
| point | 16 octets | Point sur un avion | (x, y) |
| ligne | 32 octets | Ligne infinie (pas entièrement implémentée) | ((x1, y1), (x2, y2)) |
| lseg | 32 octets | Segment de ligne finie | ((x1, y1), (x2, y2)) |
| boîte | 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 | [(x1, y1), ...] |
| 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 au lieu de 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 des fonctions spécialisés.
| Nom | Taille de stockage | La description |
|---|---|---|
| cidr | 7 ou 19 octets | Réseaux IPv4 et IPv6 |
| inet | 7 ou 19 octets | Hôtes et réseaux IPv4 et IPv6 |
| macaddr | 6 octets | Adresses MAC |
Type de chaîne de bits
Les types de chaînes de bits sont utilisés pour stocker les masques de bits. Ils sont 0 ou 1. Il existe deux types de bits SQL:bit(n) et bit varying(n), où n est un entier positif.
Type de recherche de texte
Ce type prend en charge la recherche de texte intégral, qui consiste à rechercher 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. Non. | Nom et description |
|---|---|
| 1 | tsvector Il s'agit d'une liste triée de mots distincts qui ont été normalisés pour fusionner différentes variantes du même mot, appelées «lexèmes». |
| 2 | tsquery Cela stocke les lexèmes à rechercher et les combine en respectant les opérateurs booléens & (AND), | (Ou et ! (NE PAS). Les parenthèses peuvent être utilisées pour imposer le regroupement des opérateurs. |
Type d'UUID
Un UUID (Universally Unique Identifiers) est écrit comme une séquence de chiffres hexadécimaux minuscules, en plusieurs groupes séparés par des tirets, en particulier 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 à l'aide de 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 a l'avantage de vérifier que chaque valeur stockée est une valeur JSON valide. Il existe également des fonctions de support associées disponibles, qui peuvent être utilisées directement pour gérer le type de données JSON comme suit.
| Exemple | Exemple de résultat |
|---|---|
| array_to_json ('{{1,5}, {99,100}}' :: int []) | [[1,5], [99,100]] |
| row_to_json (ligne (1, 'toto')) | {"f1": 1, "f2": "foo"} |
Type de matrice
PostgreSQL donne la possibilité de définir une colonne d'une table comme un tableau multidimensionnel de longueur variable. Des tableaux de n'importe quel type de base intégré ou défini par l'utilisateur, type enum ou type composite peuvent être créés.
Déclaration des tableaux
Le 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[4],
scheme text[][]
);Insérer des valeurs
Les valeurs de tableau peuvent être insérées en tant que constante littérale, en entourant les valeurs d'élément entre accolades et en les séparant par des virgules. Un exemple est montré ci-dessous -
INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);Accès aux baies
Un exemple d'accès aux tableaux est présenté ci-dessous. La commande donnée ci-dessous sélectionnera les personnes dont les économies sont plus importantes au deuxième trimestre qu'au quatrième trimestre.
SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];Modification des tableaux
Un exemple de modification de tableaux est illustré 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 = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';Recherche de tableaux
Un exemple de recherche de tableaux est illustré ci-dessous.
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 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 effectuer une recherche 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 la structure d'une ligne ou d'un enregistrement d'une table.
Déclaration des 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
);Entrée de valeur composite
Les valeurs composites peuvent être insérées en tant que constante littérale, en plaçant les valeurs de champ entre parenthèses et en les séparant par des virgules. Un exemple est montré ci-dessous -
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);Ceci est valable pour l' élément inventaire défini ci-dessus. Le mot clé ROW est en fait facultatif tant que vous avez plusieurs champs dans l'expression.
Accès aux types composites
Pour accéder à un champ d'une colonne composite, utilisez un point suivi du nom du champ, un peu comme 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 (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 des 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 moment entre 10h00 et 11h00).
Les types de gammes intégrées disponibles incluent les gammes suivantes -
int4range - Plage d'entiers
int8range - Gamme de bigint
numrange - Gamme de numérique
tsrange - Plage d'horodatage sans fuseau horaire
tstzrange - Plage d'horodatage avec fuseau horaire
daterange - Plage de dates
Des types de plage personnalisés peuvent être créés pour rendre disponibles de nouveaux types de plages, telles que des plages d'adresses IP utilisant le type inet comme base ou des plages flottantes utilisant le type de données float comme base.
Les types de plage prennent en charge les limites de plage inclusives et exclusives à l'aide des caractères [] et (), respectivement. Par exemple, «[4,9)» représente tous les nombres entiers commençant par 4 et incluant 9 jusqu'à mais non compris.
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 de tels 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 | Références | La description | Exemple de valeur |
|---|---|---|---|
| oid | tout | identifiant d'objet numérique | 564182 |
| regproc | pg_proc | nom de la fonction | somme |
| procédure d'enregistrement | pg_proc | fonction avec des types d'argument | somme (int4) |
| regoper | pg_operator | nom de l'opérateur | + |
| regoperator | pg_operator | opérateur avec types d'arguments | * (entier, entier) ou - (AUCUN, entier) |
| regclass | pg_class | nom de la relation | pg_type |
| regtype | pg_type | nom du type de données | entier |
| regconfig | pg_ts_config | configuration de la recherche de texte | Anglais |
| regdictionary | pg_ts_dict | dictionnaire de recherche de texte | Facile |
Pseudo types
Le système de types PostgreSQL contient un certain nombre d'entrées spéciales qui sont collectivement appelées 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 l'argument ou le type de résultat d'une fonction.
Le tableau ci-dessous répertorie les pseudo-types existants.
| S. Non. | Nom et description |
|---|---|
| 1 | any Indique qu'une fonction accepte n'importe quel type de données d'entrée. |
| 2 | anyelement Indique qu'une fonction accepte n'importe quel type de données. |
| 3 | anyarray Indique qu'une fonction accepte n'importe quel type de données de tableau. |
| 4 | anynonarray Indique qu'une fonction accepte tout type de données non tableau. |
| 5 | anyenum Indique qu'une fonction accepte tout type de données enum. |
| 6 | anyrange Indique qu'une fonction accepte n'importe quel type de données de plage. |
| sept | cstring Indique qu'une fonction accepte ou renvoie une chaîne C terminée par null. |
| 8 | internal Indique qu'une fonction accepte ou renvoie un type de données interne au serveur. |
| 9 | language_handler Un gestionnaire d'appels de langage procédural est déclaré pour renvoyer language_handler. |
| dix | fdw_handler Un gestionnaire de wrapper de données étrangères est déclaré pour renvoyer fdw_handler. |
| 11 | record Identifie une fonction renvoyant un type de ligne non spécifié. |
| 12 | trigger Une fonction de déclenchement est déclarée pour renvoyer le déclencheur. |
| 13 | void Indique qu'une fonction ne renvoie aucune valeur. |