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 are made over time. The version change occurs in the event that: • new objects are added or deleted in the table (INSERT / DELETE) • objects are updated by new versions of objects canceling and replacing existing objects (UPDATE). Sometimes, these UPDATE only concern the geometry column (change of location). Sometimes other fields/attributes (type of borehole, technical referent, name of the campaign for the borehole, start date of construction, name of the municipality where the borehole is located, etc.). => These INSERT/DELETE/UPDATE must have the effect, within the "archive" schema (schema dedicated to the storage of historical/versioned tables/objects), the creation of a new table "BOREHOLE_V1", " BOREHOLE _V2", " BOREHOLE _V3",etc. *Requirements:* • The objective is NOT to perform a version upgrade at each modification (UPDATE) in the table, but to identify structuring phases. In other words, the idea is that the administrator can decide, trigger when he deems relevant, the trigger/history-versioning process. • "Table versioning" way is recommended": the versioning tables (ARCHIVE schema) will be made up of modified elements AND also unmodified objects from the "BOREHOLE" table. This choice was done in particular to facilitate the restoration of versioned objects at the level of archived qgis projects. *Advantage*: possibility of referring to a specific phase of the Instant T project. *Disadvantage*: duplication of objects even unmodified within the archive table. The administrator could activate a historization action when it seems relevant to him. For example at the end of an Qgis editing session. Could someone guide me, direct me to the methods offered by PostgreSQL/Postgis (or even Qgis) likely to satisfy my needs? A big thank-you. ;-) Hoping to have been sufficiently clear.
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 this field with the value: 06/15/2021 - create a new "source" column of type varchar (length 50). And populate this field with the value: 'ign'. - move all the elements of these 50 tables (including all). from the "ign" schema to the "ign_v2" schema. Whether data, constraints, indexes. If someone could help me? Thank you so much.
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?
DO
$$
DECLARED
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'A' --
and other conditions, if needed
LOOPS
EXECUTE format('ALTER TABLE A.%I SET SCHEMA [B];', row.tablename);
END LOOP;
END;
$$;
Thanks so much.
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 you tell me the best way to do this action please ? Thanks a lot.
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 Postgresql 14, it seems to be easiest : https://www.postgresql.org/docs/14/predefined-roles.html In advance, thanks a lot for your help.
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. In pgadmin I created a new role with privileges "can login" only. When i tape : GRANT SELECT ON ALL TABLES IN SCHEMA TO USER the request is well recognized and carried out. But in qgis GIS, I only see the schema structure, I don't see the list of tables. Where can the error come from? Have I forgotten something? Thank you.
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-sign-postgresql I tried a reboot of the postgres services. But nothing to do. If anyone could help me ? Thank a lot.
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 public TO "user"; > GRANT SELECT ON ALL TABLES IN SCHEMA public TO "user"; > ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "user"; --FOR OTHER XXX DIAGRAM > GRANT USAGE ON SCHEMA TO "user"; > GRANT SELECT ON ALL TABLES IN SCHEMA XXX TO "user"; > ALTER DEFAULT PRIVILEGES IN SCHEMA XXX GRANT SELECT ON TABLES TO "user"; This procedure works well when I perform the table creation operation (when I am "grantor"). However, when my SuperUser colleague tries to create a table on his side, with his own superUser connection role, the table is indeed created but is only visible by those who have a superUser connection role: simple users do not seem to be able, by default, to view, access new tables created by my colleague. Does a person know how to give privileges to a SuperUser role so that when he creates a new table, whether in the public schema or another schema, the table will be by default visible, 'selectable' by everyone (by the other roles superUser AND all 'simple' user roles that they only have 'usage' privileges on schemas, ''select' privileges on tables). Hoping to have been rather clear. Thank you so much.
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('/contact/CI_ResponsibleParty/organisationName/CharacterString/text()',
CAST(data AS XML)) AS organisation_name
FROM public.metadata;
But the values don't appear into the column :
"id""organisation_name"
16410"[]"
16411"[]"
16412"[]"
16413"[]"
16414"[]"
16415"[]"
16416"[]"
16423"[]"
16425"[]"
16426"[]"
16427"[]"
16435"[]"
2250 "[]"
16587"[]"
16588"[]"
If needed, i paste below the FULL extract of the XLM content up to my
section of interest :
http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode";
codeListValue="originator" />
Office français de la
biodiversité
Thanks so much.
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[ ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'], ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'], ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'], ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'], ])) as orga_name, changedate, createdate, displayorder, doctype, extra, popularity, rating, root, schemaid, title, istemplate, isharvested, harvesturi, harvestuuid, groupowner, metadata.owner, metadata.source, uuid from public.metadata group by orga_name I obtained : ERROR: could not identify an equality operator for type xml LINE 39: group by orga_name ^ SQL state: 42883 Character: 2556 Thanks so much
