Tag: postgresql-11

Equivalent of jsonb_path_query_array in Postgresql-11

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 ne…

Select Distinct (case insensitive) on Postgres

postgresql postgresql-11 sql

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 …

How to sort numbers last in PostgreSQL?

postgresql postgresql-11 sql sql-order-by string

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

PostgreSQL 11 – using format to assign to variable

postgresql postgresql-11 sql

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

postgresql postgresql-10 postgresql-11 sql xpath

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 fu…

Is it possible to create an ENUM of smallints in postgres 11.x?

enums postgresql postgresql-11 sql

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

Postgresql select column with minimum value of hierarchical parents

postgresql postgresql-11 sql

Replace with single quote to double single quote not working in PostgreSQL 12

postgresql postgresql-11 postgresql-12 sql

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: SEL…

Select data from three tables only if one of them has reference to parent table

postgresql postgresql-11 sql

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 on…