Tag: postgresql-12

Rails & Postgres selecting sub keys of a JSONB column

I have a column of a database table that is of type JSONB and I’m wanting to get some data from that column. For the most part the column is a flat list of key value pairs. Ex: However, the key I’m after contains an array of JSON data (or can be null/nil): Now, what I want I do

SQL query Postgres 12

postgresql-12 sql

I’m doing an inner join on a table like this: The condition f.vac_1 = mv.id might not been satisfied in the case where a person have not been vaccinated. In such case, I don’t want to ignore the row, but instead of displaying the vaccine name (which is the purpose of the inner join) to display an …

PostgreSQL create index on JSONB[]

postgresql postgresql-12 sql

Consider a table defined as follows: How can I create an index on json key type and how can I query on it? Edit: Previously, there were no indexes on json keys and select queries used an unnest operation as shown below: The problem is, if the table has a large number of rows, the above query will not only

How can i find all linked rows by array values in postgres?

graph-theory postgresql postgresql-12 recursive-query sql

i have a table like this: I want to find out which rows are in a group together. In this example 1,2,4 are one group because 1 and 2 have a common element and 2 and 4. 3 and 5 form a group because they have a common element. 6 Has no common elments with anybody else. So it forms

Postgresql – Select in Select Alias Problem

arrays postgresql postgresql-12 sql subquery

I have query. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d SELECT users.id as user_ids, (SELECT ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses….

PostgreSQL – Why 1 search paramater works, but not the other? (multiple joined tables)

postgresql-12 sql

So I have a query that I run that basically looks like the one below (simplified it though for easier viewing). When I search on user_orders.orderID it works flawlessly, but if I try to search by …

Add column data to a table based on geometry intersection with another table – PostgreSQL

postgis postgresql postgresql-12 sql

my city has defined some Urban Revitalization Perimeters using PostGIS I’ve separated the city Blocks within Perimeters and the Buildings within Perimeters. What I want to do now is to update the buildings table with two new columns, that relates them with the blocks they are on. The following is a samp…

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…

Get room members, room’s owner and admin at the same time in one query with grouped by id (unique) on PostgreSQL 12

join postgresql postgresql-12 sql

I want to get room’s member list, room’s owner member in case of he doesn’t exists in other table and admin member at the same time. Currently i fetch them individually. CREATE TABLE public….