PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
1answer
16 views

Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

I have a table market_trades with a timestamp column: api_production=# \d market_trades; Table "public.market_trades" Column | Type ...
0
votes
1answer
12 views

postgresql - Indexing into two “buckets”

I'm creating a database with PostgreSQL that has an integer field called "delay". All of my queries on this column will be checking if the value of this column is less than or equal to 0, or greater ...
2
votes
3answers
57 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
1
vote
1answer
14 views

PGAdmin III Server Status Error: Could not read directory “pg_log” No such file or directory

Running PostgreSQL 9.3 on Ubuntu 14.04 and pgAdmin III 1.18.1 on Windows 7. I tried Tools->Server Status and I get an error: "Could not read directory "pg_log" No such file or directory" I ...
0
votes
1answer
14 views

How to access pg_settings from separate connection

I am trying to access the pg_settings table from a separate connection belonging to a legacy application. I know how to identify the connection's procid from pg_stat_activity, but I am unsure how to ...
1
vote
0answers
31 views

How to measure bandwidth requirements for MySQL, Postgres, …?

TLTR: how to configure MySQL/Postgres to get a fair measurement of the I/O requirments for analytical queries? Hello, for certain database and especially database hardware experiments for want to ...
2
votes
1answer
41 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
0
votes
1answer
34 views

How to create a Postgres trigger to ensure field is non-updateable

I'm trying to create a Postgres trigger to ensure after a column's value has been set, it cannot be updated (basically make it readonly). So far I've come up with the following draft trigger. 1) ...
1
vote
1answer
8 views

Create user to monitor database activity on Postgres

Postgres 9.3 Debian 7 I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity. I created the user and it can select from ...
2
votes
2answers
32 views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
0
votes
1answer
15 views

Need some help with this query or some ideas for change it to better model

I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can ...
3
votes
5answers
88 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 ...
1
vote
1answer
23 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 ...
0
votes
0answers
13 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
21 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
78 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 ...
2
votes
3answers
450 views

How do I 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
17 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
72 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
0answers
19 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
43 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
38 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
20 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
15 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
15 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
26 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
53 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 ...
7
votes
0answers
95 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
0answers
14 views

Understanding correct procedure to recover master in master-slave replication in PostgreSQL

For learning, I have setup master-slave streaming synchronous replication. Pg-pool 3.3.3 is installed in one VM and it connect to 4 VMs which runs PostgreSQL 9.3, one master and 3 slaves. Replication ...
0
votes
2answers
69 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 ...
1
vote
0answers
19 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
38 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
33 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() ...
0
votes
1answer
38 views

PostgreSQL: schema diff/patch tool

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
11 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
17 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 ...
0
votes
1answer
18 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
21 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
52 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 ...
0
votes
0answers
22 views

Matching array values in PostgreSQL with ActiveRecord

In my Rails 4 app I have a goal to see all contacts, where field visible_to in contacts table equal to 1. My visible_to is :integer, array: true. However, I get the following exception: ...
3
votes
1answer
24 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
22 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
24 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
10 views

Migrating a database from one PostgresSQL 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
30 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
30 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
140 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
26 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
43 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
31 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 | ...