All Questions
Tagged with array postgresql
265 questions
1
vote
1
answer
85
views
SELECT with array values on WHERE using postgres
I'm using an query to update a object array inside a jsonb column.
Example data:
[
{
"_id": "68696e0a3aab2f9ff9c40679",
"altura": 1,
"comprimento":...
- 145
2
votes
1
answer
117
views
Cardinality of a multirange? How many ranges or gaps are there in a multirange?
How do I obtain the cardinality of a multirange?
I'm using range_agg() to aggregate ranges, which merges ranges if they are continuous, or returns what looks like an array of ranges if the ranges are ...
- 9,465
0
votes
0
answers
80
views
PostgreSQL variadic array of arrays of composite type
I want to write a function that accepts array of arrays of my composite type. because of they have different lengths, it can NOT be defined as one array. So, I want to use variadic to accept arrays ...
0
votes
2
answers
42
views
Is there a way to "rearrange" arrays when selecting?
I have a table post_likes with one column being a post ID (uint), and another column being an array of IDs (uint[]) of users who liked the post.
If I'm selecting info from multiple tables on multiple ...
- 107
0
votes
0
answers
109
views
What are downsides of using composite types and array type columns in TimescaleDB hypertable?
I'm considering to use a composite type, array of standard type and array of composite type columns in TimescaleDB hypertable. I'm curious whether using such columns has any downsides like less ...
- 101
0
votes
0
answers
268
views
An alternative to PostgreSQL union?
In the following example variables in single quotes '' are arguments passed to the PostgreSQL query. If 'tag_args' is an empty array, the first row of result table is a row of three nulls and I use ...
- 133
-1
votes
1
answer
477
views
Replacing substrings in array elements
I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.:
{https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg}
...
0
votes
1
answer
354
views
Performance issues with @> operator in postgres 14
CREATE TABLE public.article (
id uuid NOT NULL,
tags _text NULL,
CONSTRAINT article_pkey PRIMARY KEY (id)
);
I have the requirement to load articles that have a tag which is in a large ...
- 25
0
votes
1
answer
682
views
Convert PostgreSQL array type
Given an enum type (e.g. letter).
What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in:
create ...
- 115
1
vote
2
answers
1k
views
How to update each value in an array with a function?
I want to trim spaces from each value in a text[].
If this was just a text column I could do:
update server set tags = rtrim(ltrim(tags));
However how do I do this for an array? As otherwise that ...
1
vote
0
answers
176
views
PostgreSQL - updating a counter outside a loop function
PostgreSQL 14
Here is a function :
CREATE OR REPLACE FUNCTION test(datedebut integer)
returns text
LANGUAGE plpgsql AS
$$
DECLARE
tab integer[];
periodes text;
i integer;
BEGIN
tab := Array[[0,...
- 205
1
vote
1
answer
637
views
Find integer in array is very slow
PostgreSQL Version PostgreSQL 15.0, compiled by Visual C++ build 1914, 64-bit
This is my "victim" table.
create table if not exists "KeywordsStats" (
id bigserial primary key, &...
- 11
1
vote
2
answers
886
views
PostgreSQL Multicolumn GIN Index with Ordering
I have a large table (~20M records) in a Postgres DB with a "tags" column of ARRAY type, and a "date_inserted" column. I want to be able to query the latest inserted records for a ...
- 11
0
votes
1
answer
240
views
PostgreSQL: Return ENUM values like data_type modifiers
When querying the datatype using format_type(tttypid, atttypmod) from pg_attribute, I would like to return the ENUM values like atttypmod values.
attname | type
--------+-------------------------------...
0
votes
1
answer
1k
views
How do you pass the array of composite type to the argument of an SQL function?
Database example: https://dbfiddle.uk/sERgZPiB
Tables and types
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL,
password text NOT NULL,
email ...
- 288