Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions. Greenplum Database, Amazon Redshift, ParAccel, Postgres-XC, ...
10
votes
0answers
291 views
Seq Scan and Index Scan for primary key return different rows
I dont need performace optimization. I have problem of data with table "users":
"primary_c26a3d1a9d1c7aa4bb0a8d6b752c01a7" PRIMARY KEY, btree (id)
I have duplicates in Seq Scan:
=> WITH ...
4
votes
0answers
77 views
SQL Query in Sequelize getter method
I'm using the Postgres extension 'earthdistance' for lat/long distance calculation.
I'm also using Sequelize to access the database and I want to define a getter
method for calculation and sorting by ...
4
votes
0answers
44 views
Carrierwave mounted to Rails 4 PostgreSQL array attribute
Since Rails >= 4.0 adds support for PostgreSQL array data types I was wondering if anyone already played with carrierwave attachments mounted to array attributes instead of join tables when a model ...
4
votes
0answers
208 views
Unicorn keeps losing connection to postgres database on heroku
I have setup an app on heroku using the unicorn web server.
My database is setup on an external server (not on heroku). I have configured the DATABASE_URL as required.
When I run heroku console I am ...
3
votes
0answers
21 views
How to fix PG::DuplicatePstatement: ERROR?
I would like to know how to prevent this type of error. So far I tried disabling prepared statements via the database configuration at AWS Opsworks:
Eg:
Database Configuration
},
"deploy": {
...
3
votes
0answers
49 views
txpostgres stored procedure returns cursor
I'm trying to get a cursor from stored procedure in txpostgres.
Psycopg2 has a named cursors which are working fine for it. But there is no curs = conn.cursor('name') statement in txpostgres.
Is ...
3
votes
0answers
70 views
sonar V.4.3.1 does not start with postgresql
steps
- install the server
- change JDBC URL to point to a new created poststreSQL database (default schema / with sonar user). There the default H2 JDBC URL is disabled, and only activated ...
3
votes
0answers
68 views
postgresql data recovery after multiply rows update
I am trying to retrieve data lost on wrong update query, using xlog and pg_xlogdump tool.
The query was the following:
update table set column1 =21 where column2 > column3 ( 1 condition was missed, ...
3
votes
0answers
81 views
Postgresql permissions keep failing
I am running PostgreSQL 9.3.2 on Linux.
I have a group role 'data_scientist' and permissions to a particular (already populated) schema like so
grant usage on schema schemaname to data_scientist;
...
3
votes
0answers
148 views
Adding Postgres index to one table locks up another
I look after a single Postgres 9.3.3 (Amazon RDS instance: db.m3.2xlarge), which is the back-end of a system that logs incoming statistics and provides reports based on those data - yes, from the same ...
3
votes
0answers
303 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
...
3
votes
0answers
124 views
UPSERT in PostgreSQL using jOOQ
I am trying to perform an UPSERT in PostgreSQL using the jOOQ library.
For doing this I am currently trying to implement the following SQL statement in jOOQ:
http://stackoverflow.com/a/6527838
My ...
3
votes
0answers
335 views
Can't connect to pgpool
I'm playing around pgpool2.
I'm connecting to postgresql which is running on port 5432 with command psql -U postgres -p 5432 and it's connecting normally.
When I'm connecting to pgpool2 running on ...
3
votes
0answers
204 views
Is it possible to use postgresql 9.3 json type in associations (belongs_to) as foreign_key
I have ruby on rails 4.0 and postgresql 9.3.
I have two models:
class Person < ActiveRecord::Base
belongs_to :address_city, :class_name =>'City', :foreign_key => "address['city_id']"
...
3
votes
0answers
439 views
Ansible postgresql_db task fails after a very long pause
The following ansible task (in a vagrant VM) fails :
- name: ensure database is created
postgresql_db: name={{dbname}}
sudo_user: postgres
the task pauses for a few minutes before failing
the ...
2
votes
0answers
31 views
sails postgresql many-to-many association not working
I'm trying to create a many to many association between two models, Operator and Group.
Two two models are:
-Operator.js
var Operator = {
connection:'postgresql',
tableName: 'operator',
...
2
votes
0answers
17 views
How to implement an append-only versioned model in SQLAlchemy
I would like to re-implement some of my existing SQLAlchemy models in an append-only datastore; append-only meaning that object are only updated with INSERT statements, not using UPDATE or DELETE ...
2
votes
0answers
35 views
How to delete HSTORE keys?
I have the following migration:
class CreateFoos < ActiveRecord::Migration
def change
create_table :foos do |t|
t.hstore :foos_properties
end
end
end
In the hstore column, I ...
2
votes
0answers
16 views
How to enable PostgreSQL in Xampp on Mac OS?
Can anyone please tell me how I can enable PostgreSQL in Xampp on Mac platform? Or is there any other GUI alternative like XAMPP for Mac platform.
Thanks.
2
votes
0answers
17 views
Error on deleting postgres role
I am trying to delete a psql role, reassigning linked object to postgres role beforehand.
But I still get the error message removal of role "xxx" failed: ERROR: role "xxx" cannot be dropped because ...
2
votes
0answers
21 views
Connecting Tableau to Heroku Follower Database
Have created a follower database for a Heroku Postgres DB. I am able to connect to the follower DB by using pgAdmin, no problem.
When I try to connect with Tableau 8.2 Postgres connector using the ...
2
votes
0answers
34 views
How to format a SQLAlchemy ORM Postgresql CIDR contains (>>) query
I have a model defined with:
from app import db
from sqlalchemy.dialects import postgresql
class TableIpAddress(db.Model):
__tablename__ = 'ipaddress'
idipaddress = db.Column( postgresql.UUID, ...
2
votes
0answers
26 views
Postgres hstore update all without deleting existing data
I've started working with rails 4.1.5 and postgresql 9.3.5, so my question is how to use update_all with hstore in postgress in this situation(if that's even possible):
"1" => [1, 2, 3]
so if "1" ...
2
votes
0answers
108 views
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
I am trying to connect to postgres database which is in remote location using spring jdbc template.
I am getting org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host ...
2
votes
0answers
46 views
Finding which column caused the postgresql exception in a query.
I have a staging table with around 200 columns in Redshift. I first copy data from S3 to this table and then copy data from this table to another table using a large insert into select from query. ...
2
votes
0answers
33 views
Json parser issue with escaped double quote in Redshift
As per Redshift forum this is known issue.
I just used
json_extract_path_text(regexp_replace(event_properties,'\\\\\\\\.',''),'someValue')
but it is loading as
...
2
votes
0answers
33 views
Password-less access to PosgreSQL server from MATLAB (on Windows)?
I'm trying to get password-less access to a PostgreSQL server from within MATLAB, relying on a %APPDATA%\postgresql\pgpass.conf file, as described in the documentation.
The password-less access works ...
2
votes
0answers
49 views
Fast access to a PostgreSQL database from AutoHotKey
I'm searching for a way to interact with a PostgreSQL database from an AHK script.
All the ways I currently found include running psql.exe from the command-line and then parsing output. Of course, ...
2
votes
0answers
92 views
SSL connection has been closed unexpectedly
I have developed an application using Codeigniter and PostgreSQL as the database.
Codeigniter application resides on IP 192.168.X.34 => Application server
PostgreSQL server resides on IP ...
2
votes
0answers
59 views
Unable to write data to table with postgresql OID type
I'm facing very strange problem with storing data to OID columns on PostgreSQL. The app configuration is:
Java OpenJDK 64-Bit 1.7
JBoss Seam 2.2.2.Final
Hibernate 3.6.4.Final
PostgreSQL 9.3 (with ...
2
votes
0answers
101 views
DRP for postgres-xl
After installing and setting up a 2 node cluster of postgres-xl 9.2, where coordinator and GTM are running on node1 and the Datanode is set up on node2.
Now before I use it in production I have to ...
2
votes
0answers
48 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 ...
2
votes
0answers
83 views
will_paginate, Rails 4.1.1 pg error with count
I receive this error:
Completed 500 Internal Server Error in 73ms
2014-06-24T16:23:01.334241+00:00 app[web.1]: Processing by CollectionsController#index as HTML
2014-06-24T16:23:01.409751+00:00 ...
2
votes
0answers
89 views
Storing primary keys of objects in Redis and then querying them in Postgres through Django
All suggestions, advice and insight on this matter is extremely appreciated. I require you're brutal honesty as well. If you think what I'm currently doing in terms of design is really stupid, TELL ...
2
votes
0answers
53 views
Switching Django App Back to PostgreSQL, No Relations Found
I switched to my database engine to PostGIS and now I'm switching back to PostgreSQL (so I can continue to use Heroku for free). I changed my settings.py file back to:
DATABASES = {
'default': {
...
2
votes
0answers
308 views
Amazon RDS: PostgreSQL Performance Poor
We use postgresql as a backend for Apache. We are planning to migrate our PostgreSQL from an EC2 instance (m1.xlarge +500GB volume/Ubuntu) to RDS. However, in our testing on the same sized instance ...
2
votes
0answers
80 views
Postgresql 9.3 group by overlapping/adjacent ranges
My Data
I have a table that has a few columns of type TSTZRANGE that represent nested timelines.
It looks like this:
CREATE TABLE data (
KeyID int8 NOT NULL,
Part int4 NOT NULL,
Value ...
2
votes
0answers
218 views
I am trying to save data in postgres table after reading from an excel file.While trying to commit,I get a casting error
Model Class
package test1;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="employee_details")
public ...
2
votes
0answers
209 views
Error message installing Django-toolbelt on MacOSX
I'm attempting to follow the tutorial on Heroku to deploy a Django app. I'm doing this from within a virtualenv environment, as per the tutorial ...
2
votes
0answers
77 views
Occasional “General error” Occurs on INSERT into POSTGRES Database from Java
I'm trying to insert a collection of data from Java into a Postgres database. Both the JVM and the database are 64-bit. Querying the database and viewing the output showed that the code correctly ...
2
votes
0answers
203 views
Multiple ways to create index on a json field's nested property in PostgreSQL 9.3
In PostgreSQL 9.3, there are multiple ways to build an expression, which points to a json field's nested property:
data->'foo'->>'bar'
data#>>'{foo,bar}'
json_extract_path_text(data, ...
2
votes
0answers
47 views
Create PostgreSQL 9.3 index on json column through python, django, south?
I have a json column and I need to add an index like that:
CREATE UNIQUE INDEX index_name ON table ((column->'nest1'->'nest2'->'nest3'->>'data_to_index'));
This works PERFECTLY if I ...
2
votes
0answers
46 views
Database functions mapped to ActiveRecord Models
Let's suppose I have two database functions like this (pl/pgsql):
CREATE TABLE balances (rents decimal(12,2) DEFAULT 0 NOT NULL,
expenses decimal(12,2) DEFAULT 0 NOT NULL);
CREATE FUNCTION ...
2
votes
0answers
284 views
rake db:migrate does nothing in Rails 4.1 app - exits without errors
I'm setting up a brand new Rails 4.1 app. Everything goes smoothly until I run my first rake db:migrate command. Nothing happens. I ran trace - it doesn't look like it's processing my migrations but ...
2
votes
0answers
103 views
How to implement business logic permissions in PostgreSQL?
Let's assume I have a table of items:
CREATE TABLE items
(
item serial PRIMARY KEY,
...
);
Now I want to introduce the concept of "permissions" for each item (note, I'm not talking about ...
2
votes
0answers
106 views
How to specify an ActiveRecord attribute for a timestamp when the column name has spaces?
I have a column in my postgres database like this:
"Last Execution Timestamp" timestamp without time zone,
Literally a column name in quotes with spaces, for timestamp.
How would I set and get ...
2
votes
0answers
168 views
passing parameter value through pgoledb to postgresql stored function
I am attempting to migrate a legacy client-server GIS application from Microsoft SQL Server to PostgreSQL. In this system the SQL Server database contains a large set of T-SQL stored procedures, each ...
2
votes
0answers
17 views
is there a way to search for deeply nested json in activerecord with postgres?
Suppose I have a foos table that has a json column called "json_col"...
Is there a way with ActiveRecord to do:
Foo.create!(:json_col => { :foo => { :bar => 'baz' } }.to_json)
...
2
votes
0answers
83 views
Django select_related with join on multiple fields
I have a somewhat complex database in django. It makes extensive use of partitioned tables. I have had some problems before with django and partitioned tables but so far I have found satisfactory ...
2
votes
0answers
148 views
Doctrine2 drive me crazy with OneToMany and ManyToOne relationship
I try to work Doctrine2 Mapping out but after 8 hours of problems I really need your help, please.
I have 3 PostgreSQL table, The first one is house
Column | Type | ...
