The tag has no usage guidance.

learn more… | top users | synonyms

1
vote
1answer
19 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
2
votes
1answer
38 views

How to get an array as output parameter from stored procedure?

This is my stored procedure: CREATE PROCEDURE `getAttritionData`( IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT ) DETERMINISTIC ...
3
votes
1answer
53 views

Step through multiple arrays in parallel with FOREACH

Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like: for(int i = 0 ; i < array1.length ; i++){ ...
1
vote
0answers
14 views

Schema for ACL/User/Group/Role management

I'm building an app with some fairly complex permission and ACL rules and want to get my schema right. These are my goals: A user signs up on behalf on an organization. Organization has groups. ...
2
votes
2answers
23 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
9
votes
2answers
113 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
2
votes
2answers
114 views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc ...
1
vote
0answers
31 views

How to deal with large amounts of data - PHP [closed]

I'm currently making a postage website using php, laravel and phpmyadmin. I have a list on postcodes for different areas and each area will have a different price. E.g. Area 1 - England, Area 2 - ...
1
vote
1answer
39 views

Using a GIN index on an integer array to model sets of tags

In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table? I am trying to design a simple data model with a ...
1
vote
0answers
26 views

ANY in postgresql gives weird error

when I do: SELECT ARRAY[4] I get array integer[] ------ {4} and if I do: select * from b where b.ID = ANY (array [4]) That works. Now, something else: select array_agg(4) I get: ...
1
vote
1answer
44 views

Query single column, all rows in Postgres 2D Array

Is there any way to fix the query below so I don't have to specify an arbitrary max row count? DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, ...
1
vote
1answer
64 views

Query for sub-array in multi-dimentional array

How do I fix the following sql query: DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, array_column integer[][] ); insert into array_test (name, ...
0
votes
1answer
80 views

LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:

We have a dilemma… We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder. We have all of our TRN and BAK files on ...
0
votes
1answer
29 views

How to update array fields in a table in Postgresql?

Say I have a table defined as following: CREATE TABLE my_tbl ( id bigint, a1 bigint[], a2 bigint[] ); I would like to create a stored procedure to append values to a1 and a2. Is this ...
0
votes
1answer
130 views

Put Postgres query result in array

How can I put an sql query result in a string array with Postgres? I have some attributes in the column 'zone' of the table 'mytable' and I must put them all in a String[] areas; This is my first ...
0
votes
1answer
126 views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
4
votes
1answer
130 views

Combine multiple queries into 1 query

I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages: \d messages Table ...
2
votes
1answer
56 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
69 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
444 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
74 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
272 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
981 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
156 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
116 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
75 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
202 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
146 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
160 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
55 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
151 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 ...
3
votes
1answer
784 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
110 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
164 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
4k 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
713 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
2answers
1k 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
625 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 ...
9
votes
1answer
3k 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
1k 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
400 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
2k 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 ...
7
votes
1answer
1k 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
150 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
164 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
536 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
226 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
68 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 ...