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

how to update the value of a period contained inside a bigger range

I have a tsrange like '[2010-01-01 14:00, 2010-01-01 15:00]',20 And want to update the 20 value to 15 in a range inside that, like '[2010-01-01 14:10, 2010-01-01 14:30]',15 My expected outcome ...
3
votes
1answer
479 views

PostgreSQL: Trigger for updating view doesn't work

I'm trying to make the trigger for updating a view work. If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run. CREATE OR REPLACE FUNCTION test_trigger_function() ...
10
votes
2answers
4k views

PostgreSQL: schema diff/patch tool [closed]

Consider the following setting: A production DB A dev db, on which schema changes are made to enable new features When the development of a new feature is complete, I have to manually update the ...
0
votes
1answer
200 views

Does PostgreSQL take time to start streaming replication after restart master?

I set streaming replication in postgresql 9.3. I have one master and 3 slaves and replication works fine. But when i restart the master server and login using postgres database, pg_stat_replication ...
0
votes
1answer
154 views

Unmount/detach/deactivate PostgreSQL database

I manage a server that serves several web applications that use PostgreSQL as a data store. Each web application has a separate role and database, and each database is stored exclusively on a ...
4
votes
1answer
2k views

PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration)

I am trying to understand best uses of PostgreSQL replication and how it works so I can troubleshoot in a production environment. I am having a hard time understanding the differences between these 2 ...
1
vote
1answer
203 views

Optimizing bulk update performance in Postgresql with dependencies

Basically my question is the same as this one, but WITH dependencies, so drop/renaming the table is not a trivial option (I assume). We are refactoring a large, poorly designed table which has many ...
1
vote
1answer
618 views

PosgreSQL: very slow select on a single table with no joins

I'm trying to help a colleague who's having some problems with a PostgreSQL query. We've narrowed it down to a join to one table making the query painfully slow and, upon investigation, we found that ...
3
votes
1answer
43 views

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

I'm using PostgreSQL 9.3.5. Suppose I have the following table: CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point); I then insert a row, leaving pt NULL: INSERT INTO point_test ...
0
votes
1answer
172 views

Connect Jira to Postgresql with Pgpool-II

I gotta problem with connecting Jira 6 to Pgpool-II which is responsible for load balancing and replication in Postgresql bases(2 instances). Pgpool is started on port 9999, I just want to connect ...
1
vote
0answers
93 views

Importing PostgreSQL backup into slightly different database

I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to ...
0
votes
1answer
68 views

Migrating a database from one PostgreSQL server to another cleanly

I have a Rails app server that uses Postgres for the DB backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the ...
0
votes
0answers
72 views

Bulk update performance

I have a several process that aggregate some statistics on items (how much was shown, clicked ... around 30 factors). Once in a 5 minutes i flush the aggregate data to postgres (9.1). I have 250K ...
1
vote
1answer
277 views

Paritioning performance for full table scan on non-paritioned index

We have a table in Postgres that we are considering partitioning. We occasionally query an indexed field that is separate from one with the partition key. My question is: Can partitioning the table ...
2
votes
2answers
773 views

SELECT UNION from two views of the same table

(Sorry the the vague title, if anyone want to re-edit, be my guest!) I have these tables and views for a factory inventory module : +----------------------------+ +--------------------------+ | ...
0
votes
2answers
46 views

assign selected columns to a variable

Select sum(column_a) as a, sum(column_b) as b round(a / b) From table Group by column In the example above I am summing two column, and I would like to use those 2 results in calculating ...
0
votes
2answers
494 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
1answer
230 views

How do I list all databases and tables using psql in a pipable format? [duplicate]

This question deals with getting a list. So I get this: $ psql --list List of databases Name | Owner | Encoding | Collate | Ctype | ...
2
votes
1answer
182 views

Does PostgreSQL allow multiple grantees in GRANT commands?

Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone. Isn't possible to do something like GRANT SELECT ...
1
vote
0answers
106 views

upgrading from Postgresql 9.1 to 9.3 on Ubuntu using Londiste

I am undertaking a project to upgrade our Postgresql production database from 9.1 to 9.3 using Londiste. The reason I choose Londiste is to avoid substantial downtime from restoring the dump file or ...
0
votes
1answer
1k views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
2
votes
1answer
181 views

How to determine if postgres database contains GiST indexes and what type?

Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?
4
votes
1answer
138 views

Postgresql query plan differs with limit value making the query slower for lower limits

I have this query: SELECT "table_1".* FROM "table_1" WHERE "table_1"."deleted_at" IS NULL AND "table_1"."table_2_id" = 15797104 ORDER BY "table_1"."id" ASC LIMIT 1 The explain for the query ...
0
votes
1answer
504 views

Restoring pg_dump hangs

Aim: backup a database and restore it in database2 pg_dump A PG Dump has been created by issuing the following command: c:\Temp>pg_dump -U postgres postgres > backupTest2.sql Opening ...
0
votes
1answer
147 views

ERROR: recursive reference to query “najdi_tot” must not appear within its non-recursive term

I have a trouble with the following CTE query: WITH RECURSIVE najdi_tot(sj) AS ( SELECT nad FROM s_vztah WHERE pod=581 UNION SELECT pod FROM s_vztah WHERE nad=581 UNION SELECT ...
24
votes
2answers
777 views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
1
vote
1answer
673 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
0
votes
1answer
445 views

How can I time SQL-queries using psql from command line?

I am sorry, I cannot comment, so I am posting a new question. There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run ...
1
vote
1answer
58 views

How to apply a newly-created Domain to an existing Column?

In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other. CREATE DOMAIN ...
6
votes
2answers
2k views

Pagination with PostgreSQL 9.3: counting number of pages

I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id. -- index create index category_score_id on products(category, score, ...
4
votes
1answer
2k views

Is there a way to replicate Postgres databases in Amazon RDS between regions?

We have a Postgres RDS instance in the AWS Oregon Region. For some supposed legal reasons, we have to host our client's European data in the Ireland AWS Region, but we need that data synchronized to ...
2
votes
1answer
329 views

PGBouncer Pause Waits for Remote Connection to Close

I'm testing the pgbouncer "pause" feature to minimize database downtime when a database restart is required. For this test, I have a remote psql session connected to my database through pgbouncer. ...
2
votes
1answer
469 views

Can't list users - “select * from user” returns only one row with one column

I have a heroku postgres add-on database with a user table. However, when I attempt to select * from user, all I get is: current_user ---------------- rtsjlhdfptlaqd (1 row) The table name is ...
1
vote
1answer
450 views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
3
votes
1answer
8k views

Efficient way to insert/update/delete table records from complex query in Postgres 9.x

I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day. My initial approach was just clear data from my table and ...
1
vote
1answer
478 views

PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver

I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema: CREATE TABLE "entities" ( "id" BIGSERIAL, "type" INT ...
2
votes
2answers
1k views

Why can a new user select from any table?

When I create a user in Postgres, create user test with password 'test'; The user ends up being able to connect to every database in the instance. The user can also select from any table: # psql ...
3
votes
3answers
10k views

Trying to connect to PostgreSQL with Entity Framework via Npgsql and C#

So, I'm new to databases and only have done a little with PostgreSQL before. I have C# (visual studio 2012) and have downloaded the Entity framework (6). I have also downloaded the latest Npgsql data ...
2
votes
1answer
213 views

PostgreSQL equivalent of mysqld --bootstrap or mysqld --init-file

I'm trying to automate initializing a postgresql data directory, with a user and a default database pre-created. I know I can do this by running postgresql backgrounded and using createdb and ...
0
votes
1answer
63 views

Can a connection's prepared statements be altered by Postgres's admin functionality? [closed]

I am trying to tamper proof as best as possible a peer to peer application. I am concerned that the prepared statements will be altered. Can the Postgres admin alter a connection's prepared ...
0
votes
1answer
160 views

Postgres-9.1 having more Idle connections

The connections were created 2 days before. Can I kill the process manually by using the below query? select pg_terminate_backend(procpid) from pg_stat_activity where datname = 'mydb' and procpid ...
5
votes
1answer
318 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
3
votes
1answer
1k views

Problems installing Postgis extension on Ubuntu 14.04

I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple: sudo apt-get install postgresql-contrib ...
1
vote
1answer
338 views

What is the impact of building Postgres with --disable-thread-safety option?

I am a newbie in the database field. Recently I have been trying to install Postgres 9.3.4 on AIX 6.1. When I was building the code with the configure command, I ran into this error: checking thread ...
3
votes
1answer
430 views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
3
votes
1answer
46 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
2
votes
2answers
1k views

starting pgbouncer on startup

I installed pgbouncer and configured it and it starts only via using this command pgbouncer /etc/pgbouncer/pgbouncer.ini however using service pgbouncer start it dose not start. I then set up ...
2
votes
1answer
515 views

Replicate single table from Postgres 9.3 replica

I have an existing Postgres 9.3 server acting as a read-only replica using the built-in streaming replication. I need to replicate a single table from a database on that server to another physical ...
7
votes
2answers
301 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
2
votes
1answer
115 views

PL/pgSQL function or rCTE to detect depth of relations between two tables

I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...