Re: append jsonb array to another jsonb array

Hi, David, Pavel, Thank you for your indications Joao On Wed, Feb 17, 2021 at 7:50 PM Pavel Stehule wrote: > Hi > > st 17. 2. 2021 v 20:44 odesílatel Joao Miguel Ferreira < > joao.miguel.c.ferre...@gmail.com> napsal: > >> Hello all, >> >> I PL/pgSQL I have a few jsonb variables (instantiated

Re: append jsonb array to another jsonb array

Hi st 17. 2. 2021 v 20:44 odesílatel Joao Miguel Ferreira < joao.miguel.c.ferre...@gmail.com> napsal: > Hello all, > > I PL/pgSQL I have a few jsonb variables (instantiated each with an array > of objects) and I need to append them to produce the final result. All of > them are small in size (ma

Re: append jsonb array to another jsonb array

On Wednesday, February 17, 2021, Joao Miguel Ferreira < joao.miguel.c.ferre...@gmail.com> wrote: > We are using Pg 11. > > Example: > a = [ x, y ] > b = [ z , w ] > result would be [ x, y, z, w ] > > What would you consider to be a suitable approach ? > That’s called concatenation. https://www.

append jsonb array to another jsonb array

Hello all, I PL/pgSQL I have a few jsonb variables (instantiated each with an array of objects) and I need to append them to produce the final result. All of them are small in size (max 30 each). We are using Pg 11. Example: a = [ x, y ] b = [ z , w ] result would be [ x, y, z, w ] What would y

Re: Slow index creation

The st_area calculation is done mostly once or sometimes twice for each geom, and I suspect that can't explain the factor 20 slower. Creating an index with only one st_area calculation is also done rather quickly. On Wed, Feb 17, 2021 at 7:48 PM hubert depesz lubaczewski wrote: > On Tue, Feb 16,

Re: Slow index creation

On Tue, Feb 16, 2021 at 07:30:23PM +0100, Paul van der Linden wrote: > Hi, > I have 2 functions: > CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS > $func$ > DECLARE >     retVal text; > BEGIN >     SELECT >       CASE >         WHEN a='v1' AND b='b1' THEN 'r1' >         WHEN a='v1' THE

Re: Slow index creation

On 2/16/21 12:30 PM, Paul van der Linden wrote: Hi, I have 2 functions: CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS $func$ DECLARE     retVal text; BEGIN     SELECT       CASE         WHEN a='v1' AND b='b1' THEN 'r1'         WHEN a='v1' THEN 'r2'         ... snip long list contai

Re: How to return a jsonb list of lists (with integers)

st 17. 2. 2021 v 16:02 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > I have tried switching to SELECT INTO, but IF FOUND is still always true, > which gives me [ null, null, null ] for some users: > > SELECT JSONB_BUILD_ARRAY( > SUM(CAS

Re: How to return a jsonb list of lists (with integers)

st 17. 2. 2021 v 15:34 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > Hi Pavel, > > why would SELECT INTO be better here? > Minimally it doen't use undocumented feature. And you can be sure, so the query is evaluated really like a query. The expressions are evaluated differe

Logical replication stuck and no data being copied

I'm connecting from an aurora postgresql 12.4 instance to a postgresql 12.6 community edition. The postgresql 12.6 is the publisher and the aurora db is the subscriber. Most of the subscriptions and publishing worked. Except for one table, which I've created a separate publication for. One table

Re: Slow while inserting and retrieval (compared to SQL Server)

sivapostg...@yahoo.com schrieb am 17.02.2021 um 14:27: > We use datawindows. Datawindows will send the required DML > statements to the database. > And it sent in format 1 . > > IN start of the application, Autocommit set to True. > Before update of any table(s) > Autocommit is set to False > Inse

Re: Slow index creation

Well, first off it's annoying if I have to change the function and a reindex afterwards, and secondly, lots of other queries are blocking on that reindex query (basically everything needing a queryplan on that table). Materializing is also an option but that too is taking its time. As far as I kn

Re: Slow while inserting and retrieval (compared to SQL Server)

Yea. Let's see what the metrics actually are. It is possible that the data gets loaded in instantly but for whatever reason you do t see the reflected changes. On Wed, Feb 17, 2021, 9:09 AM sivapostg...@yahoo.com wrote: > So far no performance tuning done for sql server. It works fine for the >

Re: How to return a jsonb list of lists (with integers)

I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users: SELECT JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won') OR (player2 = in_viewer AND state2 = 'won') THEN

Re: How to return a jsonb list of lists (with integers)

Hi Pavel, why would SELECT INTO be better here? Thanks Alex

Re: Slow while inserting and retrieval (compared to SQL Server)

So far no performance tuning done for sql server.  It works fine for the load. Even the express edition which is free, works better.  I don't think postgres will be so slow to insert such a low number of records in an empty database.    I'll be preparing the required sql statements to insert tho

Re: Slow while inserting and retrieval (compared to SQL Server)

Sql server is a 10k dollar to 1 million dollar application (or more) that is specifically optimized for windows and had limited to no support anywhere else. Postgres is free and from my experience, comes within 5% of any other dbms. Inserting 1 row at a time with auto commit on will be a bit slow b

Re: Slow while inserting and retrieval (compared to SQL Server)

We use datawindows.  Datawindows will send the required DML statements to the database.  And it sent in format 1 .   IN start of the application, Autocommit set to True.Before update of any table(s)Autocommit is set to FalseInsert/Update/Delete recordsIf success commit else rollbackAutocommit i

Re: Slow while inserting and retrieval (compared to SQL Server)

sivapostg...@yahoo.com schrieb am 17.02.2021 um 13:01: > To populate some basic data we try to insert few records (max 4 > records) in few tables (around 6 tables) from one window. We feel > that the insert time taken is longer than the time taken while using > Sql Server. We tested almost a sim

Re: Slow while inserting and retrieval (compared to SQL Server)

- Mensagem original - > De: "sivapostgres" > Para: "luis.roberto" > Cc: "pgsql-general" > Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:54:18 > Assunto: Re: Slow while inserting and retrieval (compared to SQL Server) > Hello, > Yes, that's what I feel. With no records in any table

Re: Slow while inserting and retrieval (compared to SQL Server)

Hello,Yes, that's what I feel.  With no records in any tables, working from the same machine where PG has been installed, with only one user working, inserting few records (10 records in total, in all 6 tables)  should not take this much time.   I'll be inserting records from PowerBuilder appli

Re: Slow while inserting and retrieval (compared to SQL Server)

- Mensagem original - > De: sivapostg...@yahoo.com > Para: "pgsql-general" > Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:01:15 > Assunto: Re: Slow while inserting and retrieval (compared to SQL Server) > > Hello, > > Using Postgres 11 in Windows Server 2012 & Powerbuilder > Worki

Re: Slow while inserting and retrieval (compared to SQL Server)

Hello, Using Postgres 11 in Windows Server 2012 & PowerbuilderWorking from the same machine where Postgres 11 is installed.   So no chance for any network issue, I feel. No setup/config change done.  Just working with all the default settings.With no data in any of the 179 tables in that databas

Re: How to return a jsonb list of lists (with integers)

Hi st 17. 2. 2021 v 11:40 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > I have tried the following, but IF FOUND is always false for some reason: > > _versus := JSONB_BUILD_ARRAY( > SUM(CASE WHEN (player1 = in_uid AND state1 = > 'won')

Re: How to return a jsonb list of lists (with integers)

I have ended up with the following (to avoid returning [null, null, null] for players who never played with each other): _versus := JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE

Re: How to return a jsonb list of lists (with integers)

I have tried the following, but IF FOUND is always false for some reason: _versus := JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer, SUM(CA

Re: PostgreSQL Replication

On 2021-02-17 04:22, Mutuku Ndeti wrote: > Thank you.  > > I agree with you. Single master, with a standby replica, seems easier to > manage. Is there a way to automatically promote the standby, when the > active master fails? Is it feasible to have 2 instances of the > application, writing onto t

Re: PostgreSQL Replication

Thank you. I agree with you. Single master, with a standby replica, seems easier to manage. Is there a way to automatically promote the standby, when the active master fails? Is it feasible to have 2 instances of the application, writing onto the same DB, reason for two instances of the applicatio

Re: How to return a jsonb list of lists (with integers)

Hello, thank you for the helpful replies. I have decided to go with PL/PgSQL for now and also switched from JSONB list of lists to map of lists. And the custom stored function below works mostly well, except for a special case - CREATE OR REPLACE FUNCTION words_stat_charts( in_ui

Re: PostgreSQL Replication

On 2021-02-16 09:28, Raul Giucich wrote: > This article will help you > https://wiki.postgresql.org/wiki/Multimaster > .  > > El mar., 16 feb. 2021 10:56, Mutuku Ndeti > escribió: > > Hi, > > Need some advice here.