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

learn more… | top users | synonyms (1)

3
votes
5answers
468 views

Find all values not present for other values of other column

Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data: a | b ------- 1 | 2 1 | 3 1 | 4 1 | 5 2 | 1 2 | 3 2 | 4 3 | 1 3 | 3 Now I need to ...
2
votes
1answer
160 views

Joining timestamped records with the most-recent-prior records from another timestamped table

Say I have 2 tables: Table: sales timestamp timestamp without time zone amount numeric Table: prices timestamp timestamp without time zone price numeric How can I get a ...
1
vote
1answer
875 views

Postgresql connection refused

I'm trying to remotely connect to my postgresql database. I have two virtual machines set up both running ubuntu 14.04. I am trying to connect to the second vm using the first vm using psql -h ...
0
votes
0answers
57 views

Which benchmark assess string pattern matching in a database?

I have a postgresql DB in which I do a lot of string matching ala : UPDATE table1 SET colum1 = TRUE WHERE colum2 LIKE '%teststring%'; colum2 is a whole html file As I said I do A LOT of this and ...
3
votes
1answer
823 views

timeline 2 of the primary does not match recovery target timeline 1

I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this: .-----. .--------. | | W | DB | | APP ...
1
vote
2answers
320 views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
3
votes
3answers
728 views

How to ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table: | id | version | | 1 | 0.9 | | 2 | 1.0 | | 3 | 0.9.1 | | 4 | 1.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | How do I produce this output? | id | ...
0
votes
1answer
286 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
2
votes
1answer
320 views

Searching a big table for a few results without scanning the whole table

I need to search a very big PostgreSQL table (500+M rows), I want to limit the returned search results, but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?) ...
0
votes
1answer
380 views

Postgresql tries to connect to fe80::1, permission denied

I upgraded a couple of days ago to IPv6 on my local test machine and since then I've been experiencing some issue with connecting to my local PostgreSQL server. I set the host to 'localhost', which ...
1
vote
1answer
123 views

Update different columns on different clauses

I have a database table in Postgres 9.3 with the following layout: id SERIAL, col1 INT, col2 INT Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases ...
0
votes
0answers
271 views

cascade delete doesn't work even though constraint is added

I created the following table: CREATE TABLE message_link ( id serial NOT NULL, thread_id integer, message_id integer, user_id integer, read boolean NOT NULL, CONSTRAINT message_link_pkey ...
0
votes
1answer
254 views

PostgreSQL synchronous replication time out?

I'm using PostgreSQL 9.3 with synchronous replication. I have 2 synchronous replicators and 1 async replicator. My problem is when 2 synchronous replicators are down, transaction will wait ...
1
vote
1answer
2k views

Grant usage partially on schema to user on Postgres

I granted INSERT in a specific table for one user. The problem is that I need to also grant USAGE in the schema for this same user, but granting USAGE in schema also turn all relations in that schema ...
0
votes
0answers
50 views

Which primary keys must be specified when using slony PostgreSQL replication?

I'm extremely green when it comes to databases, so forgive my ignorance. I've been researching ways to replicate our production Postgres DB, and I've decided to try out Slony. Since the server is ...
1
vote
1answer
1k views

PostgreSQL Slave has more files in pg_xlog than /wal_archive

Background: for various reasons I am switching PostgreSQL Slave from a Streaming replication to a warm standby replication. The streaming replication was setup using this guide: ...
1
vote
1answer
118 views

How to optimize multiple ORDER BYs?

I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column. This is for a dynamic pagination page where the query is constructed on demand, and the application caches ...
9
votes
1answer
441 views

Busy table is not getting vacuumed

We're using Postgres 9.2 on Windows to store low-frequency timeseries data: we're inserting around 2000 rows per second every second 24 hours, 7 days a week with no downtime. There is a DELETE that ...
0
votes
2answers
101 views

How can I make this query more efficient?

This is the query: SELECT races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at FROM races LEFT JOIN (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...
2
votes
1answer
59 views

Understanding pg-pool in details

I have following problem regarding the pg-pool (3.3.3) When master fails, automatic failover happens and standby is promoted to master. But this new master don't have any replicators. Other slave ...
1
vote
1answer
112 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
500 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
202 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
165 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
220 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
651 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
179 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
94 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
73 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
286 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
791 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
504 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
245 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
205 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
107 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
188 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
141 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
537 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
163 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
779 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
706 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
494 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
60 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, ...