The array tag has no wiki summary.
1
vote
1answer
22 views
capability and performance of tags implemented as array vs text vs full text
In terms of search capability and performance, what are the pros & cons of implementing tags as an array or text or full text field?
I am seeing limitations of capability in choosing to implement ...
0
votes
1answer
32 views
Easy way to replace an YAML array of IDs with strings from a joined table
I have a database full of camp data. Each camp has many campers and has many camp_weeks.
In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project ...
3
votes
1answer
283 views
How to remove known elements from a JSON[] array in PostgreSQL?
I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
0
votes
1answer
81 views
Assign result of regexp_split_to_array() to Pl/pgSQL array variable
How do I create an array and then put a result of a function into it?
I'm doing it like this:
array text[];
select regexp_split_to_array('whatever this is it splits into array with spaces', E'\\s+')
...
1
vote
1answer
118 views
Unnest multiple arrays into rows
I have been shown a great way to bulkinsert with this example:
WITH p AS (
INSERT INTO parent_table (column_1)
SELECT $1
RETURNING id)
INSERT INTO child_table (parent_table_id, ...
1
vote
2answers
117 views
How to do WHERE x IN (val1, val2,…) in plpgsql
I would like to update a selection of rows in a table; this works...
UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5);
But how can I do the same in a plpgsql function? The following gives a ...
3
votes
1answer
301 views
Foreign key constraint on array member?
Suppose I have a table containing job roles:
CREATE TABLE roles
(
"role" character varying(80) NOT NULL,
CONSTRAINT "role" PRIMARY KEY (role)
);
Suppose I further have a table, users, and each ...
2
votes
1answer
117 views
mongodb distinct() in an array
In my mongodb database,there is only one document below:
{
"_id" : ObjectId("5226efc5ccf23796f9590f67"),
"count" : NumberLong(126045),
"eissn" : "1466-5034",
"grade" : 0.0,
...
1
vote
0answers
79 views
Fastest way to get the length of the intersection of two text arrays in PostgreSQL
This is what I am currently using:
CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS
$$
select array ( select unnest($1) intersect select unnest($2) order by 1 ...
3
votes
1answer
553 views
PostgreSQL list of integers separated by comma or integer array for performance?
As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a ...
4
votes
1answer
168 views
PostgreSQL PREPARE query with IN () parameters
I'm trying to prepare a query from PHP like:
pg_prepare($con, "prep", "select * from test where tid in ($1)");
and then execute it with:
$strpar = "3,4,6,8,10";
pg_execute($con, "prep", ...
1
vote
0answers
105 views
Is there are way to use Fine Grain Auditing to record Varray bind values?
I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are ...
0
votes
2answers
98 views
Cycle or something similar in PL/SQL
Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via
alter user %username% account unlock
and ...
1
vote
0answers
217 views
storing arrays or images in a database table
I'm a beginner with both database design and use of forums such as this one so forgive me if I forget to provide some relevant information below.
Basically I'm trying to store a set of arrays, in ...
0
votes
1answer
96 views
Dealing with data stored as arrays in a MySQL DB
So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
4
votes
1answer
701 views
Transpose two-dimensional array from n rows to 2 columns
Background
Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available.
Problem
Looking to simplify the code that pivots a two-dimensional array.
Code
A working, overly-verbose example ...
1
vote
1answer
855 views
Pass a table array as a parameter to an upsert function in postgresql
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
0
votes
0answers
47 views
Checking of one specific element of one array is in another array?
Suppose I have two arrays. One looking like (3, 9, 10, 39), and one looking like (4, 10, 9, 48, 391, 2).
How would I (through pure MySQL) check if one of the elements in the first array is inside the ...
2
votes
1answer
742 views
Selecting with varbinary(max) criteria (in the where clause)
Basic info
Database: SQL Server Express 2008 R2
Client: SQL Server Management Studio
Backround (skip if not interested):
A project I'm maintaining uses an ORM, which apparently stored my enum ...
5
votes
3answers
1k views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
1
vote
2answers
584 views
ERROR: could not find array type for datatype information_schema.sql_identifier
I am trying to run the below sql command:
SELECT ARRAY(
SELECT column_name
FROM information_schema.columns
WHERE table_name ='gis_field_configuration_stage'
);
and I get the below ...
4
votes
1answer
220 views
Postgresql - Internal Array handling - array type or pointer type
I have a question, about the internal handling of array columns in Postgres.
Does the engine handle it as a classical array with memory alloc or is it somehow a pointer list.
It would be interesting ...
9
votes
1answer
1k views
How to preserve the original order of elements in an unnested array?
Given the string:
'I think that PostgreSQL is nifty'
I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
5
votes
1answer
117 views
Retrieve Indices of Associative Array as Collection
In PL/SQL, suppose I have some associative array defined as follows:
declare
type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
a_arr a_arr_t;
I then, sparsely, populate the ...
