Forums PostgreSQL.fr

roro

Membre

Copie de tables dans un autre schéma

Bonjour,

J'ai des tables dans un schéma public et je souhaiterais les copier dans un schéma travail.
J'avais utilisé la fonction ALTER TABLE  SET SCHEMA, mais cette fonction déplace les tables et ne les copie pas.
En cherchant j'ai cru comprendre qu'il fallait que je fasse une sauvegarde de mon schéma public pour le restaurer dans mon schéma travail.
Pour cela j'ai tapé dans l'éditeur SQL:

pg_dump -n public -vFp -f public.sql bd_hic

pg me dit:

ERROR:  syntax error at or near "pg_dump"
LINE 1: pg_dump -n public -vFp -f public.sql bd_hic

En cherchant je crois comprendre qu'il faut exécuter pg_dump...mais alors là c'est compliqué pour moi je ne comprends plus rien!!!

Si quelqu'un peut m'aider s'il vous plait....

Merci d'avance.

Marc Cousin

Membre

Re : Copie de tables dans un autre schéma

avec pg_dump ça va être long: on sort les données de la base, pour les y remettre, avec pas mal de travail inutile à la clé. Pourquoi pas:
- soit CREATE table nouveau_schema.nouvelle table AS select * from ancienne_table;
- soit faire un CREATE TABLE avec la même définition que l'ancienne table, puis INSERT INTO nouvelle_table SELECT * FROM ancienne_table ?

---

Marc.

roro

Membre

Re : Copie de tables dans un autre schéma

Merci,
J'ai utilisé la deuxième solution pour recopier mes tables. Cela me permet en effet de garder les contraintes déjà définies dans le premier schéma.

Par contre, lorsque je connecte ma base à QGIS des 3 tables avec géométrie, une seule s'affiche.
J'ai donc supprimé les contraintes géométriques des tables qui ne s'affichent pas pour les recréer avec "addgeometrycolumn".

SELECT AddGeometryColumn('travail.perim_etude', 'the_geom', 2154, 'POLYGON', 2)

J'ai précisé le_nouveau_schema.la_table mais pg me dit:

ERROR:  Table 'travail.perim_etude' does not occur in the search_path
CONTEXT:  SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )"
PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement

Je ne comprends pas bien de quelle erreur il s'agit.

Merci d'avance pour votre aide.

roro

Membre

Re : Copie de tables dans un autre schéma

C'est bon j'ai trouvé, il fallait préciser le schéma de cette façon:
SELECT AddGeometryColumn('travail', 'perim_etude', 'the_geom', 2154, 'POLYGON', 2)
Dsl...

roro

Membre

Re : Copie de tables dans un autre schéma

Dans mon schéma public j'avais une fonction trigger me permettant de copier automatiquement dans une table non géométrique l'identifiant créé dans la table avec géométrie sous QGIS.
Depuis que j'ai copié toutes mes tables dans un nouveau schéma, lorsque je crée un nouvel objet sous QGIS il ne veut pas me l'enregistrer:

ERROR:  insert or update on table "hab_phyto" violates foreign key constraint "fk_id_hab"
DETAIL:  Key (id_hab)=(7) is not present in table "habitat".
CONTEXT:  SQL statement "INSERT INTO travail.hab_phyto (id_hab) VALUES ( $1 )"
PL/pgSQL function "trig_maj_hab" line 2 at SQL statement

Alors j'ai refait ma fonction trigger:
CREATE OR REPLACE FUNCTION travail.trig_maj_hab()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO travail.hab_phyto (id_hab)
VALUES (NEW.id_hab);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION travail.trig_maj_hab() OWNER TO postgres;

CREATE TRIGGER trig_maj_id_hab
  AFTER INSERT OR UPDATE
  ON travail.habitat
  FOR EACH ROW
  EXECUTE PROCEDURE travail.trig_maj_hab();

Je ne comprends pas pourquoi ça fonctionnait dans le schema public et pas dans le schema travail.
Merci d'avance pour vos réponses.

Marc Cousin

Membre

Re : Copie de tables dans un autre schéma

Qu'est ce qui a changé dans la fonction PL ?

Il y a trop peu d'information pour répondre précisément. La nouvelle table avait la fonction trigger ? Ça n'est pas recopié par un create as select

---

Marc.

roro

Membre

Re : Copie de tables dans un autre schéma

Bonjour,

En fait j'ai recréé la fonction trigger dans le schéma travail car je ne sais pas si on peut faire appel à une fonction écrite dans un autre schéma.
J'ai aussi le même nom de tables dans les deux schémas, je me dis que cela peut peut-être poser problème.

Voilà la fonction trigger dans le schéma public:

CREATE OR REPLACE FUNCTION trig_copy_id_hab()
  RETURNS trigger AS
$BODY$
BEGIN
    insert into hab_phyto (id_hab) values (NEW.id_hab);
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION trig_copy_id_hab() OWNER TO postgres;

et le trigger associé sur la table habitat:

CREATE TRIGGER copy_id_hab
  AFTER INSERT OR UPDATE
  ON habitat
  FOR EACH ROW
  EXECUTE PROCEDURE trig_copy_id_hab();

Voici la fonction trigger dans le schéma travail:

CREATE OR REPLACE FUNCTION travail.trig_maj_hab()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO travail.hab_phyto (id_hab)
VALUES (NEW.id_hab);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION travail.trig_maj_hab() OWNER TO postgres;

et le trigger associé dans la table habitat du schéma travail:

CREATE TRIGGER trig_maj_id_hab
  AFTER INSERT OR UPDATE
  ON travail.habitat
  FOR EACH ROW
  EXECUTE PROCEDURE travail.trig_maj_hab();

Voilà j'espère vous avoir donné toutes les informations.

Une dernière question,
Je n'ai pas besoin de recopier ma table geometry_columns dans mon nouveau schéma?
J'ai vu que celle présente dans le schéma public a les lignes correspondantes à mes nouvelles tables géométriques de mon schéma travail.

J'espère avoir été assez claire...

Merci encore pour votre aide.

Marc Cousin

Membre

Re : Copie de tables dans un autre schéma

> En fait j'ai recréé la fonction trigger dans le schéma travail car je ne sais pas si on peut faire appel à une fonction écrite dans un autre schéma.
Si bien sûr… Il suffit de préciser le schéma au moment du create trigger. Ce que vous avez d'ailleurs fait.

Par contre effectivement, les deux procédures n'accèdent pas à la même table. La première accède à public.hab_phyto, la seconde à travail.hab_phyto. Il s'agit bien de deux fonctions différentes. On pourrait tout mettre dans la même fonction, avec un IF ou un ordre dynamique, mais à quoi bon ? Et cela aura un coût en termes de performance.

Par contre, vous devriez modifier la fonction du schéma public, pour écrire explicitement public.hab_phyto, parce qu'à l'heure actuelle, son comportement dépend de 'search_path', ce qui est très dangereux, surtout pour une fonction trigger.

> Je n'ai pas besoin de recopier ma table geometry_columns dans mon nouveau schéma?
Surtout pas. Cette table doit rester dans public. En théorie, même, c'est une bonne pratique Postgis, il ne faut laisser dans le schéma public que les objets Postgis. Cela simplifie entre autres les migrations entre versions majeures.

---

Marc.

roro

Membre

Re : Copie de tables dans un autre schéma

Merci pour la rapidité de votre réponse!
J'ai bien précisé le schéma dans la fonction trigger du schéma public.

Cependant, lorsque je crée un nouvel objet dans ma table habitat sous QGIS, il me dit un message d'erreur relatif à ce trigger:

ERROR:  insert or update on table "hab_phyto" violates foreign key constraint "fk_id_hab"
DETAIL:  Key (id_hab)=(11) is not present in table "habitat".
CONTEXT:  SQL statement "INSERT INTO travail.hab_phyto (id_hab) VALUES ( $1 )"
PL/pgSQL function "trig_maj_hab" line 2 at SQL statement

Est-ce que l'erreur vient du trigger. Il dit pourtant bien AFTER INSERT OR UPDATE... il me semble que ça devrait fonctionner?!?

Marc Cousin

Membre

Re : Copie de tables dans un autre schéma

>J'ai bien précisé le schéma dans la fonction trigger du schéma public.
Pas dans l'exemple que vous donnez (peut-être l'avez vous modifié depuis…):

insert into hab_phyto (id_hab) values (NEW.id_hab);

> Cependant, lorsque je crée un nouvel objet dans ma table habitat sous QGIS, il me dit un message d'erreur relatif à ce trigger:
Je crois qu'il faudrait reposter la définition exacte des deux tables dans lesquelles vous essayez d'insérer, le code de la fonction trigger, de l'ordre de création du trigger, et le contenu de la variable search_path. On trouvera plus vite ce qui ne va pas.

---

Marc.

roro

Membre

Re : Copie de tables dans un autre schéma

Pour la première remarque, je l'avais modifié entre temps.

Ensuite,
Voilà la définition de la table habitat:

CREATE TABLE travail.habitat
(
  id_hab serial NOT NULL,
  surf_ha numeric,
  surf_prcent_perim real,
  date_saisie_min date,
  date_saisie_max date,
  descriptif character varying(150),
  id_referentiel integer,
  id_uv integer,
  id_nat_obs integer,
  id_et_cons integer,
  id_restaur integer,
  id_typi integer,
  num_site character varying(9),
  id_dynam integer,
  the_geom geometry,
  CONSTRAINT pk_hab PRIMARY KEY (id_hab),
  CONSTRAINT fk_dynam FOREIGN KEY (id_dynam)
      REFERENCES dynamique (id_dynam) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_et_cons FOREIGN KEY (id_et_cons)
      REFERENCES etat_cons (id_et_cons) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_nat_obs FOREIGN KEY (id_nat_obs)
      REFERENCES nature_obs (id_nat_obs) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_num_site FOREIGN KEY (num_site)
      REFERENCES perim_etude (num_site) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_referentiel FOREIGN KEY (id_referentiel)
      REFERENCES referentiel (id_referentiel) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_restaur FOREIGN KEY (id_restaur)
      REFERENCES restaur (id_restaur) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_typi FOREIGN KEY (id_typi)
      REFERENCES typicite (id_typi) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_uv FOREIGN KEY (id_uv)
      REFERENCES unite_veg (id_uv) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2154)
)
WITH (OIDS=TRUE);
ALTER TABLE travail.habitat OWNER TO postgres;

-- Trigger: trig_maj_id_hab on travail.habitat

-- DROP TRIGGER trig_maj_id_hab ON travail.habitat;

CREATE TRIGGER trig_maj_id_hab
  AFTER INSERT OR UPDATE
  ON travail.habitat
  FOR EACH ROW
  EXECUTE PROCEDURE travail.trig_maj_hab();

La définition de la table hab_phyto:

CREATE TABLE travail.hab_phyto
(
  id_hab integer,
  "surf_%_hab" real,
  id_statut integer,
  association_veg character varying(150),
  id_code_cb integer,
  id_natura integer,
  id_phyto integer,
  id_cahab integer,
  id_eunis integer,
  id_hab_phyto serial NOT NULL,
  CONSTRAINT pk_hab_phyto PRIMARY KEY (id_hab_phyto),
  CONSTRAINT fk_cahab FOREIGN KEY (id_cahab)
      REFERENCES cahab (id_cahab) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb FOREIGN KEY (id_code_cb)
      REFERENCES corine_biotopes (id_code_cb) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_hab FOREIGN KEY (id_hab)
      REFERENCES habitat (id_hab) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_natura FOREIGN KEY (id_natura)
      REFERENCES natura_2000 (id_natura_2000) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_phyto FOREIGN KEY (id_phyto)
      REFERENCES code_phyto (id_code_phyto) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT id_eunis FOREIGN KEY (id_eunis)
      REFERENCES eunis (id_eunis) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE travail.hab_phyto OWNER TO postgres;

Le code de la fonction trigger:

CREATE OR REPLACE FUNCTION travail.trig_maj_hab()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO travail.hab_phyto (id_hab)
VALUES (NEW.id_hab);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION travail.trig_maj_hab() OWNER TO postgres;

Pour ce qui est du contenu de la variable path, je ne sais pas ce que c'est, ni comment trouver son contenu

Marc Cousin

Membre

Re : Copie de tables dans un autre schéma

Le search_path n'est plus aussi important, vu que tout est préfixé. Pour info, on le voit en tapant SHOW search_path;

Sinon, le problème ne viendrait pas de ça ?
CONSTRAINT fk_id_hab FOREIGN KEY (id_hab)
      REFERENCES habitat (id_hab) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
Elle ne devrait pas pointer sur travail.habitat ?

---

Marc.

roro

Membre

Re : Copie de tables dans un autre schéma

Oui c'est ça je viens de comprendre et de rectifier le tir!
En fait mes clés étrangères ne précisaient pas vers quel schéma elles pointaient.
J'ai rajouté le nom du schéma dans toutes les références et ça fonctionne correctement!!

Merci de votre aide!