All Questions
Tagged with postgresql arrays
2,383
questions
-1
votes
1
answer
32
views
PostgreSQL - Get duplicate values of an Array
I have a table in which one the columns is an ARRAY of smallint.
ID | Array
--------------------
1 | [1,2,3]
2 | [4,5,6]
3 | [7,8,9]
What I'm looking for is... a function where every time I INSERT ...
- 105
0
votes
1
answer
22
views
SQL Error [42883]: ERROR: function unnest(jsonb) does not exist
I am using postgresql and I am trying to separate an array column with single values into different columns. The original column called fee_rule_id is like [98, 120, 133]. I want it to be column1 98, ...
- 1
1
vote
2
answers
41
views
How can I, having two arrays, remove elements of the second array to the first array - PostgreSQL?
Let's say I have a table t, 2 of its columns are A1 and A2. It looks something like this:
A1
A2
ID
1,3,6,9,11
3,9
1
2,3,5,7
3
2
...
I want to add a column with the result:
A3
1,6,11
2,5,7
...
...
- 13
0
votes
3
answers
41
views
How do I search within a PostgreSQL jsonb array?
I have a jsonb column that looks like this:
{
"customer_id": "1",
"address": "123 Main St.",
"orders": [{
"order_id": &...
1
vote
0
answers
54
views
What are downsides of using composite type and array of composite type columns in TimescaleDB hypertable
I'm considering to use a composite type and array of composite type columns in TimescaleDB hypertable. I'm curious whether using such columns has any downsides like less effective compression ...
- 869
0
votes
2
answers
53
views
Convert String array to integer array in Postgres
I have a string array that represents the days of the week, but I want to map it to an integer array.
I have created a function to do it, but it always returns null, I am sure I am missing something ...
- 5
0
votes
0
answers
24
views
How to compare arrays in Postgres [duplicate]
I am fairly new to Postgres, but so far I have been impressed with its array handling, but I have come across a problem that I am struggling to solve in any way that would be considered good.
I have a ...
- 5
2
votes
2
answers
65
views
How to generate an array of given size, with one field populated?
I would like to create a function that given a value of any type, a dimension of the one-dimensional array and the column to set, will return a one-dimensional array filled with null values except for ...
- 2,548
0
votes
1
answer
150
views
Insert JSON object into JSON array column
I am working on a small project to learn Supabase and NextJS. I am trying to add a JSON object to my sites array which is defined as a JSON array. I want to add the config (and more in the future) to ...
0
votes
0
answers
16
views
Is there any possible way to define context variable as array in postgresql
Is there any possible way to define context variable by "set local ..." or set_config in postgresql?
something like this
set local "test_var" = array[3,7];
or by
select set_config(...
- 1
1
vote
1
answer
228
views
How to pass array parameters to a Postgres query?
I am very new to PostgreSQL and struggling to work with arrays.
I have a table bookIds like this:
book_id
location_id
checked_out_version
12
1
10
15
1
12
From Java code, I am passing arrays of ...
- 11
1
vote
1
answer
26
views
PostgreSQL 12.17 Extract element from JSON Array
Good morning
PostgreSQL version 12.17
I have a column called "design" which contains JSON array data.
column
The JSON stored within this column is formatted as follows:
{
"...
- 13
1
vote
2
answers
59
views
Validate array of table names
I have requirement where bash script will pass on the string consisting tables names to anonymous PL/pgSQL block which will process the tables if they exists in the Postgres db.
Below is the PL/pgSQL ...
- 37
0
votes
0
answers
49
views
C extension for postgres using arrays
Hello i am trying to create an extension which would take an array as input and add 5 to it and then return the output it need not change the original array
#include "postgres.h"
#...
- 1
1
vote
1
answer
35
views
Merging two arrays in PostgreSQL in different ways
Based on a previous question in stackoverflow, I have created two similar functions. One as SQL and a very similar one in PLPGSQL:
CREATE OR REPLACE FUNCTION mergeArrays (a1 ANYARRAY, a2 ANYARRAY)
...
- 11