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
47 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 ...
3
votes
1answer
55 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 ...
1
vote
2answers
55 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
19 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
52 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 ...
2
votes
1answer
106 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
522 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
139 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
181 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
208 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
63 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
67 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, t3....
2
votes
2answers
105 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
47 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
97 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
158 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
110 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
18 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 most-...
1
vote
0answers
30 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: 1011192|1|1.2|0.34|44.8|0.3|57.4|0....
3
votes
1answer
112 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
100 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
67 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 (...
2
votes
1answer
106 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
45 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
296 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
117 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
200 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
157 views

Foreign key for array column [duplicate]

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

Creating index using dynamic SQL [closed]

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
52 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 ...
2
votes
3answers
973 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
40 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
64 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
121 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
55 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
434 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
148 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
33 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
56 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
15 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
114 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
60 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
32 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 ...
0
votes
0answers
29 views

Does JSONB column type in export look identical to JSON?

In other words, does it export as text or as a binary blob. I'm running pg_dump. I'd love to see the text version of the JSON data, not some kind of encoding of a binary blob.
2
votes
0answers
158 views

Server Doesn't Listen Error when connecting to PostgreSQL from Client

I have done a lot of research through the Google and have not been able to come up with a solution. I installed PostgreSQL 9.5 and PostGIS 2.1.8 On Ubunutu Server 14.04 and configured PostgreSQL as ...
3
votes
1answer
41 views

Queries show data but not column names

I'm getting through the postgresql tutorial, I've created my db, 2 tables, weather and cities and added a few lines of data. When I query my db with this sql statement for example: SELECT city, ...
2
votes
1answer
44 views

What is preventing DROP TABLE from removing a Postgres table? [duplicate]

I have a postgres database named aarons_db in ubuntu linux. I'm trying to delete a table called pnt within aarons_db. To do this, I am using the following command: DROP TABLE pnt Unfortunately, ...
5
votes
2answers
365 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
2
votes
2answers
121 views

PostgreSQL Server: Many-to-One replication / Transactional Replication

I have several PostgreSQL DBs in different geographical locations (local sites). Each local site DB have the same schema, but unique data. For example, Take a table with columns: Site_ID, ...
1
vote
1answer
134 views

Postgres/pgloader: setting a delimiter for multiple line text records

I wish to import a 5 gigabyte text file with pgloader. The individual records are paragraphs of html. The records include carriage returns and white space. So this not an import where it is one ...