Vous n'êtes pas identifié(e).
Bonjour,
Un peu tard peut-être, mais voici le code d'une fonction qui permet de mettre à jour (et donc de lister) les séquences utilisée comme valeur par défaut par les tables du ou des schémas indiqués. Deux contraintes :
- la séquence est dans le même schéma que la table qui l'utilise
- la séquence est utilisée par une seule table
--
-- Met à jour les séquences définissant les valeurs par défaut des tables du ou des
-- schemas correspondant au pattern indiqué.
-- suppose que la séquence est définie dans le même schéma que la table qui l'utilise
-- suppose qu'une seule table utilise chaque séquence
--
-- usage: select update_my_sequences('sig%'); pour mettre à jour toutes les séquences
-- utilisées comme valeur par défaut par les tables des schémas dont le
-- nom commence par 'sig'
--
-- Traite les séquences qui aurait été définies comme valeur par défaut "après coup"
--
create or replace function update_my_sequences(v_schemas character varying)
returns integer as
$$
declare
v_nb integer := 0;
v_max bigint := 0;
v_sql varchar := '';
v_schemaname varchar := '';
v_rec1 record;
begin
FOR v_schemaname IN SELECT schema_name FROM information_schema.schemata WHERE schema_name like v_schemas LOOP
RAISE info '=================================================================';
raise info 'Schéma %',v_schemaname;
v_sql := FORMAT('SELECT regexp_replace(column_default, ''nextval\(''''([a-z0-9_]+)''''::regclass\)'',''\1'') AS sequence_name,table_schema,table_name,column_name,data_type,column_default from information_schema.columns WHERE table_schema LIKE %L AND column_default LIKE ''nextval%%''',v_schemaname);
RAISE info '-----------------------------------------------------------------';
--raise info '%', v_sql;
FOR v_rec1 IN EXECUTE v_sql LOOP
EXECUTE FORMAT('SELECT max(%I) FROM %I.%I', v_rec1.column_name,v_rec1.table_schema , v_rec1.table_name) INTO v_max;
RAISE INFO 'SEQUENCE=%, MAX(%)=%',v_rec1.sequence_name,v_rec1.table_schema || '.' || v_rec1.table_name || '.' || v_rec1.column_name, v_max;
if v_max is not null then
v_sql := format('SELECT setval(%L,',v_rec1.table_schema || '.' || v_rec1.sequence_name) || v_max || ')';
raise info '%', v_sql;
execute v_sql;
v_nb := v_nb + 1;
end if;
END LOOP;
END LOOP;
RETURN v_nb;
end;
$$
language plpgsql;Pourquoi réinitialiser ?
Votre séquence ne sert-elle pas qu'à assurer l'unicité de votre clé primaire ?
L'idée était de repartir de zéro pour mes tests en considérant que ma base était peut-être incohérente, mais mon post précédent montre que l'incohérence était seulement dans ma tête ! ;-)
J'ai refait quelques test et obtenu l'explication grâce à l'exécution du code suivant.
set search_path to public;
\x on
drop table if exists t1 cascade;
create table t1 (id bigserial primary key,nom character varying);
insert into t1 values (1,'A'),(2,'B'),(3,'C');
------
select currval('t1_id_seq');
select sequence_catalog, sequence_schema, sequence_name,data_type,start_value,minimum_value,maximum_value,"increment" from information_schema.sequences where sequence_schema = 'public' and sequence_name like 't1%seq';
------
select setval('t1_id_seq',3);
select currval('t1_id_seq');
select sequence_catalog, sequence_schema, sequence_name,data_type,start_value,minimum_value,maximum_value,"increment" from information_schema.sequences where sequence_schema = 'public' and sequence_name like 't1%seq';
------
insert into t1 (nom) values ('*A'),('*A1'),('*A2'),('*A3');
select currval('t1_id_seq');
select sequence_catalog, sequence_schema, sequence_name,data_type,start_value,minimum_value,maximum_value,"increment" from information_schema.sequences where sequence_schema = 'public' and sequence_name like 't1%seq';En fait, l'erreur "apparente" est due à une lecture erronée de ma part.
J'ai confondu l'option START affichée par PgAdmin dans le script SQL de création avec la valeur start_value donnée à la création de la séquence:
CREATE SEQUENCE public.t1_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 7
CACHE 1; ce code SQL permet de recréer la séquence pour qu'elle soit identique à son état actuel. Il est donc normal qu'il fasse démarrer la séquence à 7 en utilisant currval('t1_id_seq') puisqu'elle est définie; tandis que la vue information_schema.sequences affiche les valeurs utilisées pour créer la séquence existante.
Désolé pour le dérangement.
Ok, merci. S'agissant d'une base de test, je vais me contenter de tout réinitialiser.
Difficile de vous conseiller en connaissant mal votre organisation, vos besoins et contraintes exacts et les profils utilisateurs.
De ce que j'ai compris, peut-être pourriez-vous envisager de passer pas des sauvegardes avec pgdump d'une base complète équivalente au jeu de fichiers csv produits. Ces sauvegardes pourraient être restaurées sur un autre poste en écrasant si nécessaire la version précédente. Mais le contexte demande à être précisé avant d'aller plus loin
Comme vous utilisez Notepad++, vous pouvez facilement convertir votre fichier csv avec Encodage > Convertir en UTF8 (sans BOM)
[EDIT] remarque inutile, désolé ![/EDIT]
Non, il s'agit d'une instance de test isolée en localhost.
OS: Win7 64 bits
Postgres 9.3 (32 bits) et PgAdmin 1.22.2 (32 bits) sont sur la même machine.
J'ai fait une copie d'écran (autant pour me rassurer sur mon constat que pour vous convaincre !) 
Merci Guillaume.
Oui, j'avais mis le résultat de currval pour compléter l'information sur le contexte
Postgres 9.3
PgAdmin 1.22.2
Bonjour,
Je suis confronté à un contexte de séquences que je ne comprends pas car j'obtiens des résultats qui me paraissent incohérents :
dans psql (comme dans PgAdmin)
bd1=# select * from information_schema.sequences where sequence_name like 't1%';me renvoie
-[ RECORD 1 ]-----------+------------------------
sequence_catalog | bd1
sequence_schema | test
sequence_name | t1_id_seq
data_type | bigint
numeric_precision | 64
numeric_precision_radix | 2
numeric_scale | 0
start_value | 8
minimum_value | 1
maximum_value | 9223372036854775807
increment | 1
cycle_option | NO
-[ RECORD 2 ]-----------+------------------------
sequence_catalog | bd1
sequence_schema | test
sequence_name | t1_mslink_seq
data_type | bigint
numeric_precision | 64
numeric_precision_radix | 2
numeric_scale | 0
start_value | 1000149
minimum_value | 1
maximum_value | 9223372036854775807
increment | 1
cycle_option | NOet
bd1=# select currval('t1_id_seq');
ERREUR: la valeur courante (currval) de la séquence « t1_id_seq » n'est pas encore définie dans cette session
bd1=# select currval('t1_mslink_seq');
ERREUR: la valeur courante (currval) de la séquence « t1_mslink_seq » n'est pas encore définie dans cette sessiontandis que PgAdmin m'indique
CREATE SEQUENCE test.t1_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 285
CACHE 1;
ALTER TABLE test.t1_id_seq
OWNER TO usr1;
CREATE SEQUENCE test.t1_mslink_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000233
CACHE 10;
ALTER TABLE test.t1_mslink_seq
OWNER TO usr1;Ceci, sans que les séquences en question n'aient été modifiées ou utilisées depuis l'ouverture de session.
Pourquoi PgAdmin affiche-t-il une valeur différente de start_value pour l'option START ?
Merci d'avance
- le serveur sera, sur toutes les machines, postgresql/postgis (même version que le mien car elle est précisée dans le fichier .bat) - et du coup le port sera également le même pour que ça puisse fonctionner
Non ! Il n'y a qu'un seul serveur auquel se connectent les différents clients (à moins que j'ai mal compris votre objectif; l'intérêt d'un SGBD est, entre autres de centraliser les données)
Voir à ce sujet Architecture Client-Serveur
les clients seront psql pour le traitement sql, et QGis pour l'affichage des vues géomatiques sur toutes les machines
oui
les fichiers à importer/exporter devront être sur toutes les machines dans le même dossier et chemin, accessible à tous les utilisateurs (par exemple C:\Users\Public\Documents\import\ et C:\Users\Public\Documents\export)
oui, chemin accessible au moins à l'utilisateur connecté et utilisant le script
le fichier .bat devra être lancé depuis... tout compte utilisateur des machines ou depuis le compte de l'administrateur ?
depuis un compte utilisateur (il faut distinguer l'utilisateur système (windows) qui a ouvert la session de l'utilisateur postgres qui se connectera à la base de données)
le fichier sql devra préciser le nom des bases et des schémas pour qu'il s'exécute correctement ?
oui, le fichier sql ou le fichier batch avec les options de la commande psql
le fichier sql devra utiliser \copy plutôt que copy ?
oui
J'aurais une autre question concernant les fichiers .csv importés et traités (ils servent à faire quelques analyses et quelques mises à jours de tables de la bdd). Je la formule ici car elle est dans la continuité du projet, mais peut-être qu'il faudra que je la pose dans un nouveau sujet ?
J'ai plusieurs fichiers qui correspondent à des extractions régulières d'une base de données. Je cherche à actualiser ma propre base de donnée avec des traitements de ces fichiers. J'aimerai par exemple obtenir une synthèse (requête sql) de chaque fichier et regrouper les résultats en fonction de la date du fichier source :id | date_extract | nb_condition1 | nb_condition2 1 | 2017-04 | 125 | 27 2 | 2017-01 | 100 | 29 ...Avec par exemple, deux fichiers d'extractions nommés 20170412_extract_ouv.csv et 20170122_extract_ouv.csv
-- Est-il possible de récupérer le nom du fichier (dans une variable ?) pour ensuite appliquer la requête sql ? Auriez-vous un exemple ?
-- Est-il possible d'importer plusieurs fichiers de ce type, tous enregistrés dans un seul et même dossier, mais tous avec un nom de fichier différent, pour leur appliquer ensuite la requête ? Auriez-vous un exemple ?
effectivement, la plupart des échanges se sont éloignés du sujet initial du post (que vous pourriez peut-être renommer). Il serait préférable d'en créer un nouveau pour cette dernière question et la détailler.
Pour installer psql sur un poste, vous pouvez :
- installer PgAdmin qui inclut psql
ou bien
- copier dans un répertoire défini dans le PATH les fichiers suivants: psql.exe, libiconv-2.dll, lib-intl8.dll, libpq.dll. Sous windows vous trouverez ces fichiers dans le répertoire contenant pgadmin.exe
Bonjour,
Pour l'import / export via psql
Si je comprends bien, et je m'excuse par avance de la demande de confirmation sur des choses qui doivent vous sembler basiques... :
- Tout ce que je produit actuellement sur mon PC à l'aide de Postgresql/postgis, PgAdmin et QGIS (ainsi qu'avec mes fichiers shape et csv initiaux) ne pourra pas être reproduit (et donc les résultats de requêtes - tables ou vues ) par une autre personne sur un autre PC ?
Même si cette autre personne installe également Postgresql/postgis, PgAdmin et QGIS, et utilise un projet de QGIS qui charge les vues ?
Il ne pourra pas y avoir d'import/export des fichiers csv si le chemin est toujours C:\Users\Public\Documents par exemple ?
Du fait que tout (serveur et client) est installé sur votre machine les choses restent confuses.
Il faut distinguer Postgresql/Postgis, le serveur de base de données et les clients qui peuvent l'exploiter (psql, PgAdmin,QGIS...). Si vous avez la possibilité d'installer Postgres sur une autre machine, vous y verrez plus clair et (à peu près) tout ce que vous pourrez faire sur votre poste sera également possible sur n'importe quel poste: un utilisateur potentiel n'a besoin que du (des) client(s) sur son poste, par exemple :
serveur : postgres/postgis (psql est forcément présent)
client1 : psql
client2 : PgAdmin et QGIS
client3: psql et QGIS
client4: PgAdmin
client5: QGIS
Si ce n'est effectivement pas possible, d'après est-il "facile" de créer ce fameux script avec raccourci pour importer les fichiers ?
oui
un exemple de fichier batch (.bat) lancement de psql (windows 64 bits):
@set PG_VERSION=9.3
@set PG_PORT=5432
@set PG_PATH=C:\Program Files (x86)\PostgreSQL\%PG_VERSION%\
"%PG_PATH%\bin\"psql.exe -p %PG_PORT% -h serveur -f monscript.sqlCe script peut-il intégrer tout le travail de traitement codé en SQL (et qui permettrait un export de tables et de vues) ?
oui, vous pouvez lancer n'importe quel script sql
Ce script / raccourci peut-il être installé facilement sur n'importe quel PC ?
oui, en local ou sur un disque réseau
Faut-il également (je suppose que oui) que le PC ait Postgresql/postgis (et PgAdmin?)
non, seulement psql (cf. ci-dessus)
Pour simplifier le problème des droits d'accès aux fichiers, il peut être utile d'utiliser la commande \copy de psql qui est exécutée avec les droits de l'utilisateur connecté (il faut que l'emplacement des csv soit accessible en lecture (import) et/ou écriture (export) à cet utilisateur
Merci beaucoup pour votre aide
Avec plaisir
Mais l'environnement console parait hostile à certains utilisateurs, ça dépend des habitudes et s'ils ont plutôt une culture windows/souris ou unix/clavier.
Il est toujours possible de créer un script (batch ou powershell) sur lequel on crée un raccourci avec une icone adaptée. Dans ce cas, il faudrait que ce script :
- vérifie l'existence du fichier csv
- lance l'import avec psql
- supprime le fichier csv si l'import s'est bien déroulé afin d'éviter d'importer plusieurs fois les données
Bonjour,
La définition d'idauto lors de la création de la table (pseudo-type serial et PRIMARY KEY) permet de créer automatiquement la séquence obstecoul_test_idauto_seq et définir la valeur, non nulle, par défaut au résultat de nextval('obstecoul_test_idauto_seq')
Il suffit donc de créer la table avec
DROP TABLE IF EXISTS obstecoul_test CASCADE ;
CREATE TABLE obstecoul_test
(
idauto SERIAL PRIMARY KEY,
identifian character varying(254),
statut_cod numeric(10),
statut_nom character varying(254)
) ;puis importer les données avec COPY en précisant bien les colonnes correspondant au contenu du csv (sinon la première colonne du csv ira dans la colonne idauto générant probablement une erreur due à une incohérence de types).
idauto sera renseignée autmatiquement
Concernant l'erreur, il faut vérifier les droits accordés à l'utilisateur exécutant la requête ainsi que la valeur du search_path
Bonjour,
Le message me semble clair : la table schema2.matable n'existe pas sur le serveur distant
Il suffit de créer des "enregistrements de serveur" avec les utilisateurs différents ou bien j'ai mal compris la question ?
Pour la fonction ce serait quelque chose du style (non testé):
CREATE OR REPLACE FUNCTION public.sig_count3(v_table_source CHARACTER VARYING, v_table_dest character varying)
RETURNS TABLE (plandxf CHARACTER VARYING, nb_destination INTEGER, nb_source INTEGER) AS
$BODY$
BEGIN
RETURN QUERY EXECUTE FORMAT('select
%I.plandxf as plandxf,
count(distinct %I.gid) as nb_destination,
count(distinct %I.gid) as nb_source
from %I
left join %I
on %I.plandxf = %I.plandxf
group by %I.plandxf',v_table_dest,v_table_dest,v_table_source,v_table_dest,v_table_source,v_table_source,v_table_dest,v_table_dest);
END
$BODY$
LANGUAGE plpgsql;qui pourrait être utilisée ainsi
select * from sig_count3('matable2','matable1') UNION select * from sig_count3('matable2','matable3');Bonjour,
Cela ne répond pas exactement à la question sur la fonction, mais une solution est de faire une UNION de requêtes.
select
matable1.plandxf as plandxf,
count(distinct matable1.gid) as table_destination,
count(distinct matable2.gid) as table_source
from monschema.matable1
left join monschema.matable2
on matable2.plandxf=matable1.plandxf
group by matable1.plandxf
UNION
select
matable3.plandxf as plandxf,
count(distinct matable3.gid) as table_destination,
count(distinct matable2.gid) as table_source
from monschema.matable3
left join monschema.matable2
on matable2.plandxf=matable3.plandxf
group by matable3.plandxf
UNION
select
matable4.plandxf as plandxf,
count(distinct matable4.gid) as table_destination,
count(distinct matable2.gid) as table_source
from monschema.matable4
left join monschema.matable2
on matable2.plandxf=matable4.plandxf
group by matable4.plandxfAvec :
table source: matable2
tables destination : matable1, matable3, matable4
Avec une fonction, pour avoir un tableau de synthèse, il faudra de toute façon faire une UNION de requêtes
Le copier/coller est utilisable en double cliquant au préalable sur la cellule devant recevoir la donnée. mais il n'est pas possible d'affecter plusieurs cellules en bloc par copier/coller et cela perd donc de son intérêt.
Il faut passer par une requête UPDATE pour faire des affectations en bloc (ce qui est un peu moins simple mais beaucoup plus puissant).
Votre perplexité me laisse dubitatif ...
Si vous consultez la doc, vous verrez que "serial" est en fait un pseudo-type qui permet d'avoir une incrémentation automatique d'une valeur entière (un numéro de série). Le fait de définir manuellement (par copier/coller ou autre moyen) la valeur d'un telle colonne perturberait ce mécanisme et conduirait tôt ou tard à un doublon.
Et le type "serial" est très souvent utilisé pour définir une clé primaire ("bigserial" également).
C'est pour cela que PG Admin affiche cette alerte et que Julien trouve à juste titre qu'il est mieux de ne pas pouvoir la désactiver.