PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 Re : Installation » pour le superutilisateur***unknown variable superacoun***) » 28/08/2023 15:33:37

La réponse est dans le commentaire #2 de cette discussion: il faut utiliser psql -U postgres

#2 Re : Général » Mots triés différemment entre Postgresql 12 et Postgresql 15 » 09/08/2023 16:01:47

La différence se situe au niveau des caractères avec accent circonflexe

En fait il y a beaucoup plus de différences.
Quelques exemples ici: https://blog-postgresql.verite.pro/2018 … grade.html

Seule la collation "C" ou "POSIX" offre une garantie absolue de permanence des tris lors des montées de version des OS, et de tri identique entre OS différents.

#3 Re : Général » pg_basebackup et sspi » 10/07/2023 11:59:57

pg_basebackup utilise le protocole de réplication, qui sollicite des règles à part dans le pg_hba.conf.

Pour activer l'authentification voulue, Il faudrait dans les lignes ci-dessous, remplacer scram-sha-256 par sspi map=MapForSSPI

host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

#4 Re : Général » Mauvaise configuration des log dans postgresql.conf. » 14/06/2023 15:03:10

Les paquets Ubuntu/Debian configurent une rotation de log via lograte avec cette configuration:

$ cat /etc/logrotate.d/postgresql-common

/var/log/postgresql/*.log {
       weekly
       rotate 10
       copytruncate
       delaycompress
       compress
       notifempty
       missingok
       su root root
}

Remplacer weekly par daily dans ce fichier est le plus simple pour avoir un fichier par jour, sauf que le changement de fichier ne se fait pas exactement à 0h.

Si on veut exactement un fichier par jour 0h->minuit, il faut changer la configuration dans postgresql.conf avec logging_collector à on, un log_filename et un log_rotation_age personnalisés.

#5 Re : Général » Confusion avec AND et OR » 22/05/2023 19:42:16

Il y a une erreur d'inversion de a et b dans ce test:

(a IS NULL AND b IS NULL OR a > 0 AND b < 13 AND a > 22 AND b <= 99)

Une fois l'erreur corrigée les résultats sont comme attendus.


Le AND est prioritaire par rapport à OR donc effectivement les parenthèses autour des expressions entourant le OR sont facultatives.

#6 Re : Général » Restauration table par table et contraintes externes » 10/05/2023 18:03:23

Dans le 1er message il est question de faire DELETE, pas DROP ni TRUNCATE qui effectivement ne sont pas possibles en présence des contraintes d'intégrité référentielles.

#7 Re : Général » Restauration table par table et contraintes externes » 10/05/2023 17:31:45

L'erreur du pg_restore citée au 1er message est une violation de clef primaire qui suggère le DELETE FROM ma_table_à_restaurer a été fait dans une transaction non committée.


Le pg_restore se faisant dans une autre transaction, de son point de vue les données de ma_table_à_restaurer sont toujours là.


Pour faire marcher votre idée, il faudrait tout faire dans la même transaction, donc au lieu de jouer pg_restore directement sur la base il faut lui demander de créer un fichier avec les commandes SQL de restauration (option -f- de pg_restore) et ensuite passer ces commandes dans la même transaction que le reste (\i sous psql) après le DELETE et avant le COMMIT.

#8 Re : Général » Contrainte d'unicité » 28/04/2023 18:01:15

La collation influe sur le résultat de la comparaison des chaînes de caractères.

Ici la contrainte d'unicité sur (code_cad, code_ds, no_lot) implique qu'à l'insertion ou MAJ d'une ligne, le moteur vérifie qu'il n'y a pas déjà une valeur pour laquelle il y aurait code_cad=nouveau code_cad ET code_ds=nouveau code ds ET no_lot=nouveau no_lot.


Comme il y a une collation personnalisée affectée à la colonne no_lot, la comparaison no_lot=nouveau no_lot se fait avec cette collation. Sans savoir comment est définie cette collation (ça peut se voir avec select * from pg_collation WHERE collname='numeric') et quelle est la valeur de no_lot qu'il ne reconnait pas comme un doublon alors que c'en serait un, difficile de dire si c'est la faute de la collation.


Surtout dans ce sens là d'ailleurs. Si c'était une valeur différente qui était égale d'après la collation, ce serait plus compréhensible (par exemple abc=ABC pour une collation case-insensitive). Mais deux valeurs réellement identiques à l'octet près dont la collation dit qu'elle sont différentes, là je ne vois pas bien quelle collation pourrait dirait ça. Une autre piste pourrait être plus simplement des caractères invisibles dans les valeurs, avec Unicode ça arrive. Dans ce cas, le fait qu'il y a une collation personnalisée serait une coïncidence.

#9 Re : Général » Contrainte d'unicité » 27/04/2023 22:09:42

On voit que cette collation non standard intervient dans la contrainte unique:


no_lot character varying(255) COLLATE public."numeric" NOT NULL,

Quelle est la définition de cette collation?

Est-ce qu'une requête SELECT avec un GROUP BY sur les colonnes de la contrainte et HAVING count(*)>1 renvoie effectivement des lignes?

#10 Re : Général » Connexion base locale et looker studio » 17/04/2023 18:33:51

Nom de l'hôte: localhost   (j'ai essayé 127.0.0.1)

Si la BDD est herbergée sur un serveur distant il faut mettre le nom ou l'adresse IP du serveur distant ici.


"localhost" en tant que nom réseau veut dire "en local" et est interprété du point de vue de celui qui se connecte. Quand vous dites au service looker studio de se connecter à localhost, vous lui dites de se connecter à lui-même en fait.

#11 Re : pgAdmin4 » Récupération de données classées par tranches » 31/03/2023 14:49:40

Il y a apparemment deux problèmes  liés à GROUP BY dans cette requête.


1) Il faut utiliser WHERE et non pas HAVING pour filtrer les lignes de la table missions. L'utilisation de HAVING est réservée aux conditions qui s'appliquent aux résultats aggrégés avec GROUP BY.


2) le GROUP BY tranche semble ne pas avoir de sens puisque la tranche est elle même le résultat d'une aggrégation par semaine.


Si la requête est trop complexe il faut il y aller progressivement et la construire étage par étage en validant les étapes intermédiaires.

#12 Re : Installation » Connexion à une database » 21/03/2023 16:57:51

J'imagine que PostgreSQL est installé sur un poste Windows et que pgAdmin est lancé sur le même PC (pgAdmin et PostgreSQL sont deux logiciels distincts: pgAdmin est un client et PostgreSQL un serveur).

Par rapport à la copie d'écran dans le champ en haut Hostname/address if faut mettre localhost comme valeur.

#13 Re : Migration » Migration pg 9.6 vers pg13 » 20/03/2023 17:14:08

leandre a écrit :

Problème, des vues (au moins 1) m'a retourné une erreur :
elle utilise l'attribut consrc de pg_constraint de la 9.6 qui n'existe plus à partir de pg12.


Est-ce que pg_upgrade de pg11 prend ce changement en considération et effectuera les changements ? Cela signifierait migrer de 9.6 vers 11, puis 11 vers 12 et enfin 12 vers 13 ?
Sinon, dois-je supprimer les vues en question avant et les recréer ensuite (post migration) ?

Non pg_upgrade ne peut pas gérer ça. La doc recommande d'utiliser la fonction  pg_get_constraintdef() pour retrouver la définition d'une contrainte au lieu de la colonne consrc qui n'existe plus à partir de la version 12.

Le faire avant ou après la migration, c'est à vous de voir ce qui est le plus pratique pour vous.

#14 Re : Installation » Connexion à une database » 18/03/2023 17:11:42

Il y a des services web ouverts au public qui permettent de saisir du SQL dans une page web pour s'entraîner à faire des requêtes, mais généralement pas de s'authentifier via un client SQL. Par exemple sqlfiddle.com est assez connu mais il y en a d'autres.


Concernant les bases opendata, il y en a plein mais sous forme téléchargeable de fichiers SQL ou de fichiers CSV ou JSON, il faut les
charger dans son propre serveur.


Si vous avez installé PostgreSQL sur votre PC vous pouvez vous authentifier sur votre propre serveur et faire ce que vous voulez dessus.

#15 Re : Migration » Migration pg 9.6 vers pg13 » 09/03/2023 14:38:37

La première requête n'est pas correcte car elle joint  avec la seule condition pc.relname=pt.tablename, en ignorant le schéma.
Plutôt utiliser cette version:

select n.nspname, c.relname from 
pg_class c join pg_namespace n on c.relnamespace=n.oid 
and nspname!='pg_catalog' 
and relhasoids;

#16 Re : Migration » [Résolu] problème requête après migration de MariaDB vers postgresql » 07/03/2023 21:03:54

Dans la requête du message #1 il y  a un S1.troop_id impliqué dans le GROUP BY qui potentiellement change le nombre de lignes du résultat. Et autant pour les autres colonnes on comprend intuitivement à quoi elles correspondent, autant pour celle-là il faudrait des explications pour savoir quoi faire avec et comprendre en quoi elle influe sur le résultat.


Pour l'absence possible de 2eme date d'observation, en effet c'est un LEFT JOIN entre S1 et S2 donc S2 va avoir des colonnes correspondantes à NULL dans ces cas. C'est gérable avec le fenêtrage, pour ces cas la colonne first_date va être égale à last_date pour le même individu. Les colonnes de la 2nde observation inexistante pourraient être mises à NULL dans un étage supérieur de la requête avec des expressions du style CASE WHEN date2=date1 THEN null else date2 END.

#17 Re : Migration » [Résolu] problème requête après migration de MariaDB vers postgresql » 07/03/2023 17:42:45

Peut-être qu'un fenêtrage serait une bonne solution ici. A ce que je comprends on cherche la 1ere et dernière date d'observation par individu, et en même temps certaines autres colonnes liées à ces observations (c'est là que mysql simplifie les choses alors que le standard non)

L'idée serait de déclarer une fenêtre de ce genre là:

WINDOW w AS (PARTITION BY individual_ID ORDER BY date ROWS between unbounded preceding and unbounded following)

Ensuite on va parcourir un seule fois la table sightings (pas d'auto-jointure) et demander la 1ere et dernière ligne de chaque partition, avec ce genre d'expression

SELECT
  first_value(date) OVER w as "first date",
 last_value(date) OVER w as "last_date",
 first_value(autre_colonne) OVER w As "valeur d'une autre colonne correspondant à first date",
 last_value(autre_colonne) OVER w As "valeur d'une autre colonne correspondant à last date",
etc...
FROM sigthings
WINDOW w AS (PARTITION BY individual_ID ORDER BY date ROWS between unbounded preceding and unbounded following)

Ensuite pour aggréger ce résultat et ne garder qu'une ligne par partition on peut ajouter un étage supérieur DISTINCT ON (voir https://www.postgresql.org/docs/current … -DISTINCT). Généralement on ordonne les lignes présentées à DISTINCT ON par l'expression de déduplication mais dans ce cas particulier il n'est même pas nécessaire de faire un ORDER BY parce que toutes les lignes relatives à un même individual_ID (même partition) vont être égales.


A l'étage encore au-dessus il y aurait une jointure avec la table individuals via individual_ID pour retrouver les colonnes de cette table du style "Name", "Sex".

#18 Re : Installation » Méthode de démarrage serveur » 10/01/2023 15:57:07

Avec Debian chaque instance postgres a son répertoire de config , par exemple /etc/postgresql/14/main si c'est Postgres version 14 et que le nom du cluster est "main" (le nom par défaut).

Dans le doute, utiliser la commande pg_lsclusters pour avoir la liste des instances.

Dans ce répertoire il y a un fichier start.conf avec ce contenu:

# Automatic startup configuration
#   auto: automatically start the cluster
#   manual: manual startup with pg_ctlcluster/postgresql@.service only
#   disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.

auto

Remplacer auto par manual pour ne pas démarrer automatiquement.

Source: https://manpages.debian.org/buster/post … .1.en.html

#19 Re : PL/pgSQL » Vue modifiable sur vue matérialisée pour compléter autre table général » 30/12/2022 21:51:19

Dans le trigger:

    RETURN NEW;

    -- mettre à jour la vue matérialisée
    
    REFRESH MATERIALIZED VIEW schema_1.v_mat_1_utilisateur1; 

Le RETURN NEW fait sortir du trigger donc le REFRESH n'est pas exécuté. Le RETURN NEW doit se faire en dernier.


Sinon d'un point de vue conception, ça paraît extrêmement lourd de rafraîchir une vue matérialisée à chaque mise à jour de ligne.

Peut-être qu'il serait intéressant de creuser les raisons pour lesquelles les vues non matérialisées limitant les données exposées à QGIS ne donnent pas satisfaction. Est-ce que c'est les requêtes qui sont lentes ou c'est QGIS?
Si c'est les requêtes, il faudrait faire des EXPLAIN ANALYZE pour voir où le temps est consommé et pourquoi. Il y a des conseils à ce sujet dans https://wiki.postgresql.org/wiki/Slow_Query_Questions

#20 Re : PL/pgSQL » Nom colonne dans fonction de suivi de modification de table » 15/11/2022 14:16:33

Pour avoir la liste des colonnes changées quelque soit la structure de la table, on peut utiliser une requête de ce style, qui fait l'intersection de OLD et NEW pour ne garder que ce qui change:

    select array_agg(k2)
     from (select * from jsonb_each_text(row_to_json(OLD)::jsonb)) as r1(k1,v1)
       join (select * from jsonb_each_text(row_to_json(NEW)::jsonb)) as r2(k2,v2)
       on k1=k2
     where v1 is distinct from v2;

Il y a une version avec hstore aussi, où on peut faire encore plus simplement

hstore(NEW.*) - hstore(OLD.*)

Mais hstore étant rendu obsolète par jsonb, ce n'est pas forcément une bonne idée de partir là dessus aujourd'hui.

La version "toutes options" du trigger d'audit avec hstore est maintenue ici:
https://github.com/2ndQuadrant/audit-tr … /audit.sql

#21 Re : Général » ERROR: invalid byte sequence for encoding "UTF8": 0xe8 0x59 0x45 postg » 09/11/2022 16:30:44

Il faudrait regarder dans les logs du serveur s'il y a un autre message. C'est peut-être ce message là qui a un problème d'encodage.

#22 Re : pgAdmin4 » Mise à jour clé primaire » 14/10/2022 20:24:42

La redistribution de la clef primaire échoue via l'UPDATE du fait que Postgres ne teste pas l'unicité à la fin de l'instruction, comme indiqué ici dans la doc:

https://doc.postgresql.fr/14/sql-set-constraints.html

De plus, PostgreSQL vérifie les contraintes uniques non déferrables immédiatement, pas à la fin de l'instruction comme le standard le suggère.


Pour que l'UPDATE passe, il faudrait que la contrainte soit DEFERRABLE INITIALLY DEFERRED ou DEFERRABLE tout court et que la transaction la passe en DEFERRED.


L'autre solution est de ne pas faire un UPDATE mais de réinsérer les lignes avec DELETE+INSERT:


with d as (delete from table returning *)
 insert into table select row_number() over (order by Nom), Nom from d;

Je suggère row_number() au lieu de rank() parce que rank() créé des doublons en cas d'égalité

#23 Re : Général » le type "timetz" et "timestamptz" dans la documentation » 25/09/2022 20:00:48

timestamptz est l'abbréviation de timestamp with time zone

C'est précisé dans la doc via cette note:

Le standard SQL impose que timestamp soit un équivalent de timestamp without time zone. timestamptz est accepté comme abréviation pour timestamp with time zone ; c'est une extension PostgreSQL.

#24 Re : Général » Incrémentation d'une colonne via un curseur » 22/09/2022 19:45:29

En vrac, il y a plusieurs soucis visibles dans le code montré


- BEGIN MAJ

Pourquoi MAJ? La syntaxe valide c'est BEGIN tout seul.


- FETCH ALL FROM cLecture;

La syntaxe est FETCH [ direction { FROM | IN } ] curseur INTO cible;

ALL n'est pas possible. La cible devrait probablement être rt la variable déclarée mais pas utilisée. S'il n'y a aucune cible à avoir, un MOVE cLecture ferait mieux l'affaire.

Attention au fait que la syntaxe du FETCH en plpgsql diffère du FETCH du SQL. C'est piégeux mais c'est une règle générale. Le plpgsql a sa propre syntaxe qui ressemble superficiellement au SQL mais qui diffère à cause des variables et du côté procédural.



- COMMIT à supprimer


- la requête du curseur n'a pas d'ORDER BY donc il faut voir que les valeurs successives de NB vont arriver dans les lignes dans un ordre indéterminé.

#25 Re : Général » Incrémentation d'une colonne via un curseur » 20/09/2022 20:43:54

Est-ce un exercice? Parce qu'en dehors d'un exercice, ça n'a aucun intérêt de faire une boucle au lieu d'un seul UPDATE qui met à jour toutes les lignes.


A part ça, il faut regarder la doc des curseurs en plpgsql pour les questions de syntaxe et d'usage: https://docs.postgresql.fr/14/plpgsql-cursors.html


Sinon le COMMIT en milieu de boucle pose problème: un COMMIT ferme la transaction en cours et par extension les curseurs qui sont ouverts dedans, sauf s'il sont marqués WITH HOLD. Mais pourquoi faire un COMMIT ici? Quel intérêt?

Pied de page des forums

Propulsé par FluxBB