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 emtpy string. How

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 sample of the Blocks table, the two columns qd_setor

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: SELECT REPLACE(patient.note,””,”””), * FROM patient Output Text: Medicare Secondary Veteran’s Administration let me know if you have any solutions. Answer This has

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