Fwd: Postgresql/Postgis: Trigger for historization/versioning

2022-09-19 Thread celati Laurent
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

Repear operations on 50 tables of the same schema?

2023-02-27 Thread celati Laurent
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

Move all elements toward another schema?

2023-02-28 Thread celati Laurent
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?

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

2021-08-12 Thread celati Laurent
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

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

2021-10-13 Thread celati Laurent
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

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

2021-10-14 Thread celati Laurent
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.

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

2022-02-16 Thread celati Laurent
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

Give default privileges to another SuperUser ?

2022-02-17 Thread celati Laurent
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

Extract values from XML content

2024-11-13 Thread celati Laurent
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

How to do an update with XML column ?

2024-12-18 Thread celati Laurent
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[