Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.
6
votes
0answers
130 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
28 views
Unpack a PostGIS/PostgreSQL record with SQLAlchemy
How would I write the following query using SQLAlchemy's ORM?
SELECT filename, (stats).*
FROM (
SELECT filename, ST_SummaryStats(rast, 1, TRUE) AS stats FROM tiles
) AS stats_table;
Here, ...
4
votes
0answers
140 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 ...
4
votes
0answers
295 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 ...
4
votes
0answers
651 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 ...
4
votes
0answers
212 views
How to get value of a variable in plv8 without using plv8.elog()?
I am having an issue with getting correct output from plv8.elog(). Just to start, I'm using PostgreSQL 9.2, plv8 1.4.1 as installed by pgxn, on Ubuntu 12.10. I have a function I am building that ...
3
votes
0answers
53 views
Subqueries with select function in CLSQL
I'm attempting to create a subquery with the clsql:select function:
CL-USER> (select [books.bookid]
:from [books]
:where
(sql-in [books.bookid]
...
3
votes
0answers
51 views
Why can't I delete from db using node-postgres?
Is there something special i need to do with a parameterized query?
the following seems to succeed (i'm using a promise-ified client.query see end),
console.log('cancel for', data);
var ...
3
votes
0answers
49 views
can't select from one table on a postgresql database via pyodbc - how to debug
I have a postgresql database containing some tables made by drupal, and others I've made myself. And then I have a python api, using pyodbc, to query the database.
There's just one table, that I ...
3
votes
0answers
78 views
Debugging Rspec Postgres lockups
I am trying to test an app that uses gem devise_token_auth, which basically includes a couple extra DB read/writes on almost every request (to verify and update user access tokens).
Everything is ...
3
votes
0answers
217 views
PostgreSQL ltree- vs tree module vs integer/string arrays or string delimited path
As you may know there's a module for PostgreSQL called ltree. Also you have the possibility to use the Array type for integers (*1, see comment below), which in this test shows to actually perform a ...
3
votes
0answers
41 views
UUID column value not available until a model reload
I have a model with a :uuid column set up by the following migration.
add_column :website, :uuid, :uuid, default: "uuid_generate_v4()"
It seems that the UUID isn't generated until I reload a newly ...
3
votes
0answers
73 views
Forced Binary Matrix company structure implementation in Rails
I'm working on a Rails project that models the structure of a company with a binary matrix plan schema. Each new user is 'placed' in the hierarchy as shown here:
There is no balancing and all ...
3
votes
0answers
241 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
210 views
How to get SQL text from Postgres event trigger
In a pgsql event trigger on tag ALTER TABLE, I wish to know which table is being altered.
The pg variables do not cover this, nor do the variables exposed by GET STACKED DIAGNOSTICS.
With variables ...
3
votes
0answers
328 views
Dumping of complex table and schema names on Windows is not supported. Phppgadmin. Export
I have a database named Extension
A schema named public
A tables named extension and mfo_3
Here's what happen. When I try to export the database from the schema not the public schema in phppgadmin, ...
3
votes
0answers
143 views
How to use USING clause in Alembic/SQLAchemy?
I would like to change column type of the database from string to integer by using Alembic. If I use pure SQL, it achieves the goal:
alter table statistic_ticket alter column tags type bigint using ...
3
votes
0answers
170 views
Transactional evolutions in Play
Is it possible to make evolutions transactional, so each evolution succeeds or fails
altogether?
I've tried to wrap the ups and downs with begin/commit block (I use PostgreSQL), but received a ...
2
votes
0answers
6 views
Django: using C collation on a Postgres column?
I am using Django 1.7 and Postgres 9.4. I would like to create an index using C collation on a VARCHAR column in my database.
This is because C-collated indexes can apparently make SELECT... LIKE ...
2
votes
0answers
14 views
Using json_populate_recordset without creating a table?
I have a table in my db that has a column containing json records.
id | json_records
---+-------------
0 | "[{'x1' : 1234, 'x2' : 5678},{'x1' : 2345, 'x2' : 6789}]'
1 | "[{'x1' : 4321, 'x2' : ...
2
votes
0answers
35 views
Query Failing in Postgres but Working in H2 (Postgres Mode)
I often find myself in a situation where I want to write automated tests for my database queries. My current use case is a Spring MVC web app and a Postgres database. As far as I can tell, I have ...
2
votes
0answers
21 views
How to properly wire together readers and writers for PostgreSQL COPY?
I'm trying to use PostgreSQL COPY command to import data without creating temporary files. Initial data is in a format that requires conversion so I have something like this:
void itemsToCsv(String ...
2
votes
0answers
90 views
Import psycopg2
I am stuck trying to import psycopg2. I am working in MacOSX10.10.1 and have downloaded the one-click version of Postgresql (http://www.enterprisedb.com/products-services-training/pgdownload). When I ...
2
votes
0answers
45 views
Scala Postgres IN Operator
I'm developing a web application based on Activator and Postgres (https://github.com/mauricio/postgresql-async).
I am trying to perform the following SQL query:
SELECT *
FROM table t_0
WHERE ...
2
votes
0answers
50 views
postgres restore with psql fails
I'm having what should be a simple task that is failing. I have some friends in Africa who are literally dependent on me getting this DB back online. The problem started when the power surged (Its ...
2
votes
0answers
82 views
Config postgres to accept any user/password combination in development mode
Doing freelance, I keep on getting on new projets.
I find postgresql config highly complicated when being used in development mode only (I totally understand that production requirements are much ...
2
votes
0answers
81 views
Hibernate performance issues
I am currently facing a performance problem with Hibernate and Postgresql.
I have this query (I use Criteria API with JPA MetaModel) :
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
...
2
votes
0answers
120 views
Inserting data to a PostgreSQL jsonb column via Scala Anorm (in Play Framework)
The data column of PostgreSQL table my_table is of format jsonb. I would like to insert a Scala JsObject (or JsValue), but don't know how to do it!
The following code does not compile, because the on ...
2
votes
0answers
144 views
Undefined property: stdClass after switching database to postgres in laravel
I have a site, that uses laravel 4.2 (stable) with Eloquent. I am also using this User messaging system. I wanted to switch database from mysql to postgres. On mysql, everything worked fine. I changed ...
2
votes
0answers
76 views
Django postgresql Datefield error
I'm migrating a mysql database to postgresql in Django and have encountered the following error:
django.db.utils.ProgrammingError: column "end_date" cannot be cast automatically to type timestamp ...
2
votes
0answers
44 views
Postgres Large Text Search Advice
I'm quite new to databases, and am looking for some high level advice.
The Situation
I'm building a database using Postgres 9.3, within the database is a table in which I store log files.
CREATE ...
2
votes
0answers
42 views
Postgres replication not starting due to wal error
I am using postgres version 9.3.2 on two servers one master, one primary.
I am setting up replication as follows:-
On master:-
sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ...
2
votes
0answers
61 views
PDO queries very slow over high latency, high bandwidth connection
Running postgresql 9.x (9.1 - 9.3)
I have a custom web app built using php's PDO library. Every query in our app uses prepared statements (via our internal PDO wrapper library).
Our production ...
2
votes
0answers
45 views
SQLAlchemy inheritance not working
I'm using Flask and SQLAlchemy. I have used my own abstract base class and inheritance. When I try to use my models in the python shell I get the following error:
>>> from schedule.models ...
2
votes
0answers
50 views
Postgres SHOW CREATE Type
I'm looking for a few ways to find a CREATE TYPE statement for an existing user defined type in a postgres (9.3.3) db.
So far I have tried 3 ways and seem to have failed on each:
1) An SQL Command?
...
2
votes
0answers
82 views
Speed up insert new data to PostgreSQL
My current workflow is as follows.
However, it is extremely slow. It only can handle millions of data per/day
I want to speed it up. Any idea?
query_expression (generated by Ruby rake file, then ...
2
votes
0answers
23 views
PSql: how to not remove comments for external editor?
I like to use psql, and sometimes I commenting part of query (usually one line) - something like "quick fix and see what you get".
Internal psql editor is not suitable for big queries, and so often ...
2
votes
0answers
294 views
java.sql.Connection createArrayOf() always returns null
I am trying to insert a series of values into a sql query using Java's postgresql jdbc4.
java.sql.Connection specifies a method to convert Object[] into java.sql.Array: conn.createArrayOf(String ...
2
votes
0answers
35 views
Handle Postgres server gracefully in rails
At the moment, when I start my postgres server (after a configuration change, e.g.), my rails app goes bonkers with FATAL errors while the server is restarting.
Instead of throwing an error, i'd like ...
2
votes
0answers
117 views
PostgreSQL - privileges to get metadata of database
What minimum privileges my user needs to get metadata (name of tables, columns, functions, etc.) from database?
I need to select from:
[database].information_schema.schemata
...
2
votes
0answers
104 views
How to increasing connection throughput for pgbouncer?
I am using pgbouncer in transaction mode & trying to allow close to 500 active transaction.
The purpose is simply to stress test the setup
Current setup:
[ 'n' clients --->1 pgbouncer ----> 1 ...
2
votes
0answers
31 views
error in converting xml to table postgresql 9.2
I have the following function for converting xml data in a Postgresql table written in plpgsql:
CREATE OR REPLACE FUNCTION inicio_actualizacion(dato xml)
RETURNS character varying AS
$BODY$
DECLARE
...
2
votes
0answers
28 views
Use cases for lateral that do not involve a set returning function
I was reading this post the other day:
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/
I suspected some of the claims in the post may not have been accurate. This
one in ...
2
votes
0answers
64 views
Index is ignored on joined view in Postgres
I have a table in my DB (Postgres 9.3.4) with index on couple of columns. Also I have a view based only on this table with grouping and filter by indexed columns.
CREATE OR REPLACE VIEW ...
2
votes
0answers
102 views
How to use Helios backend framework for iOS within a Ruby on Rails app?
I have been looking for a data synchronization solution for an iOS app I have been working on for quite some time, and I think I found the solution in the Helios backend framework. The first problem ...
2
votes
0answers
140 views
Authentication by identity file failed with error code -18
I received this error when trying to establish an SSH tunnel using pgAdmin3
I specified the private key location on my local machine (had to
enable hidden files on my Mac finder to see it),
entered ...
2
votes
0answers
59 views
Keep getting PG::UndefinedTable with namespaced Model
I have some models set up with namespacing..
rails g model Technology::Post ...
This has led to the error
PG::UndefinedTable: ERROR: relation "posts" does not exist
LINE 5: WHERE ...
2
votes
0answers
65 views
Calendar precision skew while parsing a postgresql timestamp with time zone
I noticed a bug while converting a postgresql datetime with time zone
SimpleDateFormat postgreSQLTimestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSSX");
Calendar inst = ...
2
votes
0answers
43 views
How can I kill an unresponsive query in Valentina Studio (for PostgreSQL)?
Is there a way to kill a slow or unresponsive query in Valentina Studio as there is in MySQL Workbench?
2
votes
0answers
30 views
Locking in JPA/EclipseLink (Postgres 9.1)
I refresh an entity with locktype PESSIMISTIC_WRITE.
Tab1 tab1 = em.find(Tab1.class, id, LockModeType.PESSIMISTIC_WRITE);
em.refresh(tab1, LockModeType.PESSIMISTIC_WRITE);
I have 3 tables:
...
