for PostgreSQL questions specific to version 9.3.
4
votes
0answers
143 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
230 views
How to configure postgres+pgbouncer to allow large number of connections
I am in the process of stress testing pgbouncer with 5000 connections.
The purpose of the test is to multiplex the connection using pgbouncer i.e. 5000 client connection to 500 db connections.
...
2
votes
0answers
358 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, ...
1
vote
0answers
14 views
Postgresql partial index on date to query sub periode
On some very big tables, one of the most common indexes are on date field (e.g. date of insert, or date of operation).
But when the table is very large, the index tend also to but large.
I thought I ...
1
vote
0answers
34 views
Postgresql trigger procedure order
I have 3 tables (general_log, queue_log, temporary_processed) and 2 stored procedure that process the rows. In the 3rd table (temporary_processed) the events are assembled in order be further ...
1
vote
0answers
34 views
Merge results of WITH RECURSIVE queries
I have the table called users_edges for storing company employees structure.
It has the following columns:
id integer Auto increment [nextval('users_edges_id_seq')]
from_user_id integer ...
1
vote
0answers
25 views
postgrest post request -Getting Error for string passing in raw sql
I am getting error for raw sql if i am using any string literals inside the sql query.
http://localhost:3000/db/test
{
"sql": "select * from party where party_name='xxx' "
}
error:
...
1
vote
0answers
41 views
PostgreSQL casting with type specified in string variable
I am experimenting with the new JSON/JSONB objects in the latest(9.4) PostgreSQL. First, I will show you my test table:
CREATE TABLE "JSONtest" (
data jsonb
);
COPY "JSONtest" (data) FROM stdin;
...
1
vote
0answers
81 views
Postgresql hot_standby > How to calculate wal_keep_segments setting
I am having a trouble setting up a postgresql hot_standby. When attempting to start the database after running pg_basebackup, I receive, FATAL: could not receive data from WAL stream: ERROR: ...
1
vote
0answers
68 views
pgloader - fast data loading for PostgreSQL
I would like to accelerate loading data to PostgreSQL. I started using the pgloader https://github.com/dimitri/pgloader and wanted to utilize parallel loading. I was tinkering with different ...
1
vote
0answers
26 views
Postgres connection actively refused
I am having a problem connecting to postgres from my java app.I am able to login from pgAdminIII using postgres role and the right password
<db.host>127.0.0.1</db.host>
...
1
vote
0answers
45 views
Can Hibernate 3.6 persist a field as NULL if the value matches some default?
I have many entities with several Enum and Boolean fields. In order to make these objects safe to work with in code, the fields use default values and are never NULL (as a policy). However, in the ...
1
vote
0answers
30 views
Creating a filtering thesaurus in Postgresql
I am using Postgresql for full text search and I am having trouble creating a filtering thesaurus, in the way described by Postgresql documentation on Full Text Search using dictionaries (12.6).
I ...
1
vote
0answers
72 views
ERROR: current transaction is aborte d, commands ignored until end of transaction block
I've been trying to run a .sql file in my postgresql. using cmd
"%PROGRAMFILES%\Postgresql\9.3\bin\psql.exe" -U postgres -d postgres -a -f C:\Sites\xmastool\postgresql.sql
but after doing so, ...
1
vote
0answers
178 views
Difficulty connecting to MS SQL Server from Postgresql on Windows (using DBI-Link)
Dear Stackoverflow Gurus,
I am having an issue connecting to MS SQL Server (2008) from PostgreSQL 9.3 on Windows. I am trying to connect through DBI-Link DBD::ODBC for Sql Server.
I would like your ...
1
vote
0answers
48 views
pg upgrade saving database definition taking time
I am in process of a pg_upgrade from 8.4 to 9.3.
I am using this technique:
http://momjian.us/main/writings/pgsql/pg_upgrade.pdf
The upgrade has been running since 250 hours, and it has been on the ...
1
vote
0answers
31 views
How copy a file stored as largeobject in postgres?
I use postgres 9.3 as DB, Spring 4.0.6 and the postgresql-9.3-1101-jdbc41.jar driver.
I have the code to store a File into DB with the LargeObjectManager.
I have also the code to retrieve the data ...
1
vote
0answers
80 views
How to modify parser in PostgreSQL to handle new keyword and parse it
I am working on implementing Selectivity hints feature in PostgreSQL 9.3.4. I am working on this only for using it in my academic research. I have decided to give selectivity information per relation ...
1
vote
0answers
100 views
How to make PostgreSQL use the proper query plan with this JSON
I'm using PostgreSQL 9.3.4. I have a table which is defined as follows:
CREATE TABLE report (id int, keyval json, time timestamp);
The table includes a column (keyval), specified as JSON. It ...
1
vote
0answers
112 views
PostgreSQL user-defined operator function: what parameter type to use for uncast character string?
(Cross-posted to (and answered on) pgsql-general)
I'm using PostgreSQL, defining my own domain and a equality operator. I cannot cause PostgreSQL to use my operator function in a query without ...
1
vote
0answers
88 views
sql performance: recursive query check contains array
I would like to find beatiful solution for a query which checks whether an array is contained in the result of a recursive search.
DBMS is Postgresql 9.3
Supposing we have users
CREATE TABLE users
...
1
vote
0answers
42 views
Upload of big files to data directory of postgresql 9.3
After 2 weeks of huge efforts to solve one postgresql problem I'm coming here as last hope.
What I'm trying to do:
I need to store and retrieve huge files (sizes between 1 MB to 2000 MB) to ...
1
vote
0answers
166 views
Postgresql count(*) very slow on big table
SELECT
T . ID,
T . DATE,
T . TIME,
T .i_want,
T .i_want_date,
T .i_want_date_to,
T . ACTION,
T .country,
T .url,
T .user_agent,
(
SELECT
client. NAME
FROM
client client
...
1
vote
0answers
76 views
nested views much slower in Pg 9.3.4 than 8.4.8
We recently upgraded from Pg 8.4 to 9.3 and soon noticed that nested views which ran in a few seconds in 8.4 take 100 times longer or never complete w/in a reasonable (10 min) amount of time in 9.3.
...
1
vote
0answers
183 views
ipython notebook and psycopg2 don't work together
I am new with using mac os, maybe thats why i got such troubles.
So firstly I found out problem with running ipython notebook and successfully solved it with this two commands in shell (thx to ...
1
vote
0answers
192 views
getting error function lo_manage() does not exist
Currently i am using PostgreSQL Plus Advance Server 9.3 and i just created a table which has a column of blob type and i am using oid data type to store blob value.
Now i want to create a BEFORE ...
1
vote
0answers
51 views
PostgreSQL Trigger insert records to MS SQL Server
I have to solve a little big problem. I will try to show you all information so you can help me.
Amazon Server #1: ubuntu-raring-13.04-amd64-server with postgreSQL DB version 9.3
Amazon Server #2: ...
1
vote
0answers
50 views
Nearest non adjacent point
There is a point column in a table. I need to add another point. The new point can not be nearer than a certain distance from the existing points. The distance is given by the <@> operator of ...
0
votes
0answers
8 views
“Abstract” table in PostgreSQL (inheritance)
There are a lot of PostgreSQL inheritance questions on SO, but I wanted to be clear about my particular case. Suppose I have the following tables:
CREATE TABLE abstract_person (
id bigint PRIMARY ...
0
votes
0answers
26 views
PostgreSQL Slow Ordering And Count
I have 5 million data in my postgres.
When i want to order and count my data it can take long time for ordering.
Count Query Explain
SELECT COUNT(wp.*) AS count FROM wp as wp
LEFT JOIN wp_template ...
0
votes
0answers
4 views
PostgreSQL pg_upgrade --link mode performance issue after upgrade
Trying to do PostgreSQL Upgrade from 9.0 to 9.3 latest version. Using pg_uprade --link mode, since the downtime is very minimal compare to other options like pg_dumpall and pg_upgrade with normal ...
0
votes
0answers
8 views
How to use dblink with pgadmin3?
How can i install dblink extension and use it via pgadmin3 on windows?
All tutorials are showing how to do it on linux. I didt found a good one to install and use dblink with postgreSql 9.3 on ...
0
votes
0answers
19 views
Master slave delay(activate synchronous commit) and pg_xlog in a separate disk
We are preparing to add repmgr to an existant Postgresql 9.3 two nodes streaming replication cluster.
Previously we had a serious problem that affected our main business, the problem was the delay ...
0
votes
0answers
13 views
Intermittent state of executing multiple queries using a Statement object
I am executing multiple SQL statements using single Connection and Statement object as two processes.
Pre-process (Preprocess.java)
Post-process (Postprocess.java)
My Process steps are as follows,
...
0
votes
0answers
17 views
How I can specify sqoop export columns in target db?
I would like to populate a postgres table from an avro file using sqoop (2) export, but i dont have id field in the source and that should be populated automatically (serial type) but i am getting an ...
0
votes
0answers
21 views
Postgres Materialized View update every 1 minute?
I have a postgres View which is a join of almost 3-4 tables. Even after adding indexes I find the query to be slow on 100k rows.
I am thinking of changing it to a materialized view which is updated ...
0
votes
0answers
14 views
django-pgjson, how to check for the existence of a key
Using django 1.7, django-pgjson, and postgres 9.3, how can I query for the existence of a key?
Say I have the model Woot with the field somefield, and I want to check for the existence of somekey. ...
0
votes
0answers
62 views
Not reading ENV variables in database.yml (Rails 4.2.0, RubyMine 7, Postgres, Ruby 2.2.0, DotEnv)
Edit: I solved the first part of the issue. Apparently in Rails 4.2, the load order is different (Dotenv used to be loaded right after class Application < Rails::Application in application.rb, as ...
0
votes
0answers
15 views
How to Import PGSQL roles into Amazon RDS?
I am trying to migrate data from a stand-alone PostgreSQL set-up into AWS RDS. I have dumps of all the databases ready to be imported but I need their associated roles first.
What is the correct way ...
0
votes
0answers
59 views
PostgreSQL 9.3: Dynamic pivot table for huge records
I want to display a pivot table for the following data. I have a table with the 10000 records.
Table: Mat_test
CREATE TABLE Mat_test AS
SELECT DISTINCT 1 + floor(random() * 1500000)::integer AS id,
...
0
votes
0answers
30 views
MiniExifTool - Errno::ENAMETOOLONG - Error in prod only
I am getting an error while trying to save a manually geotaggued picture using the miniexiftool gem. It works fine on my local machine though. This is the error I am getting:
Errno::ENAMETOOLONG ...
0
votes
0answers
11 views
Free Space After PostgreSQL Vacuum Not Showing In Server
I just ran pg_repack on a PostgreSQL production database which freed up 14 gigs of space, but the disk still shows the old space being used.
I'm using \l+ to check the database size -- this is where ...
0
votes
0answers
16 views
Any problems with partitioning and dynamic SQL for DML?
Postgresql docs on partitioning recommend to modify tables via nested ifs in a trigger:
IF ( NEW.logdate >= DATE '2006-02-01' AND
NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO ...
0
votes
0answers
16 views
What is a difference between <-> and <@> operators when comparing Points?
SELECT
point(45,45) <-> point(46,46) AS val1
,point(45,45) <@> point(46,46) AS val2
and result:
val1: 1.4142135623731
val2: 84.3733913253679
I know that <@> returns distance ...
0
votes
0answers
21 views
How I can “atomically” replace function that is executed by trigger?
Here is code I have used to create FUNCTION and TRIGGER.
CREATE OR REPLACE FUNCTION inc_post_loves() RETURNS TRIGGER AS
$BODY$
BEGIN;
...
END;
$BODY$
language plpgsql;
CREATE TRIGGER ...
0
votes
0answers
38 views
Compare psql execute speed
I want to compare execution speed of psycopg2 with psql. I currently have a script that looks like
SQL="SOME LONG SQL QUERY"
time psql -h $HOST -d $DB -U $USER -p $PORT -c "EXPLAIN ANALYZE $SQL"
...
0
votes
0answers
29 views
Count Performance in Postgresql 9.3
I'm using a select count(*) on a table with 800k rows. The table has id column as the primary key. The results take about 800ms. Is there a way to get better performance? I have given the explain ...
0
votes
0answers
15 views
lag window function returning incorrect results
I have a table with a variety of date stamped test results, some of which are stored in json format (natively in the database). I'm attempting to use some window functions to pull out specific data ...
0
votes
0answers
37 views
SQLAlchemy json postgresql
I'am using SQLAlchemy in pyramid project with PostgreSQL9.3 database, and I have the trouble:
I want to store some data in table with JSON field, but I can't select data with filter by the key in ...
0
votes
0answers
12 views
dynamic set columns for crostab
In my database I store the configuration to connect to different kind of API's.
I retrieve the data-source parameters with an crosstab query
But I want to create a dynamic function where I can fill in ...
