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
2answers
46 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
107 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
38 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
14 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
48 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
60 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
39 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
28 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
55 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
51 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
47 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
35 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
89 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
39 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
32 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
27 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
92 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
39 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
30 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
42 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 ...
0
votes
0answers
22 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
91 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
34 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
35 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
342 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
71 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
36 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 ...
0
votes
2answers
49 views

should I denormalize for user profiles?

Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing ...
1
vote
0answers
45 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
6
votes
2answers
87 views

Which executes first? Trigger or rule?

Using PostgreSQL. Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true. My question is: will the rule prevent the trigger ...
3
votes
2answers
43 views

Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do: I have a table, fields: id SERIAL inet INET ports integer[] id | inet | ports ----+------------+------------ 2 | 1.2.2.1 ...
4
votes
1answer
32 views

Dynamic form builder database optimization Postgresql

Hi I have a project that create forms dynamically I use a model like the following: forms ----- id (PK) name (other fields) form_fields ------------- id (PK) form_id (FK to forms.id) ...
0
votes
0answers
16 views

Best approach to maintain a clone of a db in Postgres RDS

We'd like to maintain a separate database for our dev environment that closely resembles the current state of our production database. What is the best way to do this in RDS? Our current process is to ...
1
vote
1answer
26 views

Postgres Access Roles

I'm trying to get my head around postgres roles. I want to create a database and have the owner role have all privileges on the database. I then want another role which cannot create tables but able ...
0
votes
0answers
25 views

Does Postgresql have a transaction log?

My question is pretty simple; does postgresql have something similar to the transaction log in SQL Server? If so, how do you access it to truncate dead transactions, or set what size it should be? ...
0
votes
1answer
66 views

how can we merge sum and count? [closed]

My result of the request of group by is : ref state quantity total PO3253 assinged 300.0 323400 PO3253 done 300.0 323400 When I delete state from ...
1
vote
1answer
28 views

What determines whether `pg_dump` includes a varchar length?

I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns ...
0
votes
1answer
51 views

pgbouncer 1.7 with TLS/SSL client and server connections

With settings borrowed from a similar server running Postgres 9.4.1 & pgbouncer 1.6.1, I have multiple users connecting to the database through pgbouncer on port 6543. I also have a second server ...
1
vote
2answers
46 views

What is table bloating in databases

Can someone explain what bloating means in database terms. For example what does it mean to say an index is bloated. I tried searching for it but there is no explanation of what bloating is . Only ...
3
votes
3answers
76 views

SELECT + INSERT or INSERT alone

I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second. The first time a ...
1
vote
1answer
37 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do ...