Skip to content
Advertisement

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 need a JSON array. Update

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

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

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

Advertisement