Discussion :

[dhaulagiri]

Bonjour,

Je cherche des conseils pour gérer au mieux la problématique suivante avec une table PostGIS.

Une requête SELECT crée des secteurs géographiques (polygones) à partir de plusieurs autres tables de référence telles que des zones administratives (multipolygones) et des limites (lignes) servant à découper les zones en fonction de problématiques de gestion. Un code unique de type entier est attribué à chaque secteur en fonction d'un modèle à 10 chiffres, imposé par l'application dans laquelle les secteurs seront chargés ensuite: les deux premiers décrivent la nature du secteur, les 5 suivants correspondent à sa zone administrative et les 3 derniers à un numéro d'ordre dans la zone administrative permettant de garantir l'unicité du code du secteur. Ce numéro d'ordre est attribué en fonction de la position géographique des secteurs dans leur zone administrative: du nord-ouest au sud-est, en suivant le sens "naturel" de lecture de la plupart des languages.

Les tables de référence évoluent régulièrement, leur géométrie comme leurs autres attributs: modification d'un périmètre administratif, changement de nom, création d'une nouvelle limite, etc. La table de secteurs géographiques doit donc être actualisée en conséquence. En exécutant à nouveau la requête, on obtient un nouveau jeu de données à jour mais dans lequel un secteur aura pu changer de numéro: par exemple, si un secteur initial est découpé par une nouvelle limite, alors de nouveaux secteurs seront créés et tous les secteurs qui suivent dans l'ordre de numérotation auront un nouveau numéro (décalage).

L'enjeu consiste donc à trouver un identifiant pérenne permettant de mettre à jour les secteurs modifiés et de garantir l'intégrité référentielle avec les autres tables. Dans ce cas, je ne sais pas comment faire. J'ai pensé à une empreinte numérique mais je ne sais pas si c'est une bonne idée et avec quelles colonnes la calculer.

Pourriez-vous m'aider? Merci d'avance

[SQLpro]

Utilisez deux identifiants. Un identifant informatique type INT IDENTITY en PRIMARY KEY et l'autre sur le code à 10 caractères...

INT = 4 octets pour 2 milliards de valeur
Code 10 chiffres = 10 octets+ plus un extraoverhead du fait de la gestion de la collation + mise à jour possible de l'info... = Contraintes UNIQUE...

A +

[escartefigue]

Une clef primaire doit être :

• unique : chaque valeur n'apparait qu'une seule fois ;
• non "nullable" : chaque valeur est obligatoire ;
• irreductible : une sous partie de la clef ne peut suffire à déterminer de façon certaine la ligne dans la table ;
• stable : son contenu ne doit jamais changer ;
• concis : l'encombrement doit être le plus faible possible.

Les deux derniers points ne sont pas obligatoires, mais très fortement recommandés :

Stabilité : à cause de l'intégrité référentielle qui propage les valeurs de PK dans les tables liées sous forme de FK, en cas de modification, le phénomène de cascade pourrait impacter des millions de lignes !

Concision : se justifie par l'utilisation très fréquente des PK dans les requêtes, en particulier pour les jointures. Une colonne de type integer (small, int ou bigint) sera toujours beaucoup plus concise à nombre de valeurs égal qu'une colonne de tout autre type. De plus, les types numériques sont insensibles à la collation. Autant d'arguments en faveur d'une charge réseau et d'une charge CPU réduite, au bénéfice des performances.
Ce point milite en défaveur des GUID utilisés comme PK.

C'est pourquoi on fuit les identifiants "naturels", le plus souvent encombrants et instables, comme choix de clef primaire.
Un identifiant technique artificiel attribué par le SGBD est ce qui est le mieux comme PK, l'identifiant naturel pourra si besoin être doté d'une contrainte unique, mais ne sera pas PK

[dhaulagiri]

Merci. Voici plus précisément la structure de ma table:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE IF NOT EXISTS schema.ma_table
(
    id integer NOT NULL DEFAULT nextval('schema.ma_table_id_seq'::regclass),
    esz text COLLATE pg_catalog."default",
    code_du_carreau_1000x1000 text COLLATE pg_catalog."default",
    code_de_la_commune text COLLATE pg_catalog."default",
    code_du_departement text COLLATE pg_catalog."default",
    etat_de_l_objet text COLLATE pg_catalog."default",
    nom text COLLATE pg_catalog."default",
    has_convention boolean,
    geom_2154 geometry(Polygon,2154),
    CONSTRAINT ma_table_pkey PRIMARY KEY (id),
    CONSTRAINT ma_table_esz_key UNIQUE (esz)
)

J'avais déjà défini un id de type serial comme clé primaire. La colonne esz correspond au code unique que j'évoque (contrainte unique).

Ce que je n'ai pas compris dans la réponse est s'il faut ajouter une contrainte sur les deux colonnes. De plus, quand j'insère les objets potentiellement mis à jour à partir de la requête, est-ce que je dois préciser que la clause ON CONFLICT est basé sur les deux colonnes id et esz?

Merci!

[escartefigue]

Dans la mesure où la PK est de type serial, il n'y aura jamais de ON CONFLICT applicable sur cette colonne, c'est forcément sur la colonne unique non PK que pourra porter le viol de contrainte.
Oui, il faut bien mettre les deux contraintes : PK d'une part et UNIQUE d'autre part.

[SQLpro]

De plus l'autoincrémentation avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

id integer NOT NULL DEFAULT nextval('schema.ma_table_id_seq'::regclass),

c'est lourd et inutile
préférez le IDENTITY plus léger !

A +

[dhaulagiri]

Dans la mesure où la PK est de type serial, il n'y aura jamais de ON CONFLICT applicable sur cette colonne, c'est forcément sur la colonne unique non PK que pourra porter le viol de contrainte.
Oui, il faut bien mettre les deux contraintes : PK d'une part et UNIQUE d'autre part.

En effet. Du coup, je ne vois pas comment je peux m'en sortir à partir d'un jeu de données entièrement actualisé :
- il n'y a pas d'id puisque je ne fais pas de SELECT dans la table existante, je la reconstruis à partir d'autres tables
- je dispose uniquement de la colonne unique non PK mais un numéro affecté à un objet dans la table originale peut être affecté à un autre dans la table révisée (tout en demeurant unique au global)

En passant, il n'y a pas d'IDENTITY dans PostgreSQL à ma connaissance.

[escartefigue]

Selon les SGBD, on parle d'identity, counter, auto_increment, serial ou number, mais il s'agit du même principe.
Certains SGBD autorisent l'incrément ou le décrément au choix, d'autres seulement l'incrément.

IDENTITY est supporté par postgre depuis la V10 : https://www.postgresqltutorial.com/p...entity-column/

Si l'identifiant est hérité d'une autre table, alors son unicité est garantie par l'autre table, du coup une contrainte FK référence sur la table en question doublée d'une contrainte unique suffira

[SQLpro]

...
En passant, il n'y a pas d'IDENTITY dans PostgreSQL à ma connaissance.

Hé bien votre connaissance est erronée... PostGreSQL utilisait le SERIAL qui n'existe pas dans la norme et se trouvait être très limité et s'est enfin mis aux séquence et à l'identity...

https://www.postgresqltutorial.com/p...entity-column/

D'ou l'intérêt de suivre mes cours de SQL...
https://sqlpro.developpez.com/livre/...le-langage-sql

Le chapitre 2 en cours de montage parlera des types de données... et le 3 des commandes du DDL.

Extrait, chapitre 2 :

2.4.3 – Les auto-incréments
Une technique assez classique pour définir automatiquement une valeur de clé consiste à utiliser un mécanisme d’auto-incrémentation interne. Il existe pour ce faire, deux types d’objets normalisés que l’on retrouve dans la plupart des SGBDR : la propriété IDENTITY attaché à une colonne d’une table et l’objet SEQUENCE indépendant de toute table. Nous verrons ces deux mécanismes et comment les utiliser dans la chapitre 3 consacré aux commandes de création, modification et suppression des objets (tables, vues, contraintes…), c’est-à-dire dans la partie DDL (Data Definition Language) du langage SQL.

Extrait chapitre 3 :

3.8.2 Auto incréments

Il existe deux méthodes bien distinctes. L’usage de la propriété IDENTITY qui s’applique à une colonne d’une table, et l’objet SEQUENCE indépendant qui fournit des valeurs par le biais d’un appel intégré soit dans la définition de la colonne d’une table soit dans un déclencheur BEFORE.

ATTENTION
Quel que soit l’outil choisit pour générer un auto incrément, ce dernier n’offre aucune garantie de séquencement (les paramètres peuvent être changés à tout moment et on peut forcer des valeurs) et n’offre pas non plus de numérotation continue (il peut y avoir des « trous » ou des chevauchements).

3.8.2.1 Propriété IDENTITY
La propriété IDENTITY se définit dans la table comme colonne calculée à la place de la contrainte par défaut après la définition du type SQL de la colonne comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 
   [ ( <option_autoincrément> ) ]

Les arguments de cette propriété sont les suivants :
• START WITH <valeur> : initialisation de l'incrément
• INCREMENT BY <valeur> : initialisation du pas d'incrément
• MAXVALUE <valeur> | NO MAXVALUE : spécification ou déspécification de la valeur maximale de l'auto incrément
• MINVALUE <valeur> | NO MINVALUE : spécification ou déspécification de la valeur minimale de l'auto incrément
• CYCLE | NO CYCLE : spécification de cycle ou non.
• CACHE n | NO CACHE : indique si des valeurs précalculées doivent être mise en mémoire
Il ne peut y avoir qu’une seule colonne de la table pourvue de ce type d’autoincrément.
Par défaut les options sont de démarrer à 1 par pas de 1 sans aucune autre option.
Il doit être utilisé sur une colonne de type entier.

Exemple 3.45* – Table pourvu de l’auto incrément IDENTITY

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

	

	
Code  :
	
Sélectionner tout -
	Visualiser dans une fenêtre à part
	
123456789
CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER 
    GENERATED ALWAYS AS IDENTITY 
       (START WITH -2147483648
       INCREMENT BY 1
       NO CYCLE
       CACHE 100)
    CONSTRAINT PK_USR PRIMARY KEY,
 USR_LOGIN          VARCHAR(256) NOT NULL);

ATTENTION
Ne confondez pas auto incrément et clé. Un auto incrément ne garantit pas l’unicité et ne propose pas les mêmes services qu’une clé primaire. Une clé est systématiquement indexée et garantie l’unicité. Ce n’est pas le cas de l’auto incrément dont les valeurs sont arbitraires et peuvent avoir des doublons par forçage ou modification des paramètres de calcul.

3.8.2.2 Objet SEQUENCE
C’est un objet indépendant de toute table dont la syntaxe est :
CREATE SEQUENCE [ nom_schema. ] nom_sequence
AS { <type_SQL_entier> | nom_domaine_entier }
[ START WITH <valeur> ]
[ INCREMENT BY <valeur> ]
[ { MINVALUE [ <valeur> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <valeur> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <valeur> ] } | { NO CACHE } ]

Observez qu’il possède les mêmes caractéristiques que la propriété IDENTITY.
Pour en obtenir une valeur il faut faire appel à la commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
NEXT VALUE FOR [ nom_schema. ] nom_sequence 
   [ OVER ( <clause_orde_by_over> ) ]

Pour opérer comme valeur d’une colonne de table, Il doit être placé dans la contrainte par défaut de la colonne visée.
La clause OVER du séquenceur ne peut être invoquée que dans une commande de lecture SELECT ou un ordre UPDATE.
Exemple 3.46* – Définition d’une séquence et utilisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE SEQUENCE S_SYSTEM.SQC_TOUTES_TABLES
   AS BIGINT  
      START WITH 1
      INCREMENT BY 255
      NO CYCLE 
      CACHE 10000;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
-- utilisation dans la définition d'une table
CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER NOT NULL 
    DEFAULT NEXT VALUE FOR S_SYSTEM.SQC_TOUTES_TABLES
    CONSTRAINT PK_USR PRIMARY KEY,
 USR_LOGIN          VARCHAR(256) NOT NULL);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 -- utilisation dans une requêtes d'extraction SELECT
SELECT NEXT VALUE FOR S_SYSTEM.SQC_TOUTES_TABLES 
          OVER(ORDER BY  EMP_DATE_NAISSANCE ), 
       *
FROM S_RH.T_EMPLOYE_EMP;

A +