Given a column containing the above data, I use select jsonb_path_query_array(column, ‘$.key’) to get the output [KA, KB] However this doesn’t work in Postgres-11. Are there any alternatives for the same ? Answer Yes. This yields a Postgres array. Use jsonb_agg instead of array_agg if you need a JSON array. Update
Tag: postgresql-11
Select Distinct (case insensitive) on Postgres
This has been asked here and a few other places before but seems like the suggested answers either don’t apply to postgres or don’t work in this situation. I’m looking to select distinct column names, eg: SELECT DISTINCT column_name FROM table_name WHERE … ORDER BY column_name however I’m looking to eliminate case sensitive duplicates (eg A and a should be
How to sort numbers last in PostgreSQL?
Found many related answers, but nothing that did this. How to sort by numbers last: And, preferably (but not necessary) PostgreSQL 11.9 Also, probably don’t want to use a regex for performance reasons. Don’t want to create an index either… Answer I think a regex is the right way to do this: This puts first rows that contain no digit
PostgreSQL 11 – using format to assign to variable
I have been awake for well beyond my schedule and I have been stuck with this issue for a long time, I don’t even know what I am looking for to solve, but I wish to use format to insert values that I’ll be using for column names, and then executing it… but it keeps giving me errors no matter
PostgreSQL11 xpath query not working properly
When I execute below query in Postgres 10.12, it works properly. Output: But When I execute same query in Postgres 11.7, it is not working. What is the solution to fix this issue? Answer This is caused by this change: Correctly handle relative path expressions in xmltable(), xpath(), and other XML-handling functions (Markus Winand) Per the SQL standard, relative paths
Is it possible to create an ENUM of smallints in postgres 11.x?
I’d like to create an enum TYPE to constrain a pl/pgsql function to accept an argument that’s 1, 2, or 3. Enum types seemed like the way to go about this. I’ve tried creating my enum as below: Answer According to the PostgreSQL documentation, an ENUM type is always going to be a fixed value: An enum value occupies four
PostgreSQL json_build_object nested
First things first: I’m using PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit. đŸ™‚ Im trying to create a JSON object directly from the database. My desired result is { “1”: [], “…
Postgresql select column with minimum value of hierarchical parents
Having a table like this Table “public.access_level” Column | Type | Modifiers | Storage | Stats target | Description ———–+———+———–+———-+——…
Replace with single quote to double single quote not working in PostgreSQL 12
Replace with single quote to double single quote not working properly in PostgreSQL 12, it was working fine in PostgreSQL 11. PostgreSQL 12 Query: SELECT REPLACE(patient.note,””,”””), * FROM patient Output Text: Medicare Secondary Veteran�s Administration PostgreSQL 11 Query: SELECT REPLACE(patient.note,””,”””), * FROM patient Output Text: Medicare Secondary Veteran’s Administration let me know if you have any solutions. Answer This has
Select data from three tables only if one of them has reference to parent table
I have three tables person, his cars, his houses. And i need select persons with his cars and houses if one of childs(car, house) table have reference to parent(person). I tried with join, but don’t know how use OR in this condition. and result should be Answer You can left join twice and ensure that one of the joins succeded.