Forums PostgreSQL.fr

robinson

Membre

INSERT INTO à partir d'un SELECT

Bonjour,

Dans le cadre d'une migration d'une base de données géographique depuis un SIG (shapefiles) vers PostgreSQL, je dois créer l'architecture relationnelle de la base à partir des données existantes.
A partir de deux tables existantes représentant deux niveaux de découpage de zones géographiques (par exemple les 'departements' et les 'regions', comprenant les colonnes 'nomdept'/'nomregion' - clé primaires, et leur 'geometrie'), je souhaite intégrer sur la table 'departements' la colonne 'nomregion' avec une contrainte de clé étrangère se référant à la clé primaire 'nomregion de la table 'regions'. Bien sûr, je souhaite aussi que le 'nomdept' corresponde au 'nomregion' qui contient sa 'geometrie'.
J'ai d'abord créé la colonne 'nomregion' dans la table 'departements' (je n'ai pas trouvé de solution pour que la requête renvoie directement la création d'une nouvelle colonne) :

ALTER TABLE departements ADD COLUMN nomregion VARCHAR
CONSTRAINT nomregion
REFERENCES regions (nomregion);

Puis :

INSERT INTO departements (nomregion)
SELECT regions.nomregions
FROM regions
JOIN departements
ON ST_CONTAINS(regions.geom, departements.geom)

Mais j'obtiens une erreur
"null value in column "nomdept" violates not-null constraint
DETAIL:  Failing row contains (null, null, exemplenomdept)."

Sauf que :
- La commande select seule renvoie bien les données voulues
- je ne comprends pas d’où sortent les valeurs NULL sachant que j'ai bien spécifié que l'INSERT ne portait que sur la colonne 'nomregion'

J'ai bien essayé un ON CONFLICT DO UPDATE ou DO NOTHING mais ces commandes ne sont pas reconnues (bizarre...).

Vous avez une idée d'où pourrait venir l'erreur? Ou d'une meilleure formulation / fonction à employer (j'ai testé avec UPDATE mais ça ne marche pas trop avec les jointures spatiales j'ai l'impression).

Merci d'avance!

ruizsebastien

Membre

Re : INSERT INTO à partir d'un SELECT

bonjour,

quand vous faites un insert dans votre colonne nomregion, il ajoute une ligne dans la table departements. La colonne nomdept étant définie comme not null et que vous n'envoyez rien dans cette colonne, c'est normal que vous ayez cette erreur.

---

Cordialement,

Sébastien.

robinson

Membre

Re : INSERT INTO à partir d'un SELECT

Merci pour votre prompte réponse. Je comprends mieux pourquoi la commande ne marche pas, mais dans ce cas comment insérer une colonne dans une table à partir d'une jointure sur cette propre table?
J'ai fait des essais et il est possible de faire un CREATE TABLE satisfaisant avec le SELECT tel qu'il est écrit, c'est à dire créer une copie de la table 'departements' avec les résultats de la jointure et des données de base, mais comment éditer une table existante? C'est un peu fastidieux de devoir recréer une table à chaque fois puis de supprimer l'ancienne... Ce problème se pose d'ailleurs pour associer des clés primaires et étrangères sans passer par de la saisie manuelle.
Cordialement

Dernière modification par robinson (14/05/2018 16:44:00)

dverite

Membre

Re : INSERT INTO à partir d'un SELECT

Si je comprends l'objectif, l'opération que vous voulez faire est un UPDATE, pas un INSERT.
Un INSERT c'est uniquement pour insérer une nouvelle ligne, donc un nouveau département si on parle
d'une table où il y a une ligne par département.

Ici après avoir ajouté une colonne nomregion par ALTER TABLE, il s'agirait plutôt de mettre à jour le contenu
de cette colonne pour chaque département pré-existant.

La requête doit être du style:

UPDATE  departements  SET nomregion =
  (SELECT regions.nomregions
    FROM regions
   WHERE  ST_CONTAINS(regions.geom, departements.geom)
 );

Le SELECT entre parenthèses est une sous-requête corrélée, c'est-à-dire que pour chaque ligne de departements, le moteur va l'exécuter avec la valeur de departements.geom correspondant à cette ligne-là, et va mettre à jour le nomregion de cette ligne-là avec le résultat correspondant.

---

@DanielVerite
http://blog-postgresql.verite.pro/

robinson

Membre

Re : INSERT INTO à partir d'un SELECT

Bonjour,

Je suis effectivement arrivé à la même conclusion que vous et j'ai utilisé UPDATE pour l'insertion des données dans les enregistrements existants.
J'ai juste eu un problème avec votre formulation directe qui n'a pas fonctionné (sans que je sache très bien pourquoi), aussi j'ai utilisé un AS avec une table temporaire :
UPDATE  departements  SET nomregions = regions_temp.nomregions
FROM (
SELECT regions.nomregions AS nomregions
FROM regions
JOIN departements
ON ST_CONTAINS(regions.geom, departements.geom)
)
AS regions_temp;

Merci pour votre contribution!