The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
0answers
34 views

UPDATE & DELETE multiple rows with an array for the id

I wan't to check for challenges a user's team posted that have not been excepted after 1 day, then autorefund the user's team back there credits, and then also delete all the challenges. So far here ...
0
votes
1answer
34 views

Update an array element with inc mongo update

HI All I have this Data in mongo, {"articleId" : [ { "articleId" : "9514666", "articleCount" : 1 } ], "count" : NumberLong(1), ...
0
votes
0answers
21 views

aggregate array records in mongo db

HI All I have this Data in mongo, {"articleId" : [ { "articleId" : "9514666", "articleCount" : 1 } ], "count" : NumberLong(1), ...
0
votes
1answer
32 views

Array column vs another table when the relationship is quite small

The Data I am creating a table of cities and their zip codes for the United States. Some zip codes have a list of acceptable cities, as designated by the USPS. For example, the zip code 36613 is ...
2
votes
0answers
68 views

Postgres and numeric json arrays

I have a table defined as: create table dummy (jdata jsonb); I inserted the following two rows: insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ...
2
votes
1answer
85 views

PostgreSQL return second 'Group' as an array

I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this... websites ------- website_id (pk) ...
2
votes
0answers
44 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
1
vote
2answers
53 views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
0
votes
0answers
52 views

Matching array values in PostgreSQL with ActiveRecord

In my Rails 4 app I have a goal to see all contacts, where field visible_to in contacts table equal to 1. My visible_to is :integer, array: true. However, I get the following exception: ...
3
votes
1answer
306 views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
1
vote
1answer
54 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
75 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
2k 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
394 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
326 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, ...
2
votes
2answers
357 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 ...
5
votes
1answer
1k 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
599 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, ...
2
votes
0answers
218 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
1k 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 ...
6
votes
1answer
464 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
128 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
127 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
354 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
2answers
139 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
1k 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
1k 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
56 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
1k 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 ...
6
votes
3answers
2k 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
840 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
248 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
2k 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
137 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 ...