Tagged Questions
0
votes
1answer
19 views
Saving array to postgres in Rails raises ArgumentError: wrong number of arguments (3 for 2)
Using Rails 4.0.1 with postgres I have an activerecord column stored as an array
create_table "accounts", force: true do |t|
t.string "schedule_days", default: [], array: true
end
I can assign an ...
1
vote
1answer
22 views
Return type for function with array_agg()
I'm trying to create a function that returns an array of string, I'm able to do it without a function and returns a record[] type, when i try to return that type of result in the function it says that ...
0
votes
1answer
36 views
Apply function to every element of an array in a SELECT statement
I am listing all functions of a PostgreSQL schema and need the human readable types for every argument of the funtions. OIDs of the types a represented as an array in proallargtypes. I can unnest the ...
1
vote
1answer
51 views
How to use array_agg() for varchar[]
I have a column in our database called min_crew that has varying character arrays such as '{CA, FO, FA}'.
I have a query where I'm trying to get aggregates of these arrays without success:
SELECT ...
0
votes
1answer
22 views
build link using postgresql and php
)
I have a question:
I have a databse with the following information:
Name of report [name]
Link to report [link]
The various reports are displayed in tables in my page (using db_tables filed with ...
0
votes
0answers
36 views
PostgreSQL - Creating a temporary table from a multidimensional array
I have an application where I would like a user to be able to upload an spreadsheet which will end up being inserted into one of our PostgreSQL database tables. The guys that will be doing the ...
1
vote
1answer
46 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 ...
0
votes
1answer
33 views
PostgreSQL GIN index on array of uuid
I would like to use a GIN index on uuid[] (to have efficient membership tests for arrays of uuids). However when I try it PostgreSQL gives me an error:
mydb=> CREATE TABLE foo (val uuid[]);
CREATE ...
1
vote
1answer
23 views
How to replace an array of strings in a column of text in PostgreSQL?
I have 2 text columns where I need to replace (on update) chars from array 1 ('q','x','y','z') to their index-equivalent value in array 2 ('a','b','c','d').
The closest I've come to atm is to nest ...
0
votes
1answer
45 views
Function to create N arrays from one data group
I am new in PL/pgsql and I would like to create a new Function.
In my case I have this table:
Column 1: VARIABLE = 2 2 2 2 2 2 2 2 2 2
Column 2: VALUE = 20 20 20 20 180 180 180 180 180 180
Column3: ...
0
votes
1answer
41 views
PostgreSQL retrieve records with items in array with array_agg
My database system is PostgreSQL 8.4
The following is the example schema:
CREATE TABLE items(
item_code varchar(20) NOT NULL PRIMARY KEY,
item_description varchar(200),
item_pieces_per_pack ...
0
votes
1answer
54 views
Postgresql function If condition then run query else stop
I need some help with the following postgres functionality
I have the following table with the columns:
array, array_length
I initially have a few arrays in and then I run a query(actually a set ...
0
votes
1answer
43 views
Remove unwanted elements from arrays
I have a table scraped_listings and a table scraped_categories. The column scraped_listings.categories is an integer[] array containing ids of rows in scraped_categories.
Somehow (probably through a ...
0
votes
1answer
30 views
PHP script not indexing my PostgreSQL array properly
I'm facing a problem with reading an associative array from an SQL query on my PostgreSQL database.
So here's my database:
ID | NAME | ....
1 | CARS |
2 | BIKES|
3 | TRAINS |
Now I have a PHP ...
0
votes
1answer
36 views
Is it possible to query a specific index of a postgres array with Rails 4?
With a database column migrated like this:
add_column :documents, :array_column, :string, array: true
I understand it's possible to do this to query any element of the array:
Document.where("'foo' ...
3
votes
1answer
77 views
Passing an array of arrays as parameter to a function
A web application can send to a function an array of arrays like
[
[
[1,2],
[3,4]
],
[
[],
[4,5,6]
]
]
The outer array length is n > 0. The middle ...
1
vote
1answer
79 views
Array of composite type as stored procedure input
I am creating a stored procedure using plpgsql by passing a type array and do a loop inside the procedure so that I can insert each info type
CREATE TYPE info AS(
name varchar,
email_add ...
2
votes
3answers
67 views
converting the varchar dataype to text[] array
I have table A(colmn1 varchar(10),column2 varchar2(20));
but I need to convert the datatype varchar to text[] array
table A(column1 text[],column2 text[])
please tell me how to alter this table ...
0
votes
2answers
58 views
Postgres: “Recursive” query with Array
I have a database (that I can't change) one table looks like that:
| ID:integer | fk:integer | next:[integer array] |
--------------------------------------------------
| 1 | 711 | ...
1
vote
2answers
38 views
PostgreSQL search all indexes of an array
In my table 'products' I have a column named 'keywords'.
It's defined like this:
CREATE TABLE products (
id integer,
keywords character varying[]
//rest of the ...
0
votes
1answer
63 views
Apply aggregate functions on array fields in Postgres?
Is it possible to apply aggregates (like avg(), stddev()) on all the values in an integer[] field (or other arrays of numbers)?
CREATE TABLE widget
(
measurement integer[]
);
insert into widget ...
1
vote
1answer
79 views
Writing a PosgreSQL aggregate function where the input values are arrays
I have a table whose columns are arrays of reals, like this
my_column
-----------------
{5.7, 1.5, 1.7, ...}
{4.2, 4.1, 2.6, ...}
etc
and I wish to yield an aggregate array of sums where each ...
5
votes
2answers
450 views
Querying inside Postgres JSON arrays
How would you go about searching for an element inside an array stored in a json column?
If I have a JSON document like this, stored in a JSON column named blob:
{"name": "Wolf",
"ids": ...
mongodb
1
vote
1answer
23 views
Extract a portion of an array in postgresql
I have an array of type character.
For ex: {AA,BB,CC,DD,EE,FF,GG, hh, II, jj,KK}
From the above array i need to extract for ex: from 3rd position to 7th position which is {CC,DD,EE,FF,GG}
Please ...
1
vote
1answer
44 views
What is the effect of the second argument to generate_subscripts?
I'm trying to understand the second argument to the PostgreSQL generate_subscripts function. The documentation states that the second argument is the dimension of the array over which subscripts ...
4
votes
1answer
88 views
Array column field rendering in fields_for … Not Rendered
An array filed saved in db as array .. every item in this array should be rendered as text_fields in a fields_for. (In my Rails 4 App with Postgres DB)
which is not rendered as expected ... please ...
0
votes
1answer
42 views
How to delete/replace a text[] value in any position
I have a table with a text[] field:
page_id | tags
------------------+------------------------------------------------
test_page | {"first tag","second ...
0
votes
2answers
80 views
Create array dynamically inside begin and end
How to create an array dynamically inside PostgreSQL?
Consider this e.g.:
CREATE OR REPLACE FUNCTION fun( )
RETURNS SETOF void AS
$BODY$
DECLARE
i numeric;
BEGIN
FOR i in 1..10 LOOP
//I ...
0
votes
1answer
94 views
Validations for Rails 4 Postgres Array
Is it possible to use standard Rails validations with a Postgres array? I have a service_area column which accepts an array of zip codes. It works well enough, though when I try to use validations for ...
2
votes
3answers
49 views
Braces inside the array - postgresql
I have a table "temp" with two attributes: integer, text[].
I would like to insert a record with the brace inside the array.
For example a record like this:
1, {'1{c}1','a'}
where 1 is the ...
2
votes
1answer
65 views
postgres, contains-operator for multidimensional arrays performs flatten before comparing?
The following query:
SELECT ARRAY[[1,2,3], [4,5,6], [7,8,9]] @> ARRAY[2, 3, 5];
gets responded with true and not false as expected, since the array[2, 3, 5] doesn't exist in the source array. ...
1
vote
1answer
99 views
Inserting a Matlab Float Array into postgresql float[] column
I am using JDBC to access a postgresql database through Matlab, and have gotten hung up when trying to insert an array of values that I would rather store as an array instead of individual values. The ...
1
vote
1answer
107 views
PostgreSQL function to check for an enum value in an array
Types:
CREATE TYPE equipment AS ENUM ('projector','PAsystem','safe','PC','phone');
CREATE TYPE building_code AS ENUM ('IT','EMS','HSB','ENG');
Tables:
CREATE TABLE venue (
id INTEGER DEFAULT ...
4
votes
1answer
183 views
Convert multi-dimensional array to records
Given: {{1,"a"},{2,"b"},{3,"c"}}
Desired:
foo | bar
-----+------
1 | a
2 | b
3 | c
You can get the intended result with the following query; however, it'd be better to have something ...
1
vote
1answer
102 views
Postgres array of JSON
Issuing on Postgres 9.2 the following:
CREATE TABLE test (j JSON, ja JSON[]);
INSERT INTO test(j) VALUES('{"name":"Alex", "age":20}' ); -- Works FINE
INSERT INTO test(ja) VALUES( ...
1
vote
1answer
46 views
How can I add the array to the array of arrays in SQL (Postgres)
I have a table with an attribute that is an array of arrays.
Example: a={{1,A, 1},{A,B,C},{45,46,47}}
I want to add an array to the array "a".
Example: new_array={Z,T} to "a" in order to have ...
0
votes
1answer
33 views
Postgresql: Searching within arrays for an arbitrary number of inputs
I have a record in my table (table1) whose name is "Jonh Wood Doe Smith" and I want to return it even if the user types any of the possible combinations: "John Doe", "Jonn Wood Smith", etc
I ...
1
vote
1answer
93 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[], ...
0
votes
1answer
94 views
How to join a view on an array_agg column in Postgresql?
I'm searching for the syntax to join a view with an array_agg column.
View:
CREATE VIEW sampleview AS
SELECT groupid, array_agg(akey) AS keyarray
FROM sampletable
GROUP BY groupid;
Now I want do ...
3
votes
1answer
162 views
Ruby on rails form for postgresql array
I haven't found any example of how to make a dynamic form for a field which is a Postgresql array.
I want to fill it dynamically using Mustache.js. I have done it before but with a string field using ...
0
votes
1answer
31 views
Using WHERE clause to only retrieve results where array field has only ONE element inside
I have a table with various arrays within it like this:
{10574664,10574665,10574679,10574724}
{8616204,10574643,10574644,10574645,10574651,10574688,10574690,10574696,10574708}
{8616208}
{9830397}
...
2
votes
1answer
247 views
How to use xpath in Postgres to align XML elements
This query:
with x as (select
'<promotions>
<promotion promotion-id="old-promotion">
<enabled-flag>true</enabled-flag>
...
2
votes
1answer
108 views
Array field not being recognized as attribute
I have the following model I created,
class CreateUsers < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :name
t.string :email
t.string ...
0
votes
1answer
56 views
PostgreSQL HSTORE GIN query
I can't figure out how to re-write this query using arrays for the test cases:
--explain
SELECT COUNT(id)
FROM (
SELECT T.id
FROM product2 AS T
WHERE (ext @> 'p01=>1' OR ...
0
votes
1answer
88 views
How to aggregate all array values of multiple records in Postgres array?
I have a table full of records and an array column, and now I want to get a list of all (unique) values in that column. What's the best way of getting that?
I tried playing around with unnest, ...
2
votes
2answers
65 views
Return array of years as year ranges
I'm attempting to query a table which contains a character varying[] column of years, and return those years as a string of comma-delimited year ranges. The year ranges would be determined by ...
0
votes
2answers
106 views
Python: Array vs Database for storage of key/value
Q: Which is quicker for this scenario?
My scenario: my application will be storing either in either an array or postgresql db a list of links, so it might look like:
1) mysite.com
a) ...
0
votes
2answers
37 views
Outputting a query of rows to a table in php
I'm using the below code to output rows from my table called data_cases:
$query = 'SELECT * FROM mydb.data_cases
WHERE id=123 ORDER BY log_date_time DESC';
$result = pg_query($query) or ...
3
votes
3answers
823 views
New data not persisting to Rails array column on Postgres
I have a user model with a friends column of type text. This migration was ran to use the array feature with postgres:
add_column :users, :friends, :text, array: true
The user model has this ...
2
votes
1answer
213 views
PostgreSQL PDO insert into array type
I'm trying to use PHP PDO to insert values into a PostgreSQL database, but encounter the following error message:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: array value must start with ...
