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
31 views

Postgres 9.5 foreign table inheritance not using indexes

In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
0
votes
1answer
21 views

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

For some reason queries on the high range of are extremely slow and queries on the low range are extremely fast. My index is an adapted version of the answer in Optimizing queries on a range of ...
0
votes
1answer
61 views

Why are correlated subqueries sometimes faster than joins in Postgres?

This relates to the dataset described in Postgres is performing sequential scan instead of index scan I've started working on adapting the import logic to work with a more normalised schema – no ...
-1
votes
1answer
18 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
5
votes
1answer
63 views

How to improve this union to ensure consistent results?

I've been trying to find an reliable and economic way of counting rows over a 7 day period and so far, this is the best I've been able to come up with: Schema CREATE TABLE IF NOT EXISTS "data" ( ...
0
votes
0answers
12 views

Query Postgres table by Block Range Index (BRIN) identifier directly

I have N client machines. I want to load each of machine with distinct partition of BRIN index. That requires to: create BRIN with predefined number of partitions - equal to number of client ...
-1
votes
0answers
23 views

Change date OUTPUT to DMY format permanently on Postgres [duplicate]

Is there a way to change permanently the date output format on postgres to DMY? I have changed datestyle setting in postgresql.conf which will allow me to insert dates as DMY format but when I query I ...
3
votes
1answer
40 views

What are the limits of Postgresql's large object facility?

I've not found an equivalent of the about PostgreSQL page that covers the limitations of the large object facility in particular, but reading between the lines I think the following is true: No more ...
0
votes
0answers
14 views

Deadlock on Postgres 9.3 Sharelock executing same function at same time

Postgres 9.3 Debian 7 I'm developing a gambling website (in alfa now) where you bet credits (I call coins) in card games. There's an unique pg function to add/subtract user coins that updates a ...
1
vote
2answers
48 views

Optimising a query to get rows where x is between pairs of values from another table

PostgreSQL 9.4.5 In my database there are 2 tables, vcfentries and exons. I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the ...
0
votes
0answers
15 views

Difference between execution times [duplicate]

I managed to make a more efficient my query disabling the sequential scan, but the time in the explain analyze is really different from the real execution of the query, "Execution time: 4854.186 ms" ...
2
votes
2answers
32 views

In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?

I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose ...
1
vote
1answer
18 views

Postgres query to return JSON object keys as array

Is it possible to return a JSON object keys as an array of values in PostgreSQL? In JavaScript, this would simply be Object.keys(obj), which returns an array of strings. For example, if I have a ...
4
votes
3answers
420 views

Avoid double query on the same table

I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table. Query 1: SELECT ...
1
vote
1answer
45 views

How to use SSL/TLS between server and client with Postgres?

I don't know much with it comes to security. I'm deploying a virtual machine with my database in a cloud service. I would like to encrypt all the communication between this server and any clients. How ...
2
votes
1answer
62 views

PostgreSQL 9.4 query seems very slow

I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem ...
2
votes
4answers
122 views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
1
vote
2answers
43 views

Postgresql: Sortable index on array values

Suppose the following table: CREATE TABLE foo (id serial, category int, bar int[]); INSERT INTO foo VALUES (1, 1, '{4,10,20}'); INSERT INTO foo VALUES (2, 1, '{1,8,9}'); We want to query sorted ...
0
votes
3answers
48 views

PostgreSQL - Simple column binding

I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk. My SELECT is of the form: SELECT t1.id, t1.value, t2.value, ...
2
votes
2answers
66 views

Retrieving changed rows only from a table with 10^7 rows

I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well. I've ...
2
votes
1answer
24 views

Postgres soft delete partition, what to do with Foreign keys?

I have a table A that has a couple of other tables (B, C) attached to it (many rows in B and C for one row in A). I'm having performance problems with A, so I thought I would partition it using a ...
1
vote
2answers
43 views

SELECT…FOR UPDATE - Will only an UPDATE release the lock?

I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement. I may or may not need to execute the update depending on the results of the SELECT. If I execute a rs.close(); or pStmt.close(); without ...
4
votes
1answer
103 views

PostgreSQL seems to create inefficient plans in simple conditional joins

Consider these two queries: SELECT t1.id, * FROM t1 INNER JOIN t2 ON t1.id = t2.id where t1.id > -9223372036513411363; And: SELECT t1.id, * FROM t1 INNER JOIN t2 ON ...
2
votes
1answer
37 views

Is there some kind of table-level replication in PostgreSQL?

I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from ...
0
votes
0answers
12 views

The most-recent-record problem with partitioned tables

Today's variant on the most-recent-record problem involves a table partitioned on the timestamp field relevant to the most-recent-record selection, and efficiently joining the result of the ...
1
vote
0answers
26 views

What controls the precision when writing reals in postgres copy command?

I do the following copy command from psql: copy yy_caa_epdall to '/tmp/CAAImport-1234567' delimiter '|' csv header; and get a file with lines like the following: ...
3
votes
1answer
47 views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
2
votes
1answer
59 views

PostgreSQL not using index during count(*)

I have a COUNT(*) query in PostgreSQL that runs often, and looks like: SELECT COUNT(*) FROM customer WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); This query ...
3
votes
1answer
28 views

How to paste chinese characters in psql terminal?

I am trying to paste chinese characters into psql terminal: $ psql => \encoding => UTF8 # Ctrl+v (艺术与设计) => ?????设计 When pasting into normal terminal (Max OS) it works fine: $ # Ctrl+v ...
1
vote
1answer
60 views

Select ONE most recent post for each author

I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry. I use recent versions of PostgreSQL and MySQL. I have 2 tables: CREATE ...
0
votes
0answers
41 views

Reducing the time when retrieving all records from large databases

I have to retrieve most of the records from large table, I'm talking about approximately 3 millions rows. I'm even using this site as a guide for tuning the database. It is not enough, it takes an ...
1
vote
1answer
32 views

When can I clean up archived WAL files?

I need some help understanding the how archived WAL files are used and when I can clean them up. Currently we have a master database replicating to a slave, but every so often our master database ...
3
votes
1answer
24 views

Specifying a username while running initdb causes error in log on start of PostgreSQL process

I have been forced to use a different default username for PostgreSQL. Before you ask: no, this is not something I can avoid. It's dumb, I know. Let's move on: In order to do this in an existing Cent ...
4
votes
1answer
47 views

PL/pgSQL issues when function used twice (caching problem ?)

I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem. I have this function: CREATE FUNCTION sp_connect(mail character varying, passwd character ...
2
votes
1answer
34 views

Foreign key for array column

I have a table: CREATE TABLE methods ( method_id serial PRIMARY KEY, method_name varchar(100) ); I want now to create a table with the following columns: CREATE TABLE ...
0
votes
1answer
45 views

Creating index using dynamic SQL [on hold]

I have following code for creating gist index CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx ON web_stats_project_107 USING gist (properties) WHERE properties @> ...
3
votes
2answers
33 views

Dealing with partitioned tables in Postgresql

Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided ...
1
vote
1answer
75 views

“using stale statistics instead of current ones because stats collector is not responding”

My postgresql 9.5 logs shows every minute the message: using stale statistics instead of current ones because stats collector is not responding Except a post on serverfault which is not helpfull ...
0
votes
0answers
39 views

date/time field value out of range: “21/1/2016” [duplicate]

My PostgreSQL database version is PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit In both databases, datestyle is set to"ISO, DMY". ATTEMPT 1 ...
1
vote
2answers
36 views

Postgres tablespace size is bigger than database size

PostgreSQL tablespace size is bigger than database size. What is the problem about that? SELECT pg_size_pretty(pg_database_size('ibidev')); pg_size_pretty ---------------- 2578 MB (1 row) SELECT ...
1
vote
1answer
29 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 ...
1
vote
0answers
25 views

Update column type when materialized views depend on the column?

I'm working in Postgres 9.4. I want to alter the type of a column in a table: ALTER TABLE mytable ALTER COLUMN quantity TYPE integer; But I get this: ERROR: cannot alter type of a column used by ...
0
votes
0answers
84 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 ...
4
votes
1answer
36 views

What does “Heap Block” in “Bitmap Heap Scan” mean?

I have this query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test WHERE timestamp_range @> '2015-01-22 23:00:00'::timestamp AND data_int_array @> '{49, 61}'::integer[]; Which outputs: ...
0
votes
0answers
14 views

PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables ...
0
votes
1answer
29 views

Postgresql cannot drop trigger [closed]

I'm running the following with alembic: def downgrade(): conn = op.get_bind() conn.execute(sa.sql.text(''' DROP TRIGGER update_rated_trigger ON rating; DROP FUNCTION update_rated; ''')) ...
0
votes
0answers
11 views

Relationship based access control (ReBAC)

In my application, access control is based on relationships between entities. Consider this simplified schema: CREATE TABLE user (...); CREATE TABLE group (...); CREATE TABLE group_members( ...
0
votes
1answer
39 views

After renaming postgres user, getting “role 'postgres' does not exist” in logs on PostgreSQL startup

For security reasons, we've been asked to rename our postgres user in our database. We've made the change in our staging environment and bounced PostgreSQL. We're now seeing an error in the log on ...
0
votes
0answers
29 views

Postgresql Encryption by middleware

I am trying to encrypt a Postgresql database to fit some new requirements. The applications accessing this database are already in production and I would prefer to not make changes to them. I have ...
0
votes
1answer
22 views

PostgreSQL: access right problems

I have a "production" user and database on which I want to give right access on all the users belonging to a production group. Unfortunately, I don't succeed, keeping the access denied error. Can ...