Vous n'êtes pas identifié(e).
Pages : 1
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.
Hors ligne
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.
Hors ligne
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.
Hors ligne
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...
Hors ligne
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.
Hors ligne
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.
Hors ligne
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.
Hors ligne
> 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.
Hors ligne
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?!?
Hors ligne
>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.
Hors ligne
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 
Hors ligne
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.
Hors ligne
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!
Hors ligne
Pages : 1
