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

learn more… | top users | synonyms (1)

1
vote
1answer
105 views

index using btree_gin on smallint/bigint

I try to use a btree_gin index on smallint column, but the index is never used for queries. According to documentation (http://www.postgresql.org/docs/9.5/static/btree-gin.html) it should work. If I ...
0
votes
0answers
368 views

errors install and start postgresql-9.5

UPDATE Why I run "sudo apt-get purge postgresql-9.4 postgresql-client-9.5 postgresql-client-common postgresql-common" and everything is OK??? My ubuntu is 12.04.1. Does it matter with this? I ...
5
votes
1answer
849 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
1
vote
1answer
107 views

Good encryption performance

I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow. I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table). ...
3
votes
2answers
37 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
2answers
67 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
0
votes
2answers
278 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
0
votes
1answer
29 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 ...
2
votes
2answers
3k views

Assign values with the := operator

When I assign a variable with result := title || '', by '' || author; it is taking more time (approx. 15 sec) to run the function. However, when I assign the variable with result = title || '', by ...
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
26 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 ...
1
vote
1answer
28 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 ...
2
votes
0answers
40 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 ...
4
votes
1answer
147 views

Upgrade from Postgres 9.3 to 9.5

I’m attempting my first upgrade of Postgres from 9.3 to 9.5. I’ve run into two roadblocks but not sure how to get around them. I have my 9.3 PGDATA set to /data/postgres, which is a separate ...
-5
votes
0answers
37 views

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

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
2answers
44 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
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 ...
1
vote
2answers
30 views

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

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
1answer
183 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...
1
vote
2answers
58 views

Trigram index for for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
0
votes
1answer
35 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 ...
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 ...
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
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 ...
14
votes
3answers
1k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
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 ...
4
votes
2answers
69 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 ...
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
41 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 ...
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 ...
0
votes
1answer
81 views

API on top of postgresql

I'm looking for one or several API's that can be put on top of postgres to make data input more dynamic. What I'm looking for is something that can handle a wider range of protocols and differently ...
4
votes
1answer
145 views

Problems while building plpgsql_check on Windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, ...
2
votes
1answer
197 views

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic ...
4
votes
3answers
48 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.
3
votes
1answer
42 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
1answer
450 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
10
votes
4answers
16k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
1
vote
0answers
15 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 ...
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 ...
2
votes
2answers
51 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
139 views

PostgreSQL: Installing a contrib extension on windows

I am trying to install this Temporal Tables extension into my localhost installation of PostgreSQL. (pg9.4, windows 7) I have figured out I needed to put the files into the 9.4/share/contrib folder ...
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, ...
1
vote
2answers
6 views

How to restore postgres database which is created using pg_dumpall?

I have dumped all of my databases using pg_dumpall and now I want to restore them. I used the command psql -f db.out postgres, but I am still not able to see my databases. Can anyone help?
2
votes
2answers
28 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
24 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). ...
1
vote
1answer
131 views

PostgreSQL 8.4 replication of more than 100 tables

I am facing a problem with PostgreSQL version 8.4. I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to ...
3
votes
1answer
32 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 ...