Tagged Questions
2
votes
2answers
32 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 | ...
0
votes
0answers
19 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
1answer
42 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
23 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
31 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 ...
13
votes
4answers
7k views
Postgres NOT in array
I'm using Postgres's native array type, and trying to find the records where the ID is not in the array recipient IDs.
I can find where they are IN:
SELECT COUNT(*) FROM "messages" WHERE (3 = ANY ...
2
votes
3answers
3k views
PHP array to postgres array
Now php can't work directly wit Postgresql array. For example, php taking postgresql array like
'{"foo","bar"}'
I need simple php function to create multidimensional postgresql array from php ...
0
votes
1answer
46 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
27 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
24 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
29 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, ...
1
vote
2answers
42 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 ...
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
0answers
51 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
39 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
2answers
578 views
Rails 4 Postgresql array data-type: updating values
I am just starting to use the array data-type in Postgres with Rails 4 but I am having trouble getting values on an existing array to update. I have a column called 'quantity' that is an array of ...
2
votes
3answers
25 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
19 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
40 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 ...
0
votes
1answer
120 views
New array aggregate function
I have a table like this:
id | array
1 | {8,8,8,x,u,x,x}
2 | {8,8,8,x,8,x,x}
...|...
n | {8,u,u,x,u,x,x}
It contains time of work each employee (cols in array are days of week, u and x are ...
0
votes
2answers
66 views
Easily generate SQL script for an Array of objects - Rails
I made a Rake Task to parse information from some websites (using Nokogiri gem) and save everything to a .json file. To do this, I store all objects inside and array and before the rake task ends, I ...
2
votes
1answer
150 views
NULL emements lost when casting result of unnest()
I stumbled upon very odd behavior with unnest(), when casting after expanding an array.
Introduction
There are three basic syntax variants to use unnest():
1) SELECT unnest('{1,NULL,4}'::int[]) AS ...
0
votes
2answers
35 views
How to declare returning type for function, returning unnamed table
I want to create a function, that returns a table with 2 columns:
i integer -- or bigint?
arr integer[] -- array of integer
What should I write instead of ??? in this function:
CREATE OR ...
8
votes
4answers
5k views
PostgreSQL unnest() with element number
When I have a column with separated values I can use unnest() function:
myTable
id | elements
---+------------
1 |ab,cd,efg,hi
2 |jk,lm,no,pq
3 |rstuv,wxyz
select
id,
...
1
vote
1answer
42 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
67 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
38 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
506 views
Pass multidimensional array as parameter to Postgresql function
I'm trying to maintain a Php application with a PostgreSQL database. At one point, a stored procedure is called, lets say function_x and inside function_x, function_y is called; function_y is passed a ...
1
vote
1answer
182 views
Select rows such that names match elements of input array for pgsql function
I would like to create a PostgreSQL function that does something like the following:
CREATE FUNCTION avg_purchases( IN last_names text[] DEFAULT '{}' )
RETURNS TABLE(last_name text[], ...
1
vote
1answer
27 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,"
...
7
votes
5answers
6k views
Getting the last element of a Postgres array, declaratively
How to obtain the last element of the array in Postgres?
I need to do it declaratively as I want to use it as a ORDER BY criteria. I wouldn't want to create a special PGSQL function for it, the less ...
3
votes
1answer
3k views
Search in integer array in Postgres
Is there any other way to search for a certain value in an integer[] column in Postgres?
My currently installed Postgres version does not allow the following statement:
SELECT * FROM table WHERE ...
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
30 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 ...
2
votes
2answers
2k views
How to use a array loop in pgSQL
I want to do something like this:
CREATE OR REPLACE FUNCTION ff(int, text) RETRUNS integer AS $$
DECLARE
r text;
BEGIN
FOR r IN SELECT string_to_array($2, ',')
LOOP
INSERT INTO ...
0
votes
1answer
29 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
57 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
50 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
31 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
21 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 ...
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 ...
5
votes
2answers
9k views
A PostgreSQL query with 'ANY' is not working
SELECT "Ticket_id" FROM "Tickets"
WHERE "Status" = 1 AND ("Ticket_id" != ANY(array[1,2,3])) Limit 6
And the result is 1,2,3,4,5,6
2
votes
0answers
97 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
2answers
64 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 ...
2
votes
1answer
92 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 ...
0
votes
1answer
49 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. ...
0
votes
1answer
45 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 ...
6
votes
3answers
248 views
Postgresql JDBC Table Valued Parameters
MSSQL has a great feature called Table Valued Parameters. It allows you to pass a table of a custom data to stored procedures and functions.
I was wondering what is the equivalent in PostgreSQL, if ...
0
votes
1answer
93 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).
...
1
vote
1answer
52 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 ...
