This is my data with _temp_data as ( select unnest(ARRAY['A','B','A','A']) as hobbies_1 ,unnest(ARRAY['E','F','A','F']) as hobbies_2 ) select * from _temp_data Input data: hobbies_1|hobbies_2| ---------+---------+ A |E | B |F | A |A | A |F | I want to transform…
I have a table with a JSONB row which holds data in this form: id data 1 {"key_1": "value1"} 2 {"another_key": "some_value"} Given another JSON structure like this {"key_1": ["value1", "value2"]}, what shall I use to get the first row…
I've been playing around with PostgreSQL's intarray module and I've noticed some seemingly odd behavior with intarrays that have duplicate elements in them. The intarray module defines two subtraction operators with pretty much the same description: integer[] - integer →…
The current time in Shanghai is 2024-03-19 00:43 show timezone +----------+ | TimeZone | |----------| | GMT | +----------+ SELECT CURRENT_DATE AT TIME ZONE 'Asia/Shanghai', CURRENT_TIME AT TIME ZONE 'Asia/Shanghai', CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Shanghai'; +---------------------+-----------------------+---------------------------+ | timezone | timezone…
I have the following SQL Postgres database table: id fid val 401411677 3765 2019-09-06 00:00:00.000 401411677 3782 2019-09-06 00:00:00.000 401413382 3765 2019-12-20 00:00:00.000 401413382 3782 2019-12-23 00:00:00.000 401412111 3765 2019-09-20 00:00:00.000 401412111 3782 2019-09-20 00:00:00.000 In the above, an fid…
We have Postgres DB design that in simplified form looks like this: CREATE TABLE products ( id varchar, version bigint, profile varchar, PRIMARY KEY(id, profile, version) ); CREATE TABLE product_attributes ( productId varchar, key varchar, value varchar, profile varchar, version…
I'm using Flowise 1.6.1 on Docker with a Postgres 16.2 database also on Docker. When I run the Flowise container for the first time it starts but shows these errors regarding already existing PKs when initializing the database. The database…
I have User table[user_id] Disperse Table [order_id, disperse_amount] Pool Table [user_id, order_id] For each order_id in Disperse, I want to insert x random rows of [user.user_id, disperse.order_id] into pool table. (x is the current row's disperse_amount) Might be more understandable…
I have a Postgres table that looks like this: CREATE TABLE products( id string, attributes jsonb, PRIMARY KEY(id) ); An example of attributes field could be like: { "endDate": [ "2024-02-20T21:00:00.000Z" ], "countries": [ "US","IT","ES" ], "type": [ "RETAIL" ],…
I select some dateranges with tsrange that includes a specific year. I recieve an output like this: { time_range: '("2024-12-27 00:00:00","2025-02-01 00:00:00")' }, { time_range: '("2025-05-01 00:00:00","2025-05-05 00:00:00")' }, { time_range: '("2025-05-08 00:00:00","2025-05-20 00:00:00")' } can I count all days…