PL/java

[Zé Rui Marques]

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 also in java.

The PL/java “add-on” is not supported directly by Postgres.

I would like to ask if there is a plan to include it as an official procedural 
language. If so, when?

Thanks,
—
Zé Rui Marques

Re: PL/java

[Adrian Klaver]

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 that all of 
our code within the database to be also in java.


The PL/java “add-on” is not supported directly by Postgres.

I would like to ask if there is a plan to include it as an official 
procedural language. If so, when?


None that I have heard of. Even if the decision where to made today it 
would likely a couple of years before it would be included, so that is 
not going to help you. What is your concern?




Thanks,
*—*
*Zé Rui Marques*





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PL/java

[Hemil Ruparel]

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 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 also in java.
> >
> > The PL/java “add-on” is not supported directly by Postgres.
> >
> > I would like to ask if there is a plan to include it as an official
> > procedural language. If so, when?
>
> None that I have heard of. Even if the decision where to made today it
> would likely a couple of years before it would be included, so that is
> not going to help you. What is your concern?
>
> >
> > Thanks,
> > *—*
> > *Zé Rui Marques*
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Re: PL/java

[Adrian Klaver]

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 Klaver > wrote:


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 that
all of
 > our code within the database to be also in java.
 >
 > The PL/java “add-on” is not supported directly by Postgres.
 >
 > I would like to ask if there is a plan to include it as an official
 > procedural language. If so, when?

None that I have heard of. Even if the decision where to made today it
would likely a couple of years before it would be included, so that is
not going to help you. What is your concern?

 >
 > Thanks,
 > *—*
 > *Zé Rui Marques*
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Tomas Vondra]

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 message_type_id in ( 59, 62, 102, 162 )
>   and sent_on > NOW() - INTERVAL '3 days';
> 
> I created extended statistics (all types) on client_id and site_id,
> analyzed the columns, and in pg_stats_ext.dependencies (4 = client_id, 7 =
> site_id) have:
> {"4 => 7": 0.002997, "7 => 4": 0.896230}
> 
> When I check row estimates like below, I get results that indicate the
> columns are expected to be independent still.
> explain SELECT id FROM messages; --889594304
> explain SELECT id FROM messages where client_id = 1234; --133439
> explain SELECT id FROM messages where site_id = 889977; --28800
> explain SELECT id FROM messages where client_id = 1234 and site_id =
> 889977; --4
> 

I was a bit confused about this initially, because why would it still be
estimated as independent clauses, with the extended stats defined? But I
think the explanation is fairly simple - the combination of values is
simply rare enough not to be included in the MCV list, so it probably
gets estimated using the "regular" logic as if independent. But we still
mark the clauses as estimated, so the functional dependencies are not
really considered.

(I wonder if the code in PG14 would work better, but that's of little
use of course.)

> However, I pick a client & site ID pair which show up in the MCVs list,
> then I get the same estimate when querying for that site_id with or without
> including the client_id. That is great.
> 

I think this is consistent with the above explanation - in this case the
MCV actually kicks in, significantly improving the estimate.

> Is it reasonable to expect that if the correlation between two columns is
> rather high, then the optimizer might figure the columns are not
> independent and perhaps would give less weight to the value derived from
> independent column statistics? With table statistics, it is possible to set
> a static value or ratio for something like ndistinct. Any chance for
> something similar on dependency someday?
> 
> Perhaps I am expecting too much or have a poor understanding of what
> extended statistics can or someday might do. I deal with under estimates
> from correlations between client_id and sites or other similar
> dependent objects a fair bit and am hopeful to resolve some of those
> planning problems with extended stats, without the maintenance overhead of
> migrating everything to a single client per database to get more specific
> statistics.
> 

What you might try is defining the statistics with only the functional
dependencies. That should consider the column-level correlation even
when the combination of values is not in the MCV. It might make the
"good" estimate worse, but that's an inherent trade-off.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

simple reporting tools for postgres in aws

[Chris Stephens]

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 to oracle application
express's interactive reports (i come from an oracle background).

i took a look at what's available from amazon's aws marketplace but quickly
got overwhelmed. every application i saw was overkill.

users will not be tech savvy. we have a tentative fallback plan to use
excel with pages linked to views in the database but i'm looking for
something a little more targeted.

does anyone have any suggestions that fit the description above?

thanks for any input,
chris

Re: PL/java

[Rob Sargent]

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 should be.

Re: PL/java

[Adrian Klaver]

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 strictly sql to db, data to app.  As it should be.



Playing Devil's advocate. It is still Java interacting with a database. 
The objection I gather is to having Java embedded in the database. SQL 
interaction is no panacea:


https://portswigger.net/daily-swig/sql-injection



--
Adrian Klaver
adrian.kla...@aklaver.com

PostgreSQL Database Upgrade

[github kran]

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 Analyze successfully completed.?

Command used:
*ANALYZE VERBOSE;*


Appreciate your help, thank you !!

Re: PostgreSQL Database Upgrade

[Adrian Klaver]

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? Does it
show any time when the last Analyze successfully completed.? 


See:

https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW

last_analyze 	timestamp with time zone 	Last time at which this table 
was manually analyzed




Command used:
*ANALYZE VERBOSE;*
*
*
*
*
Appreciate your help, thank you !!*
*




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: simple reporting tools for postgres in aws

[Tony Shelver]

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 some of the
species you are looking for, but have never used it.

Postgres lists a variety of data reporting products :
https://www.postgresql.org/download/products/5-reporting-tools/



On Mon, 7 Dec 2020 at 17:53, Chris Stephens  wrote:

> 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 to oracle application
> express's interactive reports (i come from an oracle background).
>
> i took a look at what's available from amazon's aws marketplace but
> quickly got overwhelmed. every application i saw was overkill.
>
> users will not be tech savvy. we have a tentative fallback plan to use
> excel with pages linked to views in the database but i'm looking for
> something a little more targeted.
>
> does anyone have any suggestions that fit the description above?
>
> thanks for any input,
> chris
>

Deferrable FK not behaving as expected.

[Ron]

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 fails.


Here are sample tables, sample data and the failed delete statement.

What am I missing?

test=# \d sales_header
    Partitioned table "public.sales_header"
    Column |    Type | Collation | Nullable | Default
---+-+---+--+-
 order_num | integer | |  |
 cust_id   | integer |   | not null |
 order_ts  | timestamp without time zone |   | not null |
 shipping_addr | text    | |  |
Partition key: RANGE (order_ts)
Indexes:
    "sales_header_pkey" PRIMARY KEY, btree (cust_id, order_ts)
    "sales_header_i1" btree (order_num)
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*


test=# \d sales_detail
   Partitioned table "public.sales_detail"
    Column    |    Type | Collation | Nullable | Default
--+-+---+--+-
 cust_id  | integer |   | not null |
 order_ts | timestamp without time zone |   | not null |
 seq_no   | integer |   | not null |
 inventory_id | integer | |  |
 quantity | numeric(10,2)   | |  |
 price    | numeric(10,2)   | |  |
 tax_rate | numeric(3,3)    | |  |
Partition key: RANGE (order_ts)
Indexes:
    "sales_detail_pkey" PRIMARY KEY, btree (cust_id, order_ts, seq_no)
Foreign-key constraints:
    "fk_sales_detail_sales_header" FOREIGN KEY (cust_id, order_ts)
   REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE*
Number of partitions: 12 (Use \d+ to list them.)

INSERT INTO sales_header VALUES (1, 1, '2020-01-05 13:05:42.567', '123 Main 
St, Anytown, Iowa');
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 1, 12345, 
5.8, 28.40, 0.092);
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 2, 23456, 
6.0, 98.40, 0.092);
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 3, 34567, 
1.8, 67.00, 0.092);
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 4, 45678, 
450,  2.00, 0.092);


INSERT INTO sales_header VALUES (2, 1, '2020-02-05 13:05:42.567', '234 Main 
St, Anytown, Iowa');
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 1, 6575, 5.2, 
567, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 2, 4565, 456, 
545, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 3, 7899, 768, 
432, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 4, 2354, 556, 
890, 0.045);


INSERT INTO sales_header VALUES (3, 1, '2020-03-05 13:05:42.567', '345 Main 
St, Anytown, Iowa');
INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 1, 6575, 5.2, 
567, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 2, 4565, 456, 
545, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 3, 7899, 768, 
432, 0.045);


test=# begin transaction;
BEGIN
test=# delete from sales_header where cust_id = 1;
ERROR:  update or delete on table "sales_header_202001" violates foreign key 
constraint "sales_detail_cust_id_order_ts_fkey" on table "sales_detail"
DETAIL:  Key (cust_id, order_ts)=(1, 2020-01-05 13:05:42.567) is still 
referenced from table "sales_detail".

test=#
test=# rollback;



--
Angular momentum makes the world go 'round.

Re: Deferrable FK not behaving as expected.

[Thomas Kellerer]

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 initially 
immediate",
so you will need to make them deferrable in your transaction:

SET CONSTRAINTS ALL DEFERRED;

or create the constraint with

deferrable initially deferred

Thomas

Re: Deferrable FK not behaving as expected.

[Ron]

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 "deferrable" this is the same as "deferrable 
initially immediate",

so you will need to make them deferrable in your transaction:

    SET CONSTRAINTS ALL DEFERRED;

or create the constraint with

    deferrable initially deferred


OK.  For some reason, I thought INITIALLY DEFERRED was only applicable to 
INSERT and UPDATE statements.


--
Angular momentum makes the world go 'round.

Re: Deferrable FK not behaving as expected.

[Ron]

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 "deferrable" this is the same as "deferrable 
initially immediate",

so you will need to make them deferrable in your transaction:

    SET CONSTRAINTS ALL DEFERRED;

or create the constraint with

    deferrable initially deferred


Neither technique worked.

test=# ALTER TABLE sales_detail ALTER CONSTRAINT 
fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE

test=# \d sales_detail
   Partitioned table "public.sales_detail"
    Column    |    Type | Collation | Nullable | Default
--+-+---+--+-
 cust_id  | integer |   | not null |
 order_ts | timestamp without time zone |   | not null |
 seq_no   | integer |   | not null |
 inventory_id | integer | |  |
 quantity | numeric(10,2)   | |  |
 price    | numeric(10,2)   | |  |
 tax_rate | numeric(3,3)    | |  |
Partition key: RANGE (order_ts)
Indexes:
    "sales_detail_pkey" PRIMARY KEY, btree (cust_id, order_ts, seq_no)
Foreign-key constraints:
    "fk_sales_detail_sales_header" FOREIGN KEY (cust_id, order_ts)
  REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE INITIALLY 
DEFERRED*

Number of partitions: 12 (Use \d+ to list them.)

test=# begin transaction;
BEGIN
test=# delete from sales_header where cust_id = 1;
ERROR:  update or delete on table "sales_header_202001" violates foreign key 
constraint "sales_detail_cust_id_order_ts_fkey" on table "sales_detail"
DETAIL:  Key (cust_id, order_ts)=(1, 2020-01-05 13:05:42.567) is still 
referenced from table "sales_detail".

test=# rollback;
ROLLBACK



--
Angular momentum makes the world go 'round.

Re: PL/java

[Joshua Drake]

>
>
>
> 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 good examples.

In short, don't worry about whether or not it is "official".

JD


> I would like to ask if there is a plan to include it as an official
> procedural language. If so, when?
>
> Thanks,
> *—*
> *Zé Rui Marques*
>
>
>

Re: Deferrable FK not behaving as expected.

[Tom Lane]

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 successfully, but it actually hasn't.  If you
drop the FK constraint altogether, and recreate it having
DEFERRABLE INITIALLY DEFERRED from the start, it should
work --- at least it does for me.

regards, tom lane

Re: simple reporting tools for postgres in aws

[legrand legrand]

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/12cChart.html

Regards
PAscal





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Deferrable FK not behaving as expected.

[Ron]

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 file a bug report?


  it looks like it's updated all the
child FKs successfully, but it actually hasn't.  If you
drop the FK constraint altogether, and recreate it having
DEFERRABLE INITIALLY DEFERRED from the start, it should
work --- at least it does for me.


That worked.

--
Angular momentum makes the world go 'round.

Re: Deferrable FK not behaving as expected.

[Adrian Klaver]

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 CONSTRAINT ---


Do I need to file a bug report?


No:

https://www.postgresql.org/message-id/3144850.1607369633%40sss.pgh.pa.us



  it looks like it's updated all the
child FKs successfully, but it actually hasn't.  If you
drop the FK constraint altogether, and recreate it having
DEFERRABLE INITIALLY DEFERRED from the start, it should
work --- at least it does for me.


That worked.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Deferrable FK not behaving as expected.

[Ron]

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's updated all the
child FKs successfully, but it actually hasn't.  If you
drop the FK constraint altogether, and recreate it having
DEFERRABLE INITIALLY DEFERRED from the start, it should
work --- at least it does for me.


That works *sometimes*, but not when we *really* want it to work.

test=# alter table sales_detail drop CONSTRAINT fk_sales_detail_sales_header;
ALTER TABLE

test=# ALTER TABLE sales_detail
test-# ADD CONSTRAINT fk_sales_detail_sales_header
test-# FOREIGN KEY (cust_id, order_ts)
test-# REFERENCES sales_header (cust_id, order_ts) DEFERRABLE INITIALLY 
DEFERRED;

ALTER TABLE
test=#
test=#

-- Works

test=# begin transaction;
BEGIN
test=# delete from sales_header where cust_id = 1;
DELETE 3
test=#
test=# rollback;
ROLLBACK

-- Does not 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_order_ts_fkey"
DETAIL:  Key (cust_id, order_ts)=(1, 2020-01-05 13:05:42.567) is still 
referenced from table "sales_detail".

test=# rollback;
ROLLBACK


--
Angular momentum makes the world go 'round.

Re: Deferrable FK not behaving as expected.

[Tom Lane]

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_order_ts_fkey"

That I think you're gonna have to live with.  DETACH PARTITION isn't
something we can defer to end of transaction.

regards, tom lane

Re: PL/java

[Martin Gainty]

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 implement PL/SQL that rob/joshua mentioned
if you're calling no-joy after implementing PL/SQL
then we can take a look at interfacing to Postgres ETL binaries with a JNI 
interface
provided the JNI is spot on with signatures for each referenced function

Un Saludo
m


From: Rob Sargent 
Sent: Monday, December 7, 2020 11:16 AM
To: pgsql-general@lists.postgresql.org 
Subject: 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 should be.

Re: PL/java

[Rob Sargent]

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 disparaging either language

Going Forward:
try to implement PL/SQL that rob/joshua mentioned
if you're calling no-joy after implementing PL/SQL
then we can take a look at interfacing to Postgres ETL binaries with a 
JNI interface

provided the JNI is spot on with signatures for each referenced function

If I'm tracking correctly I can say the java implementation of 
CopyManager is, to me, blindingly fast.  So if the E and the T are in 
java then certainly the L can be also.  I have not compared it to 
straight file-base copy because my data is conceived in Java and slammed 
home directly from the app (middle-ware). I write relatively large 
blocks (millions of records) to naked temporary tables and then let 
straight sql read from there and insert into final tables in chunks.

Un Saludo
m


*From:* Rob Sargent 
*Sent:* Monday, December 7, 2020 11:16 AM
*To:* pgsql-general@lists.postgresql.org 


*Subject:* 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 should be.

Re: Deferrable FK not behaving as expected.

[Ron]

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
constraint "sales_detail_cust_id_order_ts_fkey"

That I think you're gonna have to live with.  DETACH PARTITION isn't
something we can defer to end of transaction.


Very sad, since it would simplify the archiving of the 24x365 system we're 
porting from Oracle to RDS Postgresql.


--
Angular momentum makes the world go 'round.

Extracting data from jsonb array?

[Ken Tanzer]

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_exchange_id=1296;


?column?



---
--
 [
{"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name":
"Possession Of Burglary Tools", "OffenseCodes": "9A52030;9A52060",
"ClassSeverity": {"Code": "M|GM", "Description": null}},
{"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name": "Burglary
In The Second Degree (Commercial)", "OffenseCodes": "9A52030",
"ClassSeverity": {"Code": "F|B", "Description": null}}
]

How can I extract the two "Name" elements?  (i.e.:

Possession of Burglary Tools
Burglary In The Second Degree (Commercial)

This is with 9.6.20.  Thanks in advance!

Ken






-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[Steve Baldwin]

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 of carriage
> returns for readability):
>
> SELECT _message_body->'Charges' FROM message_import_court_case where
> _message_exchange_id=1296;
>
>
>   ?column?
>
>
>
>
> ---
>
> --
>  [
> {"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name":
> "Possession Of Burglary Tools", "OffenseCodes": "9A52030;9A52060",
> "ClassSeverity": {"Code": "M|GM", "Description": null}},
> {"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name": "Burglary
> In The Second Degree (Commercial)", "OffenseCodes": "9A52030",
> "ClassSeverity": {"Code": "F|B", "Description": null}}
> ]
>
> How can I extract the two "Name" elements?  (i.e.:
>
> Possession of Burglary Tools
> Burglary In The Second Degree (Commercial)
>
> This is with 9.6.20.  Thanks in advance!
>
> Ken
>
>
>
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: Extracting data from jsonb array?

[Ken Tanzer]

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 terminology might not be correct!

Cheers,
Ken


>>
>>
>>
>>
>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ *
>> *https://demo.agency-software.org/client
>> *
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[David G. Johnston]

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 where ->>'Name' will then work.

For v12 and newer readers, SQL/JSON Path should probably be used instead.

David J.

Re: Extracting data from jsonb array?

[Ken Tanzer]

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_elements" to navigate past the array and
> get to the next layer of the json where ->>'Name' will then work.
>
>
Thank you David.  I had tried that function without much luck.  But with
your inspiration, I made progress and got to this:

select
_message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name'
FROM message_import_court_case WHERE _message_exchange_id = 1296;
 _message_exchange_id |  ?column?
--+
 1296 | Possession Of Burglary Tools
 1296 | Burglary In The Second Degree (Commercial)
(2 rows)

But what I really want is one line per message, with the charges in an
array.  I can't seem to find the right syntax to make this work:

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR:  column "message_import_court_case._message_exchange_id" must appear
in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
   ^

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR:  set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR:  set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296;
ERROR:  column "message_import_court_case._message_exchange_id" must appear
in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...

Thanks!

Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[David G. Johnston]

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..."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 where ->>'Name' will then work.
>>
>>
> Thank you David.  I had tried that function without much luck.  But with
> your inspiration, I made progress and got to this:
>
> select
> _message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name'
> FROM message_import_court_case WHERE _message_exchange_id = 1296;
>  _message_exchange_id |  ?column?
> --+
>  1296 | Possession Of Burglary Tools
>  1296 | Burglary In The Second Degree (Commercial)
> (2 rows)
>
> But what I really want is one line per message, with the charges in an
> array.  I can't seem to find the right syntax to make this work:
>
> => select
> _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
> FROM message_import_court_case WHERE _message_exchange_id = 1296;
> ERROR:  column "message_import_court_case._message_exchange_id" must
> appear in the GROUP BY clause or be used in an aggregate function
> LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
>^
>
> => select
> _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
> FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
> ERROR:  set-valued function called in context that cannot accept a set
>
> => select _message_exchange_id,(SELECT
> array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
> message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
> ERROR:  set-valued function called in context that cannot accept a set
>
> => select _message_exchange_id,(SELECT
> array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
> message_import_court_case WHERE _message_exchange_id = 1296;
> ERROR:  column "message_import_court_case._message_exchange_id" must
> appear in the GROUP BY clause or be used in an aggregate function
> LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...
>
>
>
Sub-queries are a simple solution to get around the "set-valued function"
restriction.

The more direct way is to place the set-valued function in the FROM clause
where it wants to be, by using LATERAL (the keyword itself can be implied
when dealing with functions)

select array_agg(e->>'key') from (values
('[{"key":"val"},{"key":"val2"}]'::jsonb)) val (v), jsonb_array_elements(v)
jae (e)

David J.

Re: Extracting data from jsonb array?

[Ken Tanzer]

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:

 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 where ->>'Name' will then work.
>>>
>>>
>> Thank you David.  I had tried that function without much luck.  But with
>> your inspiration, I made progress and got to this:
>>
>> select
>> _message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name'
>> FROM message_import_court_case WHERE _message_exchange_id = 1296;
>>  _message_exchange_id |  ?column?
>> --+
>>  1296 | Possession Of Burglary Tools
>>  1296 | Burglary In The Second Degree (Commercial)
>> (2 rows)
>>
>> But what I really want is one line per message, with the charges in an
>> array.  I can't seem to find the right syntax to make this work:
>>
>> => select
>> _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
>> FROM message_import_court_case WHERE _message_exchange_id = 1296;
>> ERROR:  column "message_import_court_case._message_exchange_id" must
>> appear in the GROUP BY clause or be used in an aggregate function
>> LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
>>^
>>
>> => select
>> _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
>> FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
>> ERROR:  set-valued function called in context that cannot accept a set
>>
>> => select _message_exchange_id,(SELECT
>> array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
>> message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
>> ERROR:  set-valued function called in context that cannot accept a set
>>
>> => select _message_exchange_id,(SELECT
>> array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
>> message_import_court_case WHERE _message_exchange_id = 1296;
>> ERROR:  column "message_import_court_case._message_exchange_id" must
>> appear in the GROUP BY clause or be used in an aggregate function
>> LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...
>>
>>
>>
> Sub-queries are a simple solution to get around the "set-valued function"
> restriction.
>
> The more direct way is to place the set-valued function in the FROM clause
> where it wants to be, by using LATERAL (the keyword itself can be implied
> when dealing with functions)
>
> select array_agg(e->>'key') from (values
> ('[{"key":"val"},{"key":"val2"}]'::jsonb)) val (v), jsonb_array_elements(v)
> jae (e)
>
> David J.
>
>
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 foo (id,js) VALUES (2,
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

Can anyone help me with a working query (preferably with the least
cumbersome syntax possible!) that would return these values (the key2
values) as array text elements:

id Agg_val
  
1 {r1k2val,r1k2val2}
2 {r2k2val,r2k2val}

(2 rows)

Thank you!

Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[Rob Sargent]

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 foo (id,js) VALUES (2,
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

Can anyone help me with a working query (preferably with the least 
cumbersome syntax possible!) that would return these values (the key2 
values) as array text elements:


id     Agg_val
  
1     {r1k2val,r1k2val2}
2     {r2k2val,r2k2val}

(2 rows)



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 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?

Cumbersome is in the eyes of the beholder ;)

Re: Extracting data from jsonb array?

[David G. Johnston]

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 | {"\"r2kval\"","\"r2kval2\""}
> (2 rows)
>
> I think the quotes are a fault of example data?
>
The quotes are the fault of the query author choosing the "->" operator
instead of "->>".

David J.

Re: Extracting data from jsonb array?

[Rob Sargent]

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_agg
+--
  1 | {"\"r1kval\"","\"r1kval2\""}
  2 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?

The quotes are the fault of the query author choosing the "->" 
operator instead of "->>".


David J.

With that correction OP might have an answer?

Re: Extracting data from jsonb array?

[Ken Tanzer]

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;
>>  id |  array_agg
>> +--
>>   1 | {"\"r1kval\"","\"r1kval2\""}
>>   2 | {"\"r2kval\"","\"r2kval2\""}
>> (2 rows)
>>
>> I think the quotes are a fault of example data?
>>
> The quotes are the fault of the query author choosing the "->" operator
> instead of "->>".
>
> David J.
>
> With that correction OP might have an answer?
>

Thank you Rob!  I would say yes, except I fear I over-simplified my
example.  What if there are other fields in the table, and I want to treat
this array_agg as just another field?  So here's the query you had (with
the ->> change):

=> select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->>'key') as fa from foo) g group by id;
 id |array_agg
+--
  1 | {r1kval,r1kval2}
  2 | {r2kval,r2kval2}
(2 rows)

And here's the table/data with two other fields added, f1 & f2:

CREATE TEMP TABLE foo (
  id INTEGER PRIMARY KEY,
  f1  TEXT,
  f2  TEXT,
  js  JSONB
);
INSERT INTO foo (id,f1,f2,js) VALUES (1,'My Text 1','My Text 1a',
'[
{"key":"r1kval","key2":"r1k2val"},
{"key":"r1kval2","key2":"r1k2val2"}
]');
INSERT INTO foo (id,f1,f2,js) VALUES (2,'My Text 2','My Text 2a',
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

If I want all 4 of my fields, all I can think to do is join your query back
to the table.  Something like this:

=> SELECT id,f1,f2,array_agg AS vals FROM foo LEFT JOIN (select id,
array_agg(fa) from (select id, (jsonb_array_elements(js)->>'key') as fa
from foo) g group by id) foo2 USING (id);

 id |f1 | f2 |   vals
+---++--
  1 | My Text 1 | My Text 1a | {r1kval,r1kval2}
  2 | My Text 2 | My Text 2a | {r2kval,r2kval2}
(2 rows)

That seems to work, but is there any other way to streamline or simplify
that?

Cumbersome is in the eyes of the beholder ;)


Maybe.  There's probably an aesthetic component, but also an aspect that
can be quantified, likely in character counts. :)

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 to me, as it seems to appear to others.

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[David G. Johnston]

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 to me, as it seems to appear to others.
>

Upgrade to v12+ for access to simpler/cleaner.  Composing various unnesting
and key extraction operations works but, yes, it gets ugly proportional to
the extent you need to dig into complex json structures.  That said lateral
joining reduces nesting which is measurably cleaner.

David J.

Re: Extracting data from jsonb array?

[Steve Baldwin]

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 
wrote:

> 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 to me, as it seems to appear to others.
>>
>
> Upgrade to v12+ for access to simpler/cleaner.  Composing various
> unnesting and key extraction operations works but, yes, it gets ugly
> proportional to the extent you need to dig into complex json structures.
> That said lateral joining reduces nesting which is measurably cleaner.
>
> David J.
>
>

Re: Extracting data from jsonb array?

[Ken Tanzer]

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}
> (2 rows)
>

Oh I like that, and thanks!  It seems a little clearer to me, but maybe
that's because records still seem more familiar than json.  Applying the
quantitative cumbersome-syntax test, this clocks in 8 characters shorter
than the other one (99 vs. 107).  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 | array_agg
+---++
  2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
  1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
(2 rows)

That clocks in at 109 characters, compared to 178 for the similar query we
previously had:

SELECT id,f1,f2,array_agg AS vals FROM foo LEFT JOIN (select id,
array_agg(fa) from (select id, (jsonb_array_elements(js)->>'key') as fa
from foo) g group by id) foo2 USING (id);


Upgrade to v12+ for access to simpler/cleaner.


I can't upgrade just yet, but that is something to look forward to.  Out of
curiosity, what would an equivalent query look like in V12?

Cheers,
Ken




-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[Ken Tanzer]

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 | array_agg
> +---++
>   2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
>   1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
> (2 rows)
>

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 and f2 fields.  (It's not giving the "must appear in the GROUP BY
clause or be used in an aggregate function" error that I would expect, and
that I am getting when I try to apply this to my real query.)

Can anyone explain to me why those fields don't need to be grouped?  Thanks.

Ken



> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[Steve Baldwin]

What am I missing?

b2bcreditonline=# select * from foo;
 id |   js
  |f1 | f2
++---+
  1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key": "r1kval2", "key2":
"r1k2val2"}] | My text 1 | My text 1a
  2 | [{"key": "r2kval", "key2": "r2k2val"}, {"key": "r2kval2", "key2":
"r2k2val2"}] | My text 2 | My text 2a
(2 rows)

b2bcreditonline=# 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;
ERROR:  column "f.f1" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select f.id, f.f1, f.f2, array_agg(t.key2) from foo as f, js...
 ^

On Tue, Dec 8, 2020 at 2:57 PM Ken Tanzer  wrote:

>
>
> 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 | array_agg
>> +---++
>>   2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
>>   1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
>> (2 rows)
>>
>
> 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 and f2 fields.  (It's not giving the "must appear in the GROUP BY
> clause or be used in an aggregate function" error that I would expect, and
> that I am getting when I try to apply this to my real query.)
>
> Can anyone explain to me why those fields don't need to be grouped?
> Thanks.
>
> Ken
>
>
>
>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ *
>> *https://demo.agency-software.org/client
>> *
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: Extracting data from jsonb array?

[Tom Lane]

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 and f2 fields.  (It's not giving the "must appear in the GROUP BY
> clause or be used in an aggregate function" error that I would expect, and
> that I am getting when I try to apply this to my real query.)

> Can anyone explain to me why those fields don't need to be grouped?  Thanks.

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 foo.id isn't a primary key, then I'm confused too.  Can we see the
full declaration of the table?

regards, tom lane

Re: Extracting data from jsonb array?

[Steve Baldwin]

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,r1k2val2}
  2 | My text 2 | My text 2a | {r2k2val,r2k2val2}
(2 rows)


On Tue, Dec 8, 2020 at 3:09 PM Steve Baldwin 
wrote:

> What am I missing?
>
> b2bcreditonline=# select * from foo;
>  id |   js
>   |f1 | f2
>
> ++---+
>   1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key": "r1kval2", "key2":
> "r1k2val2"}] | My text 1 | My text 1a
>   2 | [{"key": "r2kval", "key2": "r2k2val"}, {"key": "r2kval2", "key2":
> "r2k2val2"}] | My text 2 | My text 2a
> (2 rows)
>
> b2bcreditonline=# 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;
> ERROR:  column "f.f1" must appear in the GROUP BY clause or be used in an
> aggregate function
> LINE 1: select f.id, f.f1, f.f2, array_agg(t.key2) from foo as f, js...
>  ^
>
> On Tue, Dec 8, 2020 at 2:57 PM Ken Tanzer  wrote:
>
>>
>>
>> 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 | array_agg
>>> +---++
>>>   2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
>>>   1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
>>> (2 rows)
>>>
>>
>> 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 and f2 fields.  (It's not giving the "must appear in the GROUP BY
>> clause or be used in an aggregate function" error that I would expect, and
>> that I am getting when I try to apply this to my real query.)
>>
>> Can anyone explain to me why those fields don't need to be grouped?
>> Thanks.
>>
>> Ken
>>
>>
>>
>>> --
>>> AGENCY Software
>>> A Free Software data system
>>> By and for non-profits
>>> *http://agency-software.org/ *
>>> *https://demo.agency-software.org/client
>>> *
>>> ken.tan...@agency-software.org
>>> (253) 245-3801
>>>
>>> Subscribe to the mailing list
>>>  to
>>> learn more about AGENCY or
>>> follow the discussion.
>>>
>>
>>
>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ *
>> *https://demo.agency-software.org/client
>> *
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>

Re: Extracting data from jsonb array?

[Ken Tanzer]

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, I'm not clear why Postgres isn't complaining about
> > the f1 and f2 fields.  (It's not giving the "must appear in the GROUP BY
> > clause or be used in an aggregate function" error that I would expect,
> and
> > that I am getting when I try to apply this to my real query.)
>
> > Can anyone explain to me why those fields don't need to be grouped?
> Thanks.
>
>
> If foo.id isn't a primary key, then I'm confused too.  Can we see the
> full declaration of the table?
>
>
So I created some confusion because the original version of the table in my
example did _not_ declare a primary key.  A later example, and the one I
used, did have the primary key:

CREATE TEMP TABLE foo (
  id INTEGER PRIMARY KEY,
  f1  TEXT,
  f2  TEXT,
  js  JSONB
);



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 do that without the Primary Key, it does indeed complain about f1 & f2
not being grouped.  But what is the "It" in "it lets you get away with
that" referring to?  Or more specifically, is this some specialized case
because of something related to use of the jsonb_recordset function?  I've
gotten so used to having to group on every non-aggregate field that I
didn't realize there could be any exception to that.

Thanks!


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[Tom Lane]

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 do that without the Primary Key, it does indeed complain about f1 & f2
> not being grouped.  But what is the "It" in "it lets you get away with
> that" referring to?

Sorry I was vague there, it's the parse analysis phase that understands
that "GROUP BY a primary key" should be treated as allowing any column of
that pkey's table to be referenced without also explicitly grouping by
that other column.  If you then join to some other table, the free pass
doesn't extend to the other table.

> Or more specifically, is this some specialized case
> because of something related to use of the jsonb_recordset function?

Nope, unrelated to that.

> I've
> gotten so used to having to group on every non-aggregate field that I
> didn't realize there could be any exception to that.

We did not use to have this bit of logic, so maybe your habits were
formed a few years ago.  But as I said, I think the SQL spec says
this should be OK.  Definitely, there are other DBMSes that also
allow it.

regards, tom lane

Re: Extracting data from jsonb array?

[Steve Baldwin]

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 SELECT list expressions to refer to ungrouped columns
except within aggregate functions or when the ungrouped column is
functionally dependent on the grouped columns, since there would otherwise
be more than one possible value to return for an ungrouped column. A
functional dependency exists if the grouped columns (or a subset thereof)
are the primary key of the table containing the ungrouped column."

If you come from an Oracle background (as I do), this behaviour may
surprise you, since Oracle definitely doesn't allow this.

I much prefer Postgres. 😁

Steve

On Tue, Dec 8, 2020 at 3:32 PM Ken Tanzer  wrote:

>
>
> 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, I'm not clear why Postgres isn't complaining about
>> > the f1 and f2 fields.  (It's not giving the "must appear in the GROUP BY
>> > clause or be used in an aggregate function" error that I would expect,
>> and
>> > that I am getting when I try to apply this to my real query.)
>>
>> > Can anyone explain to me why those fields don't need to be grouped?
>> Thanks.
>>
>>
>> If foo.id isn't a primary key, then I'm confused too.  Can we see the
>> full declaration of the table?
>>
>>
> So I created some confusion because the original version of the table in
> my example did _not_ declare a primary key.  A later example, and the one I
> used, did have the primary key:
>
> CREATE TEMP TABLE foo (
>   id INTEGER PRIMARY KEY,
>   f1  TEXT,
>   f2  TEXT,
>   js  JSONB
> );
>
>
>
> 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 do that without the Primary Key, it does indeed complain about f1 &
> f2 not being grouped.  But what is the "It" in "it lets you get away with
> that" referring to?  Or more specifically, is this some specialized case
> because of something related to use of the jsonb_recordset function?  I've
> gotten so used to having to group on every non-aggregate field that I
> didn't realize there could be any exception to that.
>
> Thanks!
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: Extracting data from jsonb array?

[Ken Tanzer]

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 required by spec, but am too lazy to go check right now.
>
> > If I do that without the Primary Key, it does indeed complain about f1 &
> f2
> > not being grouped.  But what is the "It" in "it lets you get away with
> > that" referring to?
>
> Sorry I was vague there, it's the parse analysis phase that understands
> that "GROUP BY a primary key" should be treated as allowing any column of
> that pkey's table to be referenced without also explicitly grouping by
> that other column.  If you then join to some other table, the free pass
> doesn't extend to the other table.
>


Thanks! That makes sense to me as an explanation, and is good to know.

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 (t).  But that
doesn't seem right, because rows from t are only joining with matching rows
from f, rather than all of them.  Is there some unspoken implicit logic
going on here, or something else entirely that is going over my head?

Thanks everybody for the help and patience!

Ken


> --
>
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Extracting data from jsonb array?

[David G. Johnston]

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 created by jsonb_to_recordset (t).  But that
> doesn't seem right, because rows from t are only joining with matching rows
> from f, rather than all of them.  Is there some unspoken implicit logic
> going on here, or something else entirely that is going over my head?
>

That is the lateral join.

 https://www.postgresql.org/docs/current/sql-select.html

Read the section under from, join, lateral.

David J.

Re: Extracting data from jsonb array?

[Tom Lane]

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 (t).  But that
> doesn't seem right, because rows from t are only joining with matching rows
> from f, rather than all of them.  Is there some unspoken implicit logic
> going on here, or something else entirely that is going over my head?

There's an implicit LATERAL there:

  ... from foo as f, lateral jsonb_to_recordset(js) as t(key2 text)

ie, for each row of foo, extract the foo.js column and evaluate
jsonb_to_recordset(js) --- which, in this case, produces multiple
rows that are joined to the original foo row.  This is again a
SQL-ism.  I don't particularly care for their choice to allow
LATERAL to be implicit for function-call-like FROM items,
because it seems pretty confusing; but the spec is the spec.

[ thinks for a bit... ]  Again, I'm too lazy to go digging in
the spec's dense verbiage at this hour, but I'm vaguely recalling
that they may only require this behavior for the one case of
the function being UNNEST().  I think it was our choice to allow
it to work like that for any set-returning function.

regards, tom lane

Re: Extracting data from jsonb array?

[Ken Tanzer]

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 (f)
> > cross-joined to a table created by jsonb_to_recordset (t).  But that
> > doesn't seem right, because rows from t are only joining with matching
> rows
> > from f, rather than all of them.  Is there some unspoken implicit logic
> > going on here, or something else entirely that is going over my head?
>
> There's an implicit LATERAL there:
>
>   ... from foo as f, lateral jsonb_to_recordset(js) as t(key2 text)
>
> ie, for each row of foo, extract the foo.js column and evaluate
> jsonb_to_recordset(js) --- which, in this case, produces multiple
> rows that are joined to the original foo row.  This is again a
> SQL-ism.  I don't particularly care for their choice to allow
> LATERAL to be implicit for function-call-like FROM items,
> because it seems pretty confusing; but the spec is the spec.
>
>
That's (finally!) making sense to me.



> [ thinks for a bit... ]  Again, I'm too lazy to go digging in
> the spec's dense verbiage at this hour, but I'm vaguely recalling
> that they may only require this behavior for the one case of
> the function being UNNEST().  I think it was our choice to allow
> it to work like that for any set-returning function.
>
>
The SELECT page David pointed me towards has a little section that seems to
confirm your recollection:
Function Calls in FROM

PostgreSQL allows a function call to be written directly as a member of the
FROM list. In the SQL standard it would be necessary to wrap such a
function call in a sub-SELECT; that is, the syntax FROM func(...) alias is
approximately equivalent to FROM LATERAL (SELECT func(...)) alias. *Note
that LATERAL is considered to be implicit; this is because the standard
requires LATERAL semantics for an UNNEST() item
in FROM. PostgreSQL treats UNNEST() the same as other set-returning
functions.*


Cheers,

Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.