Tagged Questions
1
vote
0answers
17 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
32 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
24 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
28 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
22 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
44 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
23 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
26 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
32 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
50 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
28 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
32 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
44 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
53 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
3answers
26 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
20 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
41 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
43 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
76 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
30 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
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
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 ...
0
votes
1answer
30 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
52 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
32 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 ...
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 ...
0
votes
1answer
50 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
46 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 ...
2
votes
0answers
111 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
94 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
93 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
54 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 ...
0
votes
2answers
83 views
Rails 4 Postgres Array attribute: how can I ensure that the attribute is an array during validation?
I just started experimenting with the native Postgres array type in Rails 4, and I'm running into an issue I can't figure out. I want the app to raise an error if the value for an array attribute is ...
6
votes
3answers
277 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 ...
1
vote
2answers
38 views
Formating data array with ruby
I am trying to get data from PostgreSQL with ruby and store them into an array, for that I have a small script:
Script :
require 'pg'
conn=PGconn.connect( :hostaddr=>"192.168.111.136", ...
0
votes
0answers
19 views
Converting single entries into an array with PostgreSQL
I have 3 tables of data. Table A, AC and C. Table AC simply connects A and C together with a ForeignKey. Currently all my rows in AC are single entries that create these connections. So my question ...
0
votes
1answer
310 views
JSON/HSTORE in PostgreSQL Arrays
i am struggling with storing a JSON object into Postgresql Array .. I did something like this --
CREATE TABLE rock_band
(
name text,
members VARCHAR(200) []
);
INSERT INTO rock_band VALUES ...
0
votes
1answer
70 views
PostgreSQL - Create and join array from multiple rows based on ID
I have two tables:
The table LINKS has:
LINK_ID --- integer, unique ID
FROM_NODE_X -- numbers/floats, indicating a geographical position
FROM_NODE_Y --
FROM_NODE_Z --
TO_NODE_X --
TO_NODE_Y --
...
0
votes
1answer
73 views
Rails and PSQL arrays, the difference between {} and []?
I recently converted a column in my rails psql database to a PSQL array.
When trying to query against the column using psql's contains operator (<@) I'm getting an error that says
...
0
votes
1answer
51 views
PostgreSQL Storing Foreign Key in Array in Column
I am creating a a table that holds orders that we receive. ONe column will hold the product ID(s) and the quantity(s) as they could order 3 different items then next order 15 different items. So i ...
1
vote
2answers
53 views
How to determine if NULL is contained in an array in Postgres?
How do I determine if NULL is contained in an array in Postgres? Currently using Postgres 9.3.3.
If I test with the following select it returns contains_null = false.
select ...
0
votes
0answers
88 views
Rails 4: How do I get data from a simple_form text input into a Postgres array?
I'm using Simple_form to list a POSTGRES array in a Rails4 app:
<%= f.input :categories, label: 'Categories:',
collection: @user.categories,
...
