Re: pg_restore remap schema

> > People have asked for such a thing before, but it'd be quite difficult > to do reliably --- particularly inside function bodies, which aren't > normally parsed at all during a dump/restore. If you're willing to > accept a 95% solution, running the pg_restore output through "sed" > would likely

toast useless

create table test(x integer, y integer, w varchar(50), z varchar(80)); When a table has well defined sizes all their data is stored on heap, so they doesn´t need to be toasted, and no toast table is created. Fine. But then I want to store last modification of that record, so I do ... alter table t

Re: toast useless

> > What problem do they cause you? > They don't cause any problem, I was just trying to get my database as clean as possible. I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them But if there is no way, ok

get user info on log

When using set role or set session authorization and an error occurs I get user name which logged in and not the one I´ve changed to. There is a way to get session_user and current_user on log ? Thanks Marcos

massive update on gin index

In a table with people's info I have 3 phone numbers, mobile, work and home. But then some have 2 mobiles, some have 2 work numbers, so decided to test it as an array of json. I know I could have another table for that, but I was just testing. So my original table had Mobile, Work, Home and all of

Re: massive update on gin index

> > Did you try a simple array of phone numbers? If you really care about > mobile,work,home prepend the number with one of HMW. Easily stripped off > as necessary. I've had decent performance with arrays in the past. > I know I have other options, and possibly better, but I was trying to unders

Re: massive update on gin index

Em qua., 14 de set. de 2022 às 16:55, Tom Lane escreveu: > GIN does have a "pending list" of insertions not yet pushed into the main > index structure, and search performance will suffer if that gets too > bloated. I don't recall much about how to control that, but I think > vacuuming the table

Re: get user info on log

Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver < adrian.kla...@aklaver.com> escreveu: > test(5432)=# set role maura; > ERROR: role "maura" does not exist > test(5432)=# SET SESSION AUTHORIZATION 'maura'; > ERROR: role "maura" does not exist > > No, I was asking about an error occurring late

Re: Finding free time period on non-continous tstzrange field values

> > Given the following table, how do I find free time period. > https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3

Re: toast useless

Simon Riggs escreveu: > On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro wrote: > >> > >> What problem do they cause you? > > > > > > They don't cause any problem, I was just trying to get my database as > clean as possible. > > I have some th

Understanding years part of Interval

I was just playing with some random timestamps for a week, for a month, for a year ... select distinct current_date+((random()::numeric)||'month')::interval from generate_series(1,100) order by 1; It´s with distinct clause because if you change that 'month' for a 'year' it´ll return only 12 rows,

Re: How to pass table column values to function

Em sáb., 11 de fev. de 2023 às 07:10, Andrus escreveu: > Hi! > > Table source contains integer column. Its values should be passed to > function for selecting data from other table. > > I tried > > CREATE OR REPLACE FUNCTION > public.TestAddAssetTransactions(dokumnrs int[]) > RET

Re: Move all elements toward another schema?

> > As far as i know, this piece of code would move the data. But how to also > move indexes, constraints, primary key? > > create schema if not exists a; create schema if not exists b; create table a.a(id integer not null constraint pk_a primary key, name text); create index idx_a_name on a.a(name

CTE, lateral or jsonb_object_agg ?

I have a table like pg_settings, so records have name and value. This select is really fast, just 0.1 or 0.2 ms, but it runs millions of times a day, so ... Then all the time I have to select up to 10 of these records but the result has to be a single record. So it can be done with ... --Using CT

Re: DB migration : Sybase to Postgres

Em qui., 25 de mai. de 2023 às 08:30, Sengottaiyan T escreveu: > Is there an option to set novalidate constraints in postgres? In my source > Sybase DB, table structures are not defined properly (only primary keys > exist and no foreign key) - I'm making necessary changes on target Postgres > DB

vacuum to prevent wraparound

I have a replica server using Postgres 14.4. Replica is done using Publication/Subscription and I have triggers for auditing, so every record which comes from production is audited. Some months ago I changed the way I was auditing and replaced that audit table for a new one, but didn't remove the o

Re: Active Active PostgreSQL Solution

Postgres 16 will have origin on logical replication, so you can have Active Active mode. regards, Marcos Em sex., 9 de jun. de 2023 às 07:41, Mohsin Kazmi escreveu: > Hello Everyone, > > I have been working on PostgreSQL databases for the last three years and I > have also migrate databases fro

Re: Active Active PostgreSQL Solution

Em sex., 9 de jun. de 2023 às 22:22, Bruce Momjian > > Uh, I have no idea what that means and I didn't think we had active-active > in version 16 https://postgrespro.com/blog/pgsql/5969859#commit_36628396 Regards Marcos -- Atenciosamente,

Re: Trigger Function question

> > Is there a way to get new.* into a jsonb column? > select json_object_agg(js.key, js.value) from json_each_text(row_to_json(new.*)) js Marcos

Re: Read only user permission

Have you tried grant pg_read_all_data to readonly ? regards Marcos Em qua., 23 de ago. de 2023 às 14:30, Hellen Jiang < hji...@federatedwireless.com> escreveu: > Hi, > > > > I have created readonly and readwrite roles with the following grants: > however, readonly user does not have access to th

Check column result size in functions

My functions should return varchar(5) or should return an exception, but sometimes they return CHARACTER VARYING. Why ? create or replace function f_sql() returns varchar(5) language sql as $$select '0123456789'$$; --works, but shouldn't select * from f_sql(); create domain T5 as varchar(5); crea

Re: How to do faster DML

> > insert into mytable2 select * from mytable1 order by ctid limit 10_000_000 > offset 20_000_000; > You can use min/max values grouping them by 10_000_000 records, so you don´t need that offset, then generate commands and run them. select format('insert into mytable2 select * from mytable1 wher

Documentation diff

It's not easy to see what exactly changed between this and that version, on a specific page on documentation. As an example I got this page but you'll see this problem anywhere on docs. https://www.postgresql.org/docs/16/functions-json.html and https://www.postgresql.org/docs/devel/functions-json.

Re: Documentation diff

> > Which problem are you trying to solve? You should be reading the version > of > the docs which corresponds to the version you are running. If you are > trying > to figure out an upgrade then the release notes are probably a better > starting > point. Daniel Gustafsson > All the time we have

Re: Documentation diff

> jian he escreveu: > but I guess the OP wants a side by side rendered html comparison. > like you buy a iphone then you can compare it like this: > https://www.apple.com/iphone/compare/ No, I want show what changes ocurred on both versions, the page your ar

Re: CLUSTER vs. VACUUM FULL

Em dom., 21 de abr. de 2024 às 22:35, David Rowley escreveu: > > Both VACUUM FULL and CLUSTER go through a very similar code path. Both > use cluster_rel(). VACUUM FULL just won't make use of an existing > index to provide presorted input or perform a sort, whereas CLUSTER > will attempt to choo

Re: CLUSTER vs. VACUUM FULL

Em seg., 22 de abr. de 2024 às 11:25, Tom Lane escreveu: > No ... what would be the difference then Well, I think if a cluster index was defined sometime in a table, it should be respected for next commands, including VACUUM FULL. If I want to come back to PK or any other index I would use CLUS

Re: query multiple schemas

Em dom., 21 de abr. de 2024 às 17:12, Norbert Sándor < sandor.norb...@erinors.com> escreveu: > Hello, > > I have a database with multiple tenants with a separate schema for each > tenant. > The structure of each schema is identical, the tenant ID is the name of > the schema. > > What I would like

Length returns NULL ?

There are some functions called ...length, but only array_length returns NULL on empty array, why ? select array_length('{}'::text[],1), -->NULL jsonb_array_length('[]'), -->0 bit_length(''), -->0 octet_length(''), -->0 length(''), -->0 char_length(''), -->0

Re: Determining if a table really changed in a trigger

> > > PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL > (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; > IF FOUND THEN > ... changed ... > END IF; > > Maybe converting new and old records to json and text PERFORM * FROM (select ID, row_to_json(O.*)::text Old_

Re: Determining if a table really changed in a trigger

> > > Don’t use this approach with JSON (as opposed to JSONB) type fields > though, a single extra space in the JSON structure would already lead to a > difference, as would other formatting differences. > > I don´t think two equal values being converted to json will be different in any way. If row

Re: Determining if a table really changed in a trigger

> > Oh, very interesting. I thought that this is not possible because WHEN > condition on triggers does not have NEW and OLD. But this is a very > cool way to combine rules with triggers, where a rule can still > operate by row. > > That is not true create table test(i integer); create function te

Re: Logical replication issue after Postgresql upgrade from 13 to 14

> I have an issue with logical replication after Postgresql upgrade from > 13 to 14 (upgraded subscriber only using pg_upgrade_cluster -m link 13 > main). After upgrade all subscriptions were disabled so I have enabled > them and replication workers successfully started. > pg_stat_subscription cont