[veggieroll]

Loving the continued push for JSON features. I'm going to get a lot of use out of JSON_TABLE. And json_scalar & json_serialize are going to be helpful at times too. JSON_QUERY with OMIT QUOTES is awesome too for some things.

I hope SQLite3 can implement SQL/JSON soon too. I have a library of compatability functions to generate the appropriate JSON operations depending on if it's SQLite3 or PostgreSQL. And it'd be nice to reduce the number of incompatibilities over time.

But, there's a ton of stuff in the release notes that jumped out at me too:

"COPY .. ON_ERROR" ignore is going to be nice for loading data anywhere that you don't care if you get all of it. Like a dev environment or for just exploring something. [1]

Improvements to CTE plans are always welcome. [2]

"transaction_timeout" is an amazing addition to the existing "statement_timeout" as someone who has to keep an eye on less experienced people running SQL for analytics / intelligence. [3]

There's a function to get the timestamp out of a UUID easily now, too: uuid_extract_timestamp(). This previously required a user defined function. So it's another streamlining thing that's nice. [4]

I'll use the new "--exclude-extension" option for pg_dump, too. I just got bitten by that when moving a database. [5]

"Allow unaccent character translation rules to contain whitespace and quotes". Wow. I needed this! [6]

[1] https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...

[2] https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...

[3] https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...

[4] https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...

[5] https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...

[6] https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...

[nbbaier]

> I hope SQLite3 can implement SQL/JSON soon too. I have a library of compatability functions to generate the appropriate JSON operations depending on if it's SQLite3 or PostgreSQL. And it'd be nice to reduce the number of incompatibilities over time.

Is this available anywhere? Super interested

[veggieroll]

No, it's not at the moment. Sorry!

The most useful part is doing set intersection operations on JSON array's. Probably the second is extracting a value by path across both.

It's not crazy to implement, SQLite was the harder side. Just a bit of fiddling with `json_each`, EXISTS, and aggregate functions.

[nbbaier]

Might give it a whirl. Is the library pure sql or is it written in something else?

[veggieroll]

I've been using Go with Goqu [1] for SQL for a lot of things lately. But, the language shouldn't matter much. The functions are just building the appropriate SQL clauses.

For some of the operations, the method I was using required marshaling the inputs to JSON before sending them over the wire. And that's nicer in a non SQL programming language. But both db's ultimately do have json_build_array/json_build_object for PostgreSQL or json_array/json_object for SQLite3.

[1] https://github.com/doug-martin/goqu

[pbronez]

Yeah JSON_TABLE looks pretty cool. Here's the details:

https://www.postgresql.org/docs/17/functions-json.html#FUNCT...