Tagged Questions
0
votes
0answers
24 views
Rails 4 Arrays working fine on heroku not on local
I have installed postgres on the development environment and still cant get the arrays to work on it. It keeps appearing as "{\"\",1,12,13}" as opposed to being [1,12,13] although the exact same ...
0
votes
0answers
31 views
PostgreSQL 9 efficiency of text vs integer arrays
I have a PostgreSQL DB where multiple fields are basically array of integers. Example:
1|2,4,8|4,3,8,15|8,7,5,15,30,29|16,14,10,30,29,60,58|
2|2,4,8|4,3,8,15|7,5,15,30,29|14,10,30,29,60,58|
...
0
votes
0answers
21 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 ...
1
vote
2answers
24 views
Select subarray and its length in PostgreSQL
How can I get subquery array and it`s count?
SELECT location.country,
(SELECT array(
SELECT competition.id
FROM competition
WHERE location.id = ...
-4
votes
1answer
34 views
Checking if array is contained in another array in PostgreSQL
There is an array[10,20] and I want to know is it a subset of array[20,30,10] or not?
Examples:
[10,20] and [30,20,10] - yes
[10,20] and [10,30] - no
[10,20] and [20,10] - yes
[10,20] and ...
0
votes
0answers
17 views
get array field from postgreql database with libpqxx
In my PostgreSQL database I have a table with boolean[32] array field (let it be called "mask"). How can I fill C++ bolean array with data from "SELECT mask FROM mytable WHERE " query? For integer ...
-3
votes
1answer
71 views
avoid looping over sql update statement [closed]
I have an array that looks like this:
array(
[1234]=>4321,
[5678]=>8765,
....
)
The key is the id of the table field and value is the count.
Is there any way to insert that array ...
1
vote
0answers
23 views
Trying to post an array with `pg`, but keep getting wrong type error - Postgres, Express, Node
I have a route in my express app that takes an array and I am trying to update my db with the array. Here is my route:
exports.postUser = function(req, res) {
var query = client.query('insert into ...
0
votes
1answer
45 views
Rails. Permit text, validate it, make array and save to postgres. How?
I have Rails form with textarea. User puts text spltted by comma, then submit to server, where data validates, splits to array and writes to postgres database.
But there is null array in callback ...
1
vote
1answer
100 views
Error undefined method `to_key' for [xxx] :Array when trying to use array data on Active Admin table_for (Rails, Postgresql, postgres_ext gem)
I'd like to have use the data i manage to get inside an array of array to put them inside a table(table_for) on one of my Dashboards on Active Admin
My app is a Daily Deal app built to learn more ...
1
vote
1answer
37 views
Does an Array (UUID[ ]) break 1NF?
My question is whether the inclusion of a field that contains an array of UUIDs breaks the Normal Form, in a table that before the inclusion of the array was in NF?
Original table:
CREATE TABLE ...
1
vote
1answer
56 views
Count occurrence of values in a serialized attribute(array) in Active Admin dashboard (Rails, Active admin 1.0, Postgresql database, postgres_ext gem)
I'd like to have a basic table summing up the number of occurence of values inside arrays.
My app is a Daily Deal app built to learn more Ruby on Rails.
I have a model Deals, which has one attribute ...
0
votes
2answers
37 views
Postgres format string using array
I'm looking for an easy way to format a string using an array, like so:
select format_using_array('Hello %s and %s', ARRAY['Jane', 'Joe']);
format_using_array
--------------------
Hello Jane and ...
1
vote
1answer
38 views
How to split the array and store into respected columns of table in Postgresql DataBase
I am inserting the array of elements {idno,age,salary} as {1,10000,30}
in PostgreSQL database and I need to split and store into the respected columns of the table.
How to store the split array ...
2
votes
1answer
34 views
rails postgres read array out of db
I have a postgres db with each row representing an "index" (just a name not the keyword). For each market, there is an attribute "stats" thats a two dimensional array of numbers that I put into the db ...
0
votes
1answer
29 views
Printing all rows from a Postgres database array
I'm trying to do a simple query from my postgres database, then try to manipulate the data.
Script I made goes as following;
function connectLocalDB() {
$dbconnection = ...
-1
votes
1answer
63 views
How to save numpy array of Strings (with commas) to CSV?
tl;dr ANSWER: Don't use numpy. Use csv.writer instead of numpy.savetxt.
I'm new to Python and NumPy. It seems like it shouldn't be so difficult to save a 2D array of strings (that contain commas) to ...
0
votes
1answer
48 views
Iterating on each element from an array of arrays on Postgresql
I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:
{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9} -- This is a STRING
Now with ...
0
votes
1answer
47 views
Select substring from a varchar and convert to Integer array
I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:
{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9} -- This is a STRING
I want ...
2
votes
1answer
54 views
How to use array variable in PostgreSQL array_append()?
I would like to use array variable in PostgreSQL array_append(). If I use array directly then its works but not when using variable.
$name= {1,2,3};
$Name_key_array={4};
I would like to find ...
0
votes
1answer
118 views
Create simple C function for PostgreSQL with array input and array output
Can anyone share an example how I can create C function for PostgreSQL which takes array of two integers as input and returns array as output?
For simple integer I have:
#include "postgres.h"
...
1
vote
1answer
63 views
Passing array of custom type to postgres function from node-pg and SQL injection
CREATE TYPE phototable AS (
photoid integer,
parentid integer,
fileextension character varying(20),
description text,
tag character varying(100)
);
CREATE FUNCTION addphotos(
...
0
votes
1answer
41 views
Postgres 8.1 error message (data type)
Im using postgres version 8.1. I want to use string to array and unnest functions. 8.1 does not support unnest so I've created it using code found via Google and stackoverflow.
My error message is ...
1
vote
1answer
46 views
Check if any of a given array of values are present in a Postgres array
I'm writing a Rails 4 app and have a string array column on one of my models called identifiers. When the user passes in a list of identifiers, what's the syntax to fetch the rows where any of the ...
0
votes
1answer
58 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
41 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
60 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
80 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 ...
1
vote
1answer
27 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
1answer
60 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
144 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
101 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
37 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 ...
1
vote
1answer
53 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
72 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
86 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
48 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
33 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
41 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
138 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
218 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
119 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
69 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
50 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
89 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
94 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
940 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": ...
1
vote
1answer
29 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
53 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 ...
3
votes
1answer
93 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 ...
