The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
19 views

Matching list of hashes against table hashes [on hold]

I have about ~300 tables of ~2000 rows each and each row consists of a unique hash along with some other attributes. I need to scan each table and their rows against a separate list of ~2000 hashes. ...
3
votes
1answer
43 views

Use specific array column in the where clause

Having a function that returns an array and in a query we use the function to return results such as: {"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"} {"TUNDUMA RETURN ...
1
vote
1answer
28 views

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count ...
0
votes
0answers
7 views

Use array operators inside postgresql function

I'm trying to create a postgresql. But I keep having an error ERROR: operator does not exist: integer[] <@ integer Hint: No operator matches the given name and argument type(s). You might ...
1
vote
1answer
20 views

Postgres query to return JSON object keys as array

Is it possible to return a JSON object keys as an array of values in PostgreSQL? In JavaScript, this would simply be Object.keys(obj), which returns an array of strings. For example, if I have a ...
1
vote
2answers
43 views

Postgresql: Sortable index on array values

Suppose the following table: CREATE TABLE foo (id serial, category int, bar int[]); INSERT INTO foo VALUES (1, 1, '{4,10,20}'); INSERT INTO foo VALUES (2, 1, '{1,8,9}'); We want to query sorted ...
2
votes
1answer
51 views

Foreign key for array column

I have a table: CREATE TABLE methods ( method_id serial PRIMARY KEY, method_name varchar(100) ); I want now to create a table with the following columns: CREATE TABLE ...
3
votes
2answers
43 views

Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do: I have a table, fields: id SERIAL inet INET ports integer[] id | inet | ports ----+------------+------------ 2 | 1.2.2.1 ...
1
vote
1answer
56 views

PostgreSql : flatten json array data

From my current query, I obtain this jsonb data : values: "a1", ["b1", "b2"] And I want to flatten it on one level only, like this : values: "a1", "b1", "b2" Here is a simplfied way to get ...
4
votes
1answer
41 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...
3
votes
1answer
39 views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
4
votes
1answer
81 views

Order result by count of common array elements

Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection. Something like: ...
3
votes
1answer
60 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 ...
0
votes
0answers
13 views

MongoDB Single Array or Multiple Fields

I have a MongoDB for a game, and I made a collection to store all outgoing attacks. My question is which of these models is more efficient. spearMenCount: type: Number required: true ...
2
votes
1answer
71 views

How does unnest() treat NULL values and empty arrays?

I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
5
votes
1answer
197 views

Efficient merging (removing duplicates) of arrays

I have two tables, left2 and right2. Both tables will be large (1-10M rows). CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER); ALTER TABLE left2 ADD PRIMARY KEY (id,t1); CREATE TABLE right2( t1 ...
2
votes
2answers
117 views

Postgresql: Pattern match against array elements?

Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array ...
3
votes
1answer
61 views

Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

I have been playing with arrays in one of my PostgreSQL databases. I have created a table with a geometry array with at least one element: CREATE TABLE test_arrays ( polygons ...
1
vote
1answer
39 views

Aggregate rows of type array

I need to aggregate rows with a single column of type INT[] into a single array of type INT[]. The following results in an error "could not find array type for data type integer[]": create temp ...
0
votes
1answer
48 views

How can I update an hstore[] field?

I have table products, in which there is an hstore array field countries_of_origin. So a product that is assembled from multiple products might have multiple countries of origin: [ { "country" ...
0
votes
1answer
37 views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
1
vote
1answer
253 views

MongoDB update object in Array

I have the following document: { "name" : "student_1", "courses" : [ { "name" : "Algebra", "grades" : [ 98, 96, 92 ] }, { "name" : ...
1
vote
1answer
50 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
311 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 ...
4
votes
1answer
227 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
45 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. ...
3
votes
2answers
38 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
192 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
924 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
92 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 - ...
2
votes
1answer
68 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
27 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
102 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, ...
2
votes
1answer
143 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
173 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
61 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
348 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 ...
2
votes
1answer
519 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
143 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
87 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
92 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
1k 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 ...
4
votes
4answers
146 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
417 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 ( ...
2
votes
2answers
3k 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
279 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
173 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
1answer
312 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
199 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
224 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) ...