All Questions
41 questions
1
vote
1
answer
64
views
GIN index not being used with NOT?
I have a table with a column itemsIds containing an array of ids. I have created a gin index for that column. I'm trying to find rows that don't contain an element with the following query:
select * ...
0
votes
0
answers
55
views
Postgresql + array type + PDO + GIN index how to make them work?
I have this (working) query:
SELECT * from "DB".pages WHERE topic @> '{football}';
Notice that there is an array type column (topic) indexed with GIN.
I have to implement this in PhP ...
- 255
0
votes
1
answer
787
views
Most efficient way to index an array column to allow for selecting rows containing a value
Say I have a table like this:
create table mytable (
mycol text[]
)
And I want to select all rows where mycol contains "hello". I can think of two ways to do this:
SELECT * FROM mytable ...
- 1,860
0
votes
0
answers
297
views
Create Postgres JSONB Index on Array Sub-Object for ILIKE operator
I have a table that has cast as jsonb column, which looks like this:
cast: [
{ name: 'Clark Gable', role: 'Rhett Butler' },
{ name: 'Vivien Leigh', role: 'Scarlett' },
]
I'm trying to query name ...
0
votes
1
answer
228
views
postgresql to php array with column as a index
not sure if this is possible, but I need to return a json object from a postgresql query
say the query returns something like this
who count
=================
mary 2
had 9
a ...
3
votes
1
answer
4k
views
How to use Postgresql GIN index with ARRAY keyword
I'd like to create GIN index on a scalar text column using an ARRAY[] expression like so:
CREATE TABLE mytab (
scalar_column TEXT
)
CREATE INDEX idx_gin ON mytab USING GIN(ARRAY[scalar_column]);
...
- 473
0
votes
1
answer
242
views
How to improve or index postgresql's jsonb array field?
I usually use jsonb field store array data.
for example, I want to store customer's barcode info, I will create a table like this:
create table customers(fcustomerid bigint, fcodes jsonb);
One ...
- 53
1
vote
2
answers
449
views
PostgreSQL: Optimal way to store and index Unique Array field
I have user table with id as GUID and I want to create group table for users.
Constraint: group table row should be unique for a set of users?
Meaning if I want to create a new group previously I need ...
- 2,844
0
votes
1
answer
601
views
Postgres JSONB Query and Index on Nested String Array
I have some troubles wrapping my head around how to formulate queries and provide proper indices for the following situation. I have customer entities represented in JSON like this (only relevant ...
- 338
0
votes
1
answer
185
views
Postgres index creation for jsonb column having JSON Array value
I have an employee table in postgres having a JSON column "mobile" in it. It stores JSON Array value ,
e_id(integer) name(char) mobile(jsonb)
1 John [{\"mobile\": \"1234567891\...
- 211
0
votes
1
answer
391
views
Indexing only an attribute on a json array - Postgres
I have a table with a jsonb field named "data" with the following content:
{
customerId: 1,
something: "..."
list: [{ nestedId: 1, attribute: "a" }, { nestedId: 2, attribute: "b" }]
}
I need to ...
- 309
0
votes
2
answers
1k
views
Postgres: Searching over jsonb array field is slow
We are changing DB(PostgreSQL 10.11) structure for one of our projects. And one of the changes is moving field of type uuid[] (called “areasoflawid”) into the jsonb field (called “data”).
So, we ...
0
votes
0
answers
264
views
Which index type should be used with PostgreSQL's VARCHAR ARRAY? [duplicate]
I have a table containing a one-dimensional array attribute of type VARCHAR.
CREATE TABLE IF NOT EXISTS table_name (
attribute VARCHAR(64) ARRAY DEFAULT NULL
);
I'd like to have this type indexed,...
- 1,241
5
votes
2
answers
6k
views
Indexing a jsonb array in Postgres [duplicate]
I try to set up a GIN index but I do not think my index is used when I run the request, whether I use an operator or a function.
Environment
In our table we have a JSONB field (json_aip) containing ...
- 2,910
0
votes
1
answer
1k
views
Will GIN index be used with sql ANY operator in PostgreSql
I'm wondering if PostgreSql will use GIN index for ANY operator with an integer array. Lets say I have a table tree_nodes which contains id with type int and path with type int[]. Simple example:
Will ...
- 1,185