Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 9:54 PM Tom Lane wrote: > Ken Tanzer writes: > > There's one last piece of this query I'm clearly not getting though. > Where > > it says: > > > from foo as f, jsonb_to_recordset(js) as t(key2 text) > > > what is actually going on there? I keep reading this as a table foo

Re: Extracting data from jsonb array?

Ken Tanzer writes: > There's one last piece of this query I'm clearly not getting though. Where > it says: > from foo as f, jsonb_to_recordset(js) as t(key2 text) > what is actually going on there? I keep reading this as a table foo (f) > cross-joined to a table created by jsonb_to_recordset (

Re: Extracting data from jsonb array?

On Monday, December 7, 2020, Ken Tanzer wrote: > > There's one last piece of this query I'm clearly not getting though. > Where it says: > > from foo as f, jsonb_to_recordset(js) as t(key2 text) > > what is actually going on there? I keep reading this as a table foo (f) > cross-joined to a table

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 8:45 PM Tom Lane wrote: > Ken Tanzer writes: > > On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote: > >> If foo.id is a primary key, it knows that the "group by" doesn't really > >> merge any rows of foo, so it lets you get away with that. I think this > >> is actually requi

Re: Extracting data from jsonb array?

This article might help understanding the reason - https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b >From the postgres docs: "When GROUP BY is present, or any aggregate functions are present, it is not valid for the SE

Re: Extracting data from jsonb array?

Ken Tanzer writes: > On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote: >> If foo.id is a primary key, it knows that the "group by" doesn't really >> merge any rows of foo, so it lets you get away with that. I think this >> is actually required by spec, but am too lazy to go check right now. > If I

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote: > Ken Tanzer writes: > >> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, > >> jsonb_to_recordset(js) as t(key2 text) group by f.id; > > > After a little more thought and experimenting, I'm not so sure about this > > part. In particular,

Re: Extracting data from jsonb array?

You can also do this: b2bcreditonline=# select f.id, f.f1, f.f2, (select array_agg(t.key2) from jsonb_to_recordset(js) as t(key2 text)) as key2s from foo as f; id |f1 | f2 | key2s +---++ 1 | My text 1 | My text 1a | {r1k2val,r1k2

Re: Extracting data from jsonb array?

Ken Tanzer writes: >> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, >> jsonb_to_recordset(js) as t(key2 text) group by f.id; > After a little more thought and experimenting, I'm not so sure about this > part. In particular, I'm not clear why Postgres isn't complaining about > the f1

Re: Extracting data from jsonb array?

What am I missing? b2bcreditonline=# select * from foo; id | js |f1 | f2 ++---+ 1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key":

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer wrote: > > But this has a big advantage in that you can just add other fields to the > query, thusly: > > => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, > jsonb_to_recordset(js) as t(key2 text) group by f.id; > id |f1 | f2 |

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin wrote: > How about this: > > b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f, > jsonb_to_recordset(js) as t(key2 text) group by f.id; > id | array_agg > + > 2 | {r2k2val,r2k2val2} > 1 | {r1k2val,r1k2val2}

Re: Extracting data from jsonb array?

How about this: b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f, jsonb_to_recordset(js) as t(key2 text) group by f.id; id | array_agg + 2 | {r2k2val,r2k2val2} 1 | {r1k2val,r1k2val2} (2 rows) Steve On Tue, Dec 8, 2020 at 1:00 PM David G. Johnston w

Re: Extracting data from jsonb array?

On Monday, December 7, 2020, Ken Tanzer wrote: > > > I'm of course very glad Postgresql has the ability to work with JSON at > all, but as I dig into it I'm kinda surprised at the level of complexity > needed to extract data in relatively simple ways. Hopefully eventually it > will seem simple t

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 5:20 PM Rob Sargent wrote: > > On 12/7/20 6:17 PM, David G. Johnston wrote: > > On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent wrote: > >> >> postgres=# select id, array_agg(fa) from (select id, >> (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id; >>

Re: Extracting data from jsonb array?

On 12/7/20 6:17 PM, David G. Johnston wrote: On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent > wrote: postgres=# select id, array_agg(fa) from (select id, (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;  id |  array_

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent wrote: > > postgres=# select id, array_agg(fa) from (select id, > (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id; > id | array_agg > +-- > 1 | {"\"r1kval\"","\"r1kval2\""} > 2 | {

Re: Extracting data from jsonb array?

OK, let me try asking again.  (I'm trying to actually get something that works.)  So given an example like this: CREATE TEMP TABLE foo (   id INTEGER,   js  JSONB ); INSERT INTO foo (id,js) VALUES (1, '[ {"key":"r1kval","key2":"r1k2val"}, {"key":"r1kval2","key2":"r1k2val2"} ]'); INSERT INTO

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 4:00 PM David G. Johnston wrote: > On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer wrote: > >> >> >> On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin >>> wrote: >>> Try: s

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer wrote: > > > On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin >> wrote: >> >>> Try: >>> >>> select _message_body->'Charges'->>'Name' from ... >>> >> >> Not so much..

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston wrote: > On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin > wrote: > >> Try: >> >> select _message_body->'Charges'->>'Name' from ... >> > > Not so much..."Charges" is an array so "->>" doesn't do anything useful. > > The OP needs to use "json_array_el

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin wrote: > Try: > > select _message_body->'Charges'->>'Name' from ... > Not so much..."Charges" is an array so "->>" doesn't do anything useful. The OP needs to use "json_array_elements" to navigate past the array and get to the next layer of the json

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 3:12 PM Steve Baldwin wrote: > Try: > > select _message_body->'Charges'->>'Name' from ... > > Hi Steve. I tried that again, and that returns a NULL value for me. I believe that is because Charges holds an array of two elements, each of which has a Name element. Though my

Re: Extracting data from jsonb array?

Try: select _message_body->'Charges'->>'Name' from ... Steve On Tue, Dec 8, 2020 at 9:58 AM Ken Tanzer wrote: > Hello. This is probably simple, but I'm having a hard time making use of > some json data, and hoping someone can help. > > Given some data that looks like this (I added a couple o

Extracting data from jsonb array?

Hello. This is probably simple, but I'm having a hard time making use of some json data, and hoping someone can help. Given some data that looks like this (I added a couple of carriage returns for readability): SELECT _message_body->'Charges' FROM message_import_court_case where _message_exchan

Re: Deferrable FK not behaving as expected.

On 12/7/20 3:40 PM, Tom Lane wrote: Ron writes: That works *sometimes*, but not when we *really* want it to work. test=# begin transaction; BEGIN test=# ALTER TABLE sales_header DETACH PARTITION sales_header_202001; ERROR:  removing partition "sales_header_202001" violates foreign key constrain

Re: PL/java

On 12/7/20 3:10 PM, Martin Gainty wrote: Nota Bene: Almost all official interfaces to/from Oracle are coded in Java FWIK google's implementations are 95% python (although you would need to understand JSON formatting for import/export requirements) for those reasons i tread lightly before dispa

Re: PL/java

Nota Bene: Almost all official interfaces to/from Oracle are coded in Java FWIK google's implementations are 95% python (although you would need to understand JSON formatting for import/export requirements) for those reasons i tread lightly before disparaging either language Going Forward: try to

Re: Deferrable FK not behaving as expected.

Ron writes: > That works *sometimes*, but not when we *really* want it to work. > test=# begin transaction; > BEGIN > test=# ALTER TABLE sales_header DETACH PARTITION sales_header_202001; > ERROR:  removing partition "sales_header_202001" violates foreign key > constraint "sales_detail_cust_id_o

Re: Deferrable FK not behaving as expected.

On 12/7/20 1:27 PM, Tom Lane wrote: Ron writes: Neither technique worked. test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; ALTER TABLE Hmm, reproduced here. There seems to be some kind of bug in ALTER CONSTRAINT --- it looks like it'

Re: Deferrable FK not behaving as expected.

On 12/7/20 1:15 PM, Ron wrote: On 12/7/20 1:27 PM, Tom Lane wrote: Ron writes: Neither technique worked. test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; ALTER TABLE Hmm, reproduced here.  There seems to be some kind of bug in ALTER

Re: Deferrable FK not behaving as expected.

On 12/7/20 1:27 PM, Tom Lane wrote: Ron writes: Neither technique worked. test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; ALTER TABLE Hmm, reproduced here. There seems to be some kind of bug in ALTER CONSTRAINT --- Do I need to fi

Re: simple reporting tools for postgres in aws

Hello, If you know Oracle, maybe you also know Sql developer reporting features, and as SQL developer can connect to Postgres, it can be used for free (it is what Oracle claims, but it should be double verified) https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r40/Chart/12cCha

Re: Deferrable FK not behaving as expected.

Ron writes: > Neither technique worked. > test=# ALTER TABLE sales_detail ALTER CONSTRAINT > fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; > ALTER TABLE Hmm, reproduced here. There seems to be some kind of bug in ALTER CONSTRAINT --- it looks like it's updated all the child FKs s

Re: PL/java

> > > > The PL/java “add-on” is not supported directly by Postgres. > > With a few exceptions (Perl, Python) this is how the community handles all "add-ons". A great number of features available to you are available as third party extensions and not part of core. Zombodb, Timescale and Citus are go

Re: Deferrable FK not behaving as expected.

On 12/7/20 12:19 PM, Thomas Kellerer wrote: Ron schrieb am 07.12.2020 um 19:15: Referenced by: TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"    FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think if you only mention "de

Re: Deferrable FK not behaving as expected.

On 12/7/20 12:19 PM, Thomas Kellerer wrote: Ron schrieb am 07.12.2020 um 19:15: Referenced by: TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"    FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think if you only mention "de

Re: Deferrable FK not behaving as expected.

Ron schrieb am 07.12.2020 um 19:15: Referenced by:     TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"   FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think if you only mention "deferrable" this is the same as "deferrable ini

Deferrable FK not behaving as expected.

v12.5 I added a deferrable FK constraint on sales_detail to ensure that no one can delete records from sales_header when an associated sales_detail record exists. That works perfectly. The problem is that try to delete parent records before the child records *inside a transaction* also fail

Re: simple reporting tools for postgres in aws

LibreOffice Base? I have used LibreOffice Base to spin up a quick data entry front end to Postgresql. Has some basic reporting functionality as well, and can integrate to other LIber\Office products. Poor man's version of MS Access. As for other tools, https://querytreeapp.com seems to have som

Re: PostgreSQL Database Upgrade

On 12/7/20 8:35 AM, github kran wrote: Hello PostgreSQL Team, We have recently upgraded the postgreSQL version from V9 to V10. * Is the below command right to update the database statistics after the database upgrade ?. * How do I verify in PG_STATS if the Analyze ran successfully? Do

PostgreSQL Database Upgrade

Hello PostgreSQL Team, We have recently upgraded the postgreSQL version from V9 to V10. - Is the below command right to update the database statistics after the database upgrade ?. - How do I verify in PG_STATS if the Analyze ran successfully? Does it show any time when the last Analy

Re: PL/java

On 12/7/20 8:16 AM, Rob Sargent wrote: On 12/7/20 8:25 AM, Adrian Klaver wrote: On 12/7/20 7:18 AM, Hemil Ruparel wrote: I want to say this. I never liked any extension language. It's like Java is not meant to interact with databases. The JDBC folks might disagree. That interaction is str

Re: PL/java

On 12/7/20 8:25 AM, Adrian Klaver wrote: On 12/7/20 7:18 AM, Hemil Ruparel wrote: I want to say this. I never liked any extension language. It's like Java is not meant to interact with databases. The JDBC folks might disagree. That interaction is strictly sql to db, data to app.  As it sh

simple reporting tools for postgres in aws

Hello, We have a need to display some very simple reports generated from views in an rds postgres database. We need little more functionality beyond simple filtering, column hiding, sorting, grouping. Anything much beyond that would be a strike against the tool. i'm looking for something similar

Re: Extended statistics for correlated columns, row estimates when values are not in MCVs list

On 12/4/20 11:53 PM, Michael Lewis wrote: > On version 12.5, I have a query similar to the below where I am getting > rather unfortunate row estimates and a sub-optimal index choice as a result. > > SELECT > id > FROM > messages > WHERE > client_id = 1234 > and site_id = 889977 > and

Re: PL/java

On 12/7/20 7:18 AM, Hemil Ruparel wrote: I want to say this. I never liked any extension language. It's like Java is not meant to interact with databases. The JDBC folks might disagree. Nor is python (in my opinion). I think you should skip the search On Mon, Dec 7, 2020 at 8:15 PM Adrian K

Re: PL/java

I want to say this. I never liked any extension language. It's like Java is not meant to interact with databases. Nor is python (in my opinion). I think you should skip the search On Mon, Dec 7, 2020 at 8:15 PM Adrian Klaver wrote: > On 12/7/20 3:25 AM, Zé Rui Marques wrote: > > Hi, > > > > I am

Re: PL/java

On 12/7/20 3:25 AM, Zé Rui Marques wrote: Hi, I am the lead architect in a small firm that develops a very specialised ETL platform. Traditionally, we have used Oracle and PL-SQL, and we are now porting everything to Postgres. All of our code outside of the database is java and I wiash tha

PL/java

Hi, I am the lead architect in a small firm that develops a very specialised ETL platform. Traditionally, we have used Oracle and PL-SQL, and we are now porting everything to Postgres. All of our code outside of the database is java and I wiash that all of our code within the database to be a