How to do an update with XML column ?

Dear all, I'm taking the liberty to ask an question. Do you know if there are some ways in order to update and/or do group by with an XML column ? For instance, select id, unnest(xpath( '//cit:CI_Organisation/cit:name/gco:CharacterString/text()', CAST(data AS XML), ARRAY[

Extract values from XML content

Good afternoon, I have a table 'metadata' with 2000 records. With one column 'id' and one column 'data' with XML content. I need to extract for all records the values regarding the Organisation names. I success in querying without error message thanks to this following sql query : SELECT id, xpath

Move all elements toward another schema?

Good afternoon, With postgresql 13, I want to find a way to move 100 tables from schema 'A' to schema 'B'. Not just data. But also indexes, primary keys, constraints (INCLUDING ALL). As far as i know, this piece of code would move the data. But how to also move indexes, constraints, primary key?

Repear operations on 50 tables of the same schema?

Good morning, I am new to Postgresql. I have 50 tables into a "ign" schema (schema other than public). I would like for each of these 50 tables: - Add a prefix to the name of the table: "IGN_bdTopo_" - Add a suffix to the table name: "_V1" - create a new "date" column of date type. And populate th

Fwd: Postgresql/Postgis: Trigger for historization/versioning

Good evening, I work with Postgresql 13, Postgis (and Qgis 3.22.) My need is to set up within my Postgis database (used for maps production), triggers for automation of data historization/versioning. The idea: For instance within a table 'BOREHOLE' (geometry: points), different updates of the table

Give default privileges to another SuperUser ?

Good morning, I'm a superUser. I have defined different roles for several "simple" users. They have rights for read/select/use on any existing table. And also on any table that will be generated in the future (DEFAULT PRIVILEGE). Here is my method: --FOR PUBLIC DIAGRAM > GRANT USAGE ON SCHEMA pu

How to disable read-only mode on a table? (superUser)

Good morning, I created a new table yesterday. I am superuser and I have all the rights. I had no worries yesterday to make editions on this table. Today all my fields are in readonly mode. As this post illustrates: https://bytes.com/topic/postgresql/answers/973206-how-remove-read-only-column-sig

Postresql/postgis/qgis : assign privileges only for access/reading tables ?

Good morning, I work on Postgresql/postgis and Qgis. I had created a superuser account for me. In qgis, I can see the postgis schemas and tables and browse the tables without problem. This time, i created another simple user account just to be able in qgis to access and load the table in qgis.

Grant select for all tables of the 12 schemas of my one db ?

Good morning, I work on Postgresql 13 (windows) and Postgis. For some "basic USERS", i have to grant select/read for all tables of the 12 schemas of my db ? With Postgresql 13, i am obliged to write : *GRANT SELECT ON ALL TABLES IN SCHEMA TO username ?* Or a easiest way is possible? With Postg

move data repository : server does not restart (windows 10)

Good morning, I want to move the data folder from default path to another drive/path. I made an attempt thaks to this link ressource : https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows I checked the 4 requirements. But the postgres server does not restart. Could y