The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
1answer
27 views

Combine multiple query into 1 query

I have a message table that contains words field, array type, with random words of a message. Now I have an array of words [A, B, C]. I need to find the number of messages that the words contains ...
2
votes
1answer
28 views

Select into specific position with array_agg on postgresql

Is there a way to set value in specific position inside array based on information from other columns (PostgreSQL-9.3 and later)? For example I would like to select an item and its stock information ...
1
vote
2answers
34 views

ElasticSearch: is this problem suitable for ElasticSearch DB? [closed]

I have some hardware instruments each one recording a signal and generating sequences of timestamp/value pairs. Timestamp is a basic UTC posix time and value is a double. I'd like to record such ...
1
vote
1answer
47 views

Delete array element by index

Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.) I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but ...
1
vote
2answers
51 views

Should I normalize my string arrays, or just keep it a simple string?

I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain ...
2
votes
2answers
165 views

Database-agnostic stored procedure call passing array of name/value pairs

Background Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows: CREATE TYPE array_parameters AS ( ...
1
vote
2answers
130 views

How to do an =ANY(SELECT…query in postgresql?

I'm trying to select all the latitudes and longitudes for a group of users based on their id being in an array stored in another table. Here's my attempt: SELECT latitude, longitude FROM userloc ...
0
votes
0answers
75 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
79 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
54 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
68 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
96 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
109 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) ...
3
votes
1answer
48 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
72 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
60 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
519 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
72 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
111 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 ...
4
votes
1answer
3k 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
488 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
564 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
450 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
2k 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
902 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
290 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
664 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
139 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
144 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
420 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
166 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
2k 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
2k 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
60 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
2k 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
998 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
254 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 ...
10
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
148 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 ...