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
142 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 ...
5
votes
0answers
109 views
How to optimize postgresql query with joins?
I wrote the query below this cost is around 1770077 I want to reduce the cost please suggest the best option.
SELECT eco.operationalstatus,SI.storecode,count(SI.gustoreid), ...
5
votes
0answers
251 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 ...
5
votes
0answers
330 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 ...
5
votes
0answers
672 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
79 views
How do I model a PostgreSQL failover cluster with Docker/Kubernetes?
I'm still wrapping my head around Kubernetes and how that's supposed to work. Currently, I'm struggling to understand how to model something like a PostgreSQL cluster with streaming replication, ...
kubernetes
4
votes
0answers
33 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
150 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
232 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 ...
4
votes
0answers
216 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
57 views
Load large data to R data.table from Postgresql
I store my data in Postgresql server. I want to load a table which has 15mil rows to data.frame or data.table
I use RPostgreSQL to load data.
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
...
3
votes
0answers
59 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
64 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
52 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
88 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 ...
3
votes
0answers
50 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 ...
3
votes
0answers
624 views
PostgreSQL 9.4: Aggregate / Join table on JSON field id inside array
Suppose you have the following tables, say
table: content_type
id serial not null
tabs json
table: data_type
id serial not null
html text
This is just a tiny example to illustrate.
The json ...
3
votes
0answers
86 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
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
261 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
350 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
150 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
177 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
29 views
Table design with postgresql
I am working on an inventory management system. In the back-end we are using POSTGRESQL database. We are having 3 tables:
1. invoice:
a. invoice_no CHARACTER VARYING(20), //PK stores the invoice ...
2
votes
0answers
10 views
PostgreSQL inherited foreign key workaround/issue?
I have a number of tables in Postgres that are also nodes in a graph I maintain with a transitive reflexive closure table. I had wanted to make the different tables inherit from a shared "node" table. ...
2
votes
0answers
43 views
Add data type from PostgreSQL extension in Slick
I'm using the PostGIS extension for PostgreSQL and I'm trying to retrieve a PGgeometry object from a table.
This version is working fine :
import java.sql.DriverManager
import java.sql.Connection
...
2
votes
0answers
24 views
SqlAclhemy / postgres JSON column: filter by null value
So I've got a model like such:
class myModel(Base):
id = Column(Integer, primary_key=True)
border = Column(JSONB)
How can I query for rows that don't have a border? I've tried:
...
2
votes
0answers
23 views
How to determine Strahler number on a directed graph for a stream network
Question / example / expected values
I need to determine a Strahler number or Strahler stream order for a directed graph representing a stream network. I can derive information forwards and backwards ...
2
votes
0answers
11 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
36 views
SQLAlchemy many to many against same table with no hierarchy
I would like to build a relationship between instances of a single table in Sqlalchemy using the declarative method.
I have a table called Hex, which represent hexagons on a map. I would like a table ...
2
votes
0answers
41 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
22 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
122 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
48 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
57 views
How to work with rails form and jsonb postgres
I'm looking at using the new jsonb capability in Rails4.2 and Postgres4.1.
I was looking at creating a model which represents a user profile (i.e. skill sets, etc) and storing the whole thing in a ...
2
votes
0answers
353 views
Error: Permission Denied @ rb_sysopen when trying to install gem pg in my rails app (Windows 7)
Using windows 7 ultimate and running cmd as administrator.
I have recently started working with ruby on rails and in my new rails app I'm attempting to install postgres(pg) gem and then I get a ...
2
votes
0answers
55 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
88 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
184 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
163 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
89 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
60 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
161 views
Cannot create postgres database with Doctrine
I have project in Symfony2 with doctrine. In one moment i loose ability to create/drop databases via console commands like
app/console doctrine:database:drop --force
app/console ...
2
votes
0answers
80 views
How to properly emulate statement level triggers with access to data in postgres
I am using PostgreSQL as my database for a project at work. We use triggers in quite a few places to either maintain computed columns, or tables that essentially act as a materialized view.
All this ...
2
votes
0answers
79 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
54 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
53 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
382 views
Rails 4 - postgresql 9.4 jsonb does not exist
I have used rails 4 and postgresql 9.4 in my project.when i was run "rdd && rdc && rdm && rds" and i got this error PG::UndefinedObject: ERROR: type "jsonb" does not exist how ...
2
votes
0answers
89 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
24 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 ...
