All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
0answers
6 views

PostGis - How to handle exception using function?

I am using this code to update a table and when there is any exception it should handle it, So how to do it ?? UPDATE Schema.Table1 SET Column3 = ...
0
votes
1answer
18 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
0
votes
0answers
7 views

Restoring postgres backup with WAL-E fills up disk

I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a ...
0
votes
0answers
15 views

Postgresql restore database from file encoding error

I have problem while i import database from backup I gives many output and many "invalid command" messages. In the end it gives an encoding error. what can the reason be? my command: sudo -u ...
1
vote
1answer
32 views

Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)

I have two points and I have to calculate difference between them. I using below query in SQL Server. SELECT geography::Point(27.185425, 88.124582, 4326) ...
0
votes
0answers
16 views

Group st_size by account_id - PostgreSQL 9.2

I've got the following query: CREATE TABLE junk.results AS WITH accounts AS ( SELECT id FROM public.ja_clients WHERE NOT ( (last_sub_pay > EXTRACT('epoch' FROM now() - ...
2
votes
0answers
30 views

Slow Postgresql query with left join

I have a query on Postgres, and I also have added proper index. Is there anything missing here? SELECT orders.*, demo.name as d_name FROM orders LEFT JOIN users as demo ON demo.id = orders.dr_id ...
0
votes
2answers
20 views

Unique pair checking query improving in pivot table

First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill. I have seen an example online. So I have a ...
4
votes
1answer
53 views

Is it possible to create a dynamic join?

I'm using PostgreSQL if it matters. Assuming these two tables: id | run ----+------- 1 | run_1 2 | run_2 3 | run_3 and sample | elapsed | run -------+---------+------- samp1 | 1:09 ...
1
vote
2answers
13 views

Invoke --data-checksums using pg_createcluster on Ubuntu 14.04

How do you pass the initdb argument for data checksums to pg_createcluster when creating the initial cluster? Passing the argument as per initdb fails for me: pg_createcluster --data-checksums ...
0
votes
1answer
24 views

How does Checkpoint know which point to recover from in PostgreSQL?

I'm trying to better understand Checkpoint internals, specifically in PostgreSQL. My current understanding is that Crash Recovery will always start from the latest REDO location that is marked when a ...
3
votes
2answers
33 views

Count rows with max per group and additional condition

I have the following table: horse_main_id | race_id | horse_name | rating100 | race_result ---------------+---------+-------------------+-----------+------------- 23 | 159 | ...
-5
votes
0answers
37 views

PostgreSQL community channels other than mailing lists and IRC? [on hold]

Is there an active channel for the PostgreSQL community other than the official mailing lists and IRC? I'd hope to find a forum-like channel. This is the 21st century after all...
0
votes
1answer
21 views

PostgreSQL9.6 Beta1:new setting remote_apply for configuration parameter synchronous_commit

PostgreSQL9.6 add new setting remote_apply for configuration parameter synchronous_commit, Reading the following document I still don't understand the difference between on the setting on and ...
0
votes
1answer
65 views

How to optimize query with order by

Have query which generate OpenERP ORM. Table have 100k rows. SELECT "tbl".id FROM "tbl" WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ORDER BY ...
0
votes
1answer
31 views

PostgreSQL values to unique dictionary

I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long ...
2
votes
0answers
39 views

Insertion into postgres database stops (hangs) after table reaches a certain size without error

We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table ...
1
vote
1answer
26 views

When PostgreSQL will use Index Only Scan over Bitmap Index + Heap Scan

Today I've been doing some reading on the difference between the two and I think I've got a reasonable idea of each. Index Only: Accesses heap pages as it moves through the index, possibly access the ...
1
vote
2answers
29 views

Scalable way to track who's viewed a post? [on hold]

I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly ...
0
votes
2answers
43 views

CASE with max()

I know how to do this in SQL Server but Postgres is different. I want to return the max employee pay. Select b.enumber, b.bday, case when Max(c.id) then c.pay ELSE c.pay End As "Current Pay" From ...
0
votes
1answer
33 views

Unable to backup postgresql database. pg_dump memory alloc error

After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished. The very first thing I attempt to do is to ...
0
votes
1answer
33 views

Get A Count From Table

I want to see all fsid limit 25 from my table -- I tried this query Select * from btmt WHERE Count(fsid) > 1 limit 24 But this throws an error of Aggregates are not allowed in WHERE ...
1
vote
1answer
29 views

Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan?

I have a table items with an indexed column type_id. The table contains about 80 million rows. At the moment the first 5 million (in order of insertion) and the last 3 million have type_id = 8, and ...
0
votes
0answers
36 views

Postgres query taking 1minute to fetch one row

I am using postgres version 9.4 in my application. I have a partitioned table named sql_sent_sms which is partitioned on the basis of primary keys. I have a query which uses join on same table with ...
0
votes
1answer
20 views

Different behaviour between oracle and postgres regarding alter table primary key command

In Oracle 11g, when you write alter table abc add constraint abc_pk primary key (one, two, three); Automatically an index for one, two, three is added. In PostgreSQL 9.5, when you do the same, ...
0
votes
0answers
36 views

Connecting Oracle 11g to Postgres via dblink

I have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow. I've seen ...
4
votes
3answers
46 views

What is the easiest way to backup PostgreSQL and send backups to Google Drive?

I am new with PostgreSQL, So my question is: what is the easiest way to make regular backups and send them to Google Drive. Perhaps, there is a tool that can make it or something else.
1
vote
1answer
32 views

PostgreSQL won't allow remote connections

My Postgres installation won't allow me to connect remotely. I set the listen_addresses field in the postgresql.conf to '*' and added the line host all all 0.0.0.0/0 ...
3
votes
1answer
32 views

Postgres: Preventing objects with same name

I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. ...
1
vote
0answers
14 views

How can I upgrade PostgreSQL while using PgQ queue system?

I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then: sudo ...
2
votes
2answers
50 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
1
vote
1answer
24 views

Storing last N samples in a table coming from multiple clients

I want to store some last N (say fixed 50) samples in a table indexed by uuid. They're coming from multiple clients. The requirements of which last N are weak (the use case is to show some recent ...
2
votes
1answer
11 views

In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row?

I have a table with the following schema: CREATE TABLE foo ( data TEXT NOT NULL, count BIGINT NOT NULL DEFAULT 1, PRIMARY KEY (data) ); I'd like to get the relative rank of a single entry, ...
0
votes
0answers
23 views

improve performance with schema design, a lot join

I'm making a custom cms system for my client, with postgresql, nodejs ... have problem with slow performance issue, when doing select data . (and it is still on localhost/mac nginx and just few data, ...
0
votes
0answers
4 views

Save relation into postgres function variable to use in CORR() call?

I have two tables, queries and signalsand I want to create a Postgres function to calculate the CORR() for all signals, given a query. I'm trying to simply call CORR() with two variables in the ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
0
votes
0answers
27 views

Is there any way to reduce pg_largeobject size?

The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject. I could remove loid which ...
3
votes
1answer
31 views

Postgres database deadlock issue

I am using Cloudera Manager to monitor and configure Hadoop. I choose PostgreSQL as metastore of HUE - a web portal to submit your SQL to Hadoop. When I submit a SQL, the HUE says the database is ...
1
vote
0answers
15 views

How do I configure Postgresql stop words to reduce to -ism and -ist?

I want to reduce communism and communist to the same stop words. Currently, that's not the way it's working. # select to_tsvector('communism communist'); to_tsvector ...
2
votes
2answers
25 views

Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server. More specific details: ...
0
votes
0answers
13 views

Diagram conventions and standards for Database modeling pgmodeler [duplicate]

I am using pgmodeler for data modeling in postgres.. Apart from naming conventions are there any other standards for drawing the diagram... Like UML modeling
1
vote
1answer
23 views

array_length if array is empty behaviour

Please explain to me this behavior. This SQL finds elements where ids (type is array::bigint) is empty. SELECT * FROM rises WHERE ids = '{}' -- finds, e.g., 9 rows This SQL finds no rows: SELECT ...
4
votes
2answers
67 views

Select tables on column entry in schema (with many tables)

I have a schema in a PostgreSQL-DB that includes approx. 3000 tables with each having one row/ entry (size, id, geom). I'd like to select all tables which have a size value lower than 20. How can I ...
3
votes
1answer
40 views

Converting SQL SERVER Procedure to POSTGRESQL

I have procedure in SQL Server to print all players of given country. I try to convert it into plpgsql, but I get errors. SQL Server Procedure CREATE PROCEDURE kraj_pilkarze @kraj varchar(30) AS ...
1
vote
0answers
22 views

optimize a window aggregate function

First, the query SELECT tc.numreg, com.numdep, tc.numcom, tc.numiris, sum(tc.conso_climat_reel)/sum(sum(tc.conso_climat_reel)) OVER (PARTITION BY tc.numreg) AS ratio, ...
1
vote
0answers
27 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
0
votes
0answers
11 views

Different insert operations with the same data and cache

I want to perform several different insert operations into a Postgres database. PostgreSQL Version 9.5. Operating system: SLES 12. I have 200000 rows of data. It is the same for every different ...
3
votes
0answers
44 views

Postgresql Filter Slows Query

I've got a query like this: SELECT count(*) FROM acs2014_5yr.geoheader as g JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract) JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id) ...
2
votes
1answer
13 views

Backup a hot standby database (streaming replication) whilst it is in recovery

Synchronous Streaming Replicating is set up between a master and a hot standby database. It seems like a good idea to perform daily backups on the standby database to avoid any performance issues ...
0
votes
0answers
5 views

Export data from Hive to Postgres

I would like to save data that I fetch via Hive on a remote Hadoop cluster to my local machine in a Postgres database. I am currently downloading the data as files but I have no idea on how to ...