0
votes
2answers
18 views

appending to multi dimensional arrays in postgres

I am trying to create a multi-column aggregate function that needs to accumulate all the column values for later processing. CREATE OR REPLACE FUNCTION accumulate_scores(prev integer[][],l1 integer, ...
1
vote
1answer
30 views

How to check if an array is empty in Postgres

I have a postgres storage procedure: CREATE OR REPLACE FUNCTION get_stats( _start_date timestamp with time zone, _stop_date timestamp with time zone, id_clients integer[], OUT date ...
0
votes
2answers
18 views

Unnest arrays of different dimensions

Is there a function or query that could return arrays of different dimensions as a set? For example, I would like to return the values ARRAY[1] ARRAY[2,3] ARRAY[4,5,6] as 1 2 3 4 5 6
2
votes
2answers
15 views

Postgres GROUP BY integer arrays

My DB table in Postgres 9.3 | f1|f2 | f3 | 1 | 2 | {1,2,3} | 1 | 3 | {4,5} f1,f2 are integers f3 is integer[]. How can I get this: SELECT f1, myfn(f3) FROM dbTable GROUP BY f1; so that I get: ...
0
votes
1answer
21 views

Rails 4.0.5 - Handling params with arrays containing empty strings

Since I switched to Rails 4.0.5, some undesired behavior began occurring with a form for a model that uses a postgresql string array. def up add_column :cuts, :states, :text, :array => true ...
0
votes
1answer
21 views

syntax for string array migration

It seems postgres arrays are supported knex #226. Is there syntax for a migration beyond raw sql? The schema building docs don't mention it. Presumably the raw sql datatype using the postgres array ...
0
votes
2answers
20 views

Create a function declaring a predefined text array

I need to create a function in Postgres and one of the variables I declare is a predefined text array, but I don't know the syntax to set its values. This is what I have so far: CREATE OR REPLACE ...
0
votes
2answers
49 views

Inserting values into array in PostgreSQL

I am trying to insert values into an integer array, used as path to show all ancestors of a particular node. These values (parent_link integer) are from a table with ID and parent_link. I am trying ...
0
votes
2answers
37 views

How to get columns name from an array for a select query in Postgres

I need query a table getting the columns name from an array... something like this $$ DECLARE column varchar[] := array['column1','column2','column3']; _row record; BEGIN FOR i IN 1 .. ...
0
votes
1answer
38 views

Selecting rows where array elements in Array Rails ActiveRecord/SQL

In Rails 4/Ruby 2.1, I'm trying to do something like this: @user.submissions.where(:submission_codes_array => valid_submission_codes_array).count Sort of similar to when you want to see if the ...
1
vote
0answers
30 views

postgresql json array index

I have data for customers with more than one adresses with json representation like this: { "firstName" : "Max", "lastName" : "Mustermann", "addresses" : [{ "city" : ...
0
votes
2answers
47 views

javascript associative array, looping, and / or scope issue with Highcharts JSON call

I have a javascript associative array issue, looping issue, and / or scope issue. I'm trying to call data from a postgresql database, parse it, and implement it in a Highchart. The data consists ...
1
vote
1answer
26 views

PostgreSQL query for array in any array

I need a PostgreSQL command that matches from a list of emails against an array type column. my column is declared as this: emails character varying(255)[] DEFAULT '{}'::character varying[] And I ...
0
votes
0answers
37 views

Postgre Creating a trajectory linestring with time from timestamped points

I m using pgadmin , postgre sql I have a table of this type object_id(numeric),t(timestamp),pos(geometry point) ,trajectory_id(numeric) The table data looks like this 1 12:00 x,y 1 1 12:01 ...
1
vote
2answers
31 views

postgres + json object to array

i would like to know if is possible to 'covnert' a json object to a json array to iterate over a mixed set of data. i have a two rows that look like {Data:{BASE:{B1:0,color:green}}} ...
2
votes
2answers
54 views

PostgreSQL: joining arrays within group by clause

We have a problem grouping arrays into a single array. We want to join the values from two colums into one single array and aggregate these arrays of multiple rows. Given the following input: | id | ...
1
vote
0answers
29 views

Why won't my serialized PostgreSQL array save to the database?

My schema looks like this: create_table "products", force: true do |t| t.string "elements_list", default: [], array: true end I am using Rails 4 and aiming to use PostgreSQL's array column ...
0
votes
0answers
49 views

Working with JSON arrays in postgresql [duplicate]

I have a JSON variable in my table which has data stored as array in postgresql. test=# \d+ dummy Table "public.dummy" Column | ...
0
votes
1answer
28 views

Storing one to many relation in database - Array or one to many relation?

When we store a one to many association in a database, which is a better approach. One - Many mapping in a table or storing the many part as an array. I'm specific to postgres database (constraint) ...
0
votes
1answer
38 views

Ruby: each_with_index bails when encountering Range as string, NoMethodError

I'm using the new Postgresql feature of storing an array in my Rails 4 app. As you know, each element of an array stored in PG is in strings. So my array = [7, 8..9, 10] is what you want but is ...
0
votes
1answer
75 views

Ruby on Rails: Insert values via form into array field in db

How would you insert values from a form into an array in the database? I'm using simple form, rails 4.1, postgresql db. Here is my form, all fields update correctly except for the impact and ...
0
votes
1answer
31 views

masking and mapping aligned 1D arrays in PostgreSQL

I am searching for support in PostgreSQL to process protein sequences for tasks which seem generic or common enough that I suspect that I am not applying the correct Google query terms or defining the ...
0
votes
1answer
39 views

Rails and Postgres array columns

I have a model with an array column (box_ids). In my view I would like to have a field for each of the values in the array and three extra empty fields to be able to add new values to the array. How ...
0
votes
1answer
35 views

In Rails, how to add an element to an array type attribute for all records?

DB is PostgreSQL. How do you append a value for all records to a column that has an array data type? For example: # migration_file.rb def change add_column :users, :tags, :string, array: true, ...
3
votes
1answer
42 views

Why do these join differently based on size?

In Postgresql, if you unnest two arrays of the same size, they line up each value from one array with one from the other, but if the two arrays are not the same size, it joins each value from one with ...
1
vote
2answers
65 views

Postgres array datatype in waterline being converted to text

I need to create an App. We are using PostgreSQL as the underlying DB. The reason we decided on Postgres was because we wanted a relational database due to various entities and their relationships ...
1
vote
0answers
55 views

postgres: how to save id's within recursion?

I am a newby when it comes to somewhat complexer sql queries so please bear with me. I have a street network. My final goal is update segments of "class" 5 that are touching each other by the class ...
1
vote
2answers
46 views

UNION of two arrays in postgress without unnesting

I have two arrays in postgres that I need to union. for example: {1,2,3} union {1,4,5} would return {1,2,3,4,5} Using the concatenate (||) operator would not remove duplicate entries, EG it returns ...
2
votes
3answers
29 views

Create an empty array in an SQL query using PostgreSQL instead of an array with NULL inside

I am using the following schema: CREATE TABLE person ( person_name VARCHAR PRIMARY KEY ); CREATE TABLE pet ( animal_name VARCHAR, person_name VARCHAR REFERENCES person(person_name), PRIMARY ...
1
vote
1answer
22 views

PostgreSQL: concatenate nested arrays with differing element dimensions

Concatenating the nested arrays {{1,2}} and {{3,4}} is no problem at all: SELECT array_cat( ARRAY[ARRAY[1,2]] , ARRAY[ARRAY[3,4]] ) array_cat --------------- ...
0
votes
1answer
46 views

Add an array column to a resource

I need help going through the steps to add an array column to a resource using the rails g migration command. I have a postgresql database. I need to make an array of strings, and another array of ...
1
vote
1answer
46 views

Multiply elements in an array according to their position

What is an efficient function I could use to increase the amount of elements in an array by their position? For example, if I have an array of: ARRAY[10,20,30,40,50]::INT[] I would like that to ...
0
votes
1answer
110 views

No function matches the given name and argument types you might need to add explicit type casts

The following statement in the code gives me an error: cur.execute(" update %s as A set fm = foo.fm from ( select src_id as id, agg_bit_or(fm) as fm from %s, %s where dst_id = id ...
1
vote
1answer
36 views

JDBC insert real array

I am attempting to insert a real array into a postgresql array: the table definition is: String sqlTable = "CREATE TABLE IF NOT EXISTS ccmBlock" + " sampleId INTEGER," ...
1
vote
1answer
43 views

How to sum two float4 arrays values into a Datum array? (c function in postgres)

My question is about C functions in PostgreSQL, where I'm trying to receive 2 float4[] arrays as parameters and return a float4[] array as the sum of array1 + array2 (columns in tables are set as ...
0
votes
1answer
25 views

How to clone array in PostgreSql?

I have this snippet of code: RAISE NOTICE 'p_new_roster: %', p_new_roster; --prints {3,4,5} FOREACH i IN ARRAY p_new_roster LOOP RAISE NOTICE 'p_new_roster[i]: %', p_new_roster[i]; --prints ...
1
vote
2answers
32 views

How to get current key in foreach in pl/pgsql?

I iterate over an array, and do something with both the array value and its key. Since PostgreSQL 9.1 there is foreach loop, so the array value is no problem, but is there any elegant way to get the ...
0
votes
1answer
34 views

postgresql string_to_array - get the last and one before last elements

I have a long string contained of int,int,float,float * many and I need to get the last two floats. 100,140,14.123,15.123,200,240,16.124,17.123 I'm using string_to_array to get element 3 & 4 ...
0
votes
1answer
58 views

Return an array from a Postgres C function?

This code compiles without error but it does not return anything. Any ideas on what's missing? #include <postgres.h> #include <fmgr.h> #include <utils/array.h> ...
1
vote
3answers
60 views

How to convert numpy array to postgresql list

I am trying to use python to insert 2 columns of a numpy array into a postgresql table as two arrays. postgresql table is DOS: primary_key energy integer[] dos integer[] I have a numpy array that ...
0
votes
1answer
36 views

Postgresql C function that takes a FLOAT8[] and returns an INT[]?

Could someone please provide a template for a C function that would receive FLOAT8[] values and return INT[] values? The examples that I have found use the same input numeric type as the output ...
1
vote
1answer
24 views

Update multiple values from an array with array_replace()

I am trying to do an update of a table in PostgreSQL. In fact, I am trying to update an array. I have an table call switch_ids of OLD_ID, NEW_ID and I have the table TABLE_TO_UPDATE with a column ...
0
votes
2answers
65 views

PHP - Help in FOR LOOP

I have an array and looping through it and pushing the data using for loop. $test_data = [10,20,35,01,50,60,87,12,45,86,9,85]; Actually that's a sample data. But my data is a result similar to that ...
0
votes
1answer
51 views

I want to put my query values ​​in an array in cakephp

well explain, I have a qthe problem is when sending the array values ​​are all numbered and name apararece null. The problem is when sending the array values ​​are all numbered and name display null. ...
1
vote
2answers
38 views

How to unnest an array for supply a sequence of arguments?

PostgreSQL 9.2 works with, SELECT Format('%s %s!', 'Hello', 'world'); But I need something like SELECT Format('%s %s!', array['Hello', 'world'] ); or, more specifically, I need some ...
0
votes
1answer
56 views

Postgresql record to array

I need to convert from an array to rows and back to an array for filtering records. I'm using information_schema._pg_expandarray in a SELECT query to get one row per value in the array. Given the ...
3
votes
0answers
161 views

Why does add_index using 'gin' create a 'btree' index instead?

I am on PostgreSQL 9.3.4 and Rails 4.0.4. I add a "tags" column, and corresponding gin index (or, at least I ask for one). class AddTagsToPhotos < ActiveRecord::Migration def change ...
0
votes
1answer
106 views

what is a better way than arrays overlap? (postgresql)

I am building an app for a business company, and they need to control who sees which reports by project and roles, the report can belong to one project and can be seen by many roles (employees roles). ...
2
votes
1answer
99 views

postgresql two nested joins and arrays in join

I've never had to ask an SO question before, I always eventually stumble upon what I'm looking for through searching, but there's a lot going on here and I've gotten to the end of my searching ...
1
vote
1answer
60 views

PostgreSQL, CIDR - search all ip addresses that are cotained within networks - from cidr array

I have a table that contains two cidr arrays. One contains hosts ip addresses and the second one network addresses. I need to write a function that would execute query like this: SELECT * from ...