arrays – SQL

Remove Null from For loop of ArrayList

I am currently converting an ArrayList to a String to send it to a DB so I can retrieve it on the other end and convert it back to an ArrayList later. My thought process is to convert it to a string …

PostgreSQL array overlap with the ALL construct

I want to achieve the same behavior as in next example: select array[1, 3] && array[1] and array[1, 3] && array[2] using the ALL construct like so: select array[1, 3] && all(…

SQL – select where for specific id, all distinct values for other column exist

I am having a bit of a hard time explaining the logic I want to achieve in the title. Anyway, I have a database containing two tables (rent and car). The car table contains data about cars (license …

PostgreSQL Integer in Array is not merge-joinable

I would like to perform a FULL OUTER JOIN ON a condition that is an integer being in an array of integer. The query would look something like this: SELECT thing FROM table1 t1 FULL OUTER JOIN table2 …

postgres procedure list of values

I’m trying to pass in a list of values into a Postgres 11 procedure. The query has an IN condition. How do I pass in a list of values to the procedure & then how do I use it in the query? create …

How to use LOWER() on elements of a jsonb column in PostgreSQL?

I have a PostgreSQL table like this one: Table t id | keys (jsonb) —+—————- 1 | [“Key1”, “Key2”] My goal is to query this table to find out if one of the keys of a …

Postgresql – Select in Select Alias Problem

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

postgres – aggregate items in text array

I have an array of text items, each text item in the array is a delimited string, here is a sample: drop table if exists tmp; create table tmp(x text); insert into tmp select (‘1~41.5~50~1|2~43.72~…

Website language translation in PHP (SQL)

PostgreSql : Json Array to Rows using Lateral Join

I have two following JSON Array in details field of my table and need to evaluate the query as I use in another relational table. { “city”: “London”, “name”: “Sainburry”, “quantities”: […