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

learn more… | top users | synonyms (1)

15
votes
4answers
24k views

tuning postgresql for large amounts of ram

I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm ...
2
votes
2answers
47 views

Filtering UNION ALL result is much slower than filtering each subquery

(edit: see end for a simpler example) I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
2
votes
1answer
11 views

Constraint on row value based on values in other rows

Is it possible to have a table with four columns: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3) ); and then ...
1
vote
1answer
38 views

Can the subquery be simplified/optimized?

Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the ARRAY function; but the ARRAY ...
0
votes
1answer
15 views

pg_stat_activity shows lots of easy queries running for minutes

After application server start its connection pool fills quickly and application can't work properly. I went through pg_stat_activity and noticed lots of queries running for minutes: But postgres's ...
0
votes
0answers
3 views

How can I clear my database on Heroku?

I have an application that I deployed to Heroku. Everytime I try to sign-up, it gives me and error that tells me my password is invalid. I've type in everything correctly and I'm sure I've never ...
0
votes
0answers
2 views

Postgres Array into Geoserver

I'm working with OS Highways data, which when loaded into Postgres using ogr2ogr has a few columns that are set as arrays. Geoserver does not like this, so I need to transform the arrays into a simple ...
0
votes
2answers
211 views

PostgreSQL pg_upgrade --link mode performance issue after upgrade

Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using ...
0
votes
1answer
206 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
0
votes
1answer
17 views

Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux. The command for the backup was: $ ...
1
vote
2answers
23 views

Security of streaming replication?

If I want to use streaming replication between two Postgresql databases on different parts of the internet, is there a way to secure or encrypt the connection between the two? Or are there reasons ...
0
votes
1answer
13 views

Effectively searching through entire 1 level nested JSONB

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
6
votes
1answer
206 views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
-1
votes
0answers
15 views

PostgreSQL Index [on hold]

Im using PostgreSQL and have a question about creating an index. I know that I have approximately 1 million entries it a table called 'item', 400,000 items in a table called 'recordings' and 100,000 ...
7
votes
2answers
96 views

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

forgive me if this has been asked and answered before. I'm roughing out a schema for an inventory management system, to be implemented in PostgreSQL. All of our products and services have a sku. ...
4
votes
1answer
592 views

Default value for UUID column in Postgres

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
1
vote
2answers
19 views

Postgres: set client encoding default

When querying from certain tables, I'll often receive the error: character with byte sequence 0xef 0xbf 0xbd in encoding "UTF8" has no equivalent in encoding "WIN1252" Varying bytes, but you get ...
0
votes
1answer
13 views

PostgreSQL, display role members

Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like role: council_stuff members: Harry Ben Steve Melinda
0
votes
1answer
18 views

Make a (copy of a) postgis database available to customer?

Our application uses a PostgreSQL database (with the PostGIS spatial extension). Now one of our customers has requested direct access to the database; because, as they claim, it’s their data. We’d ...
2
votes
3answers
43 views

Extract string in PostgreSQL 9.2

I got the following column: full_path "gorfs"."absolute_pathname" It keeps the path of a BLOB, like on the example below: Note that the 4865 is the account_id.The path is always like: ...
0
votes
0answers
14 views

cannot call json_array_elements on a scalar [on hold]

I have JSON values like this: [{"lang_id":179,"language_id":26,"phy_id":82,"language":"Duch"}, {"lang_id":177,"language_id":16,"phy_id":82,"language":"ENGLISH"}, ...
0
votes
1answer
14 views

Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True I've looked around and come up with this. Any help ...
1
vote
1answer
21 views

Versioning entities in db with draft option, select query

I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after ...
0
votes
1answer
125 views

Postgresql Streaming replication - monitoring

I have setup streaming replication for my postgres servers. I am using PostgreSQL 9.2.6. Replication seems to be working fine. I am monitoring this with two ways using nagios: Log_delay and ...
2
votes
0answers
25 views

What software or cloud service do you recommend PostgreSQL to get (more) interoperable with? [on hold]

We're trying to expand PostgreSQL ecosystem by developing features for interoperability in the PostgreSQL community, as well as asking software vendors and other OSS projects to support PostgreSQL. ...
1
vote
0answers
11 views

How to find the number of tuples that are vacuum-eligible?

I am trying to find the number of vacuum-eligible tuples in one of our postgres tables. This is the output of vacuum verbose on that table INFO: "act_msgs": found 0 removable, 2217083 nonremovable ...
0
votes
1answer
99 views

When does pg_dump not export CREATE EXTENSION?

I have a production database that I am trying to dump then load (in plain text format) locally. The problem is, the database being dumped uses the hstore extension in several tables, and contrary to ...
0
votes
0answers
29 views

Postgresql BDR problem compiling pluging

I'm trying to build deb packages for postgresql BDR plugin, but I'm getting the following error: gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels ...
2
votes
0answers
12 views

What do LC_TIME and LC_NUMERIC settings actually change in PostgreSQL?

According to the PostgreSQL 9.5 documentation, LC_TIME affects "Formatting of dates and times" and LC_NUMERIC - "Formatting of numbers", but I can't seem to see any difference from changing these. ...
5
votes
0answers
20 views

Writing a query that accepts multiple sets of values in postgresql

I am trying to write a query in postgres that accepts multiple sets of values. My knowledge of postgres is brief and this below query is the most complicated I have had to put together to finish off ...
0
votes
0answers
9 views

PostgreSQL (Sync Replication) and Pgpool Load Balancing

I am trying to configure pgpool as load balancer for synchronized replicated PostgreSQL servers. I configured PostgreSQL servers and they are running normally. But I want to send write queries to ...
0
votes
0answers
12 views

postgresql service fails to start after bad reboot

I have a postgresql 9.3 running on Ubuntu 14.04. For some reason, my fellow developer rebooted the server and since then postgresql service is failing to start. Also, the reboot didn't go smooth as it ...
1
vote
1answer
19 views

Is it possible to tell when databases, schemas and tables were created in Postgres?

Is it possible to tell when databases, schemas and tables were created in Postgres? I've serached through PgAdmin and tried the command line interface with no joy. Also can't find any reference to ...
26
votes
3answers
30k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
0
votes
1answer
28 views

Is a partition a relation?

Is a partition a relation in its own right, or is a partitioned table a single relation? Specifically, do we need to increase max_fsm_relations when we split tables up into loads of partitions?
5
votes
2answers
174 views

How to examine PostgreSQL server's SSL certificate?

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate? I'm hoping for output similar to what you ...
0
votes
2answers
12 views

Only describing postgres tables with “\d” (not sequences, indexes, views, …)

I want to describe every table in a postgres database. Currently I have this: psql -d db -c "\d *" > db_description.txt But this also includes all sequences, views, etc. Is there a command ...
0
votes
1answer
22 views

Using a view to count specific values of a column [on hold]

Hello I have created a DB with two tables on postgres, one called "aula" and other called "monitor", one of the rows of monitor it is a foreign key to the primary key of monitor, a foreign key called ...
1
vote
1answer
42 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
1
vote
1answer
32 views

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8. I have table like: CREATE TABLE foo ( foo_date timestamp without time zone NOT NULL, -- other columns, constraints ) This table contains about 4.000.000 rows. ...
4
votes
1answer
87 views

pgAdmin is extremely slow on any remote operation

I run this query from my local pgAdmin connecting remotely to our dev server: select * from users order by random() limit 1; it hangs for 17 seconds and shows Total query runtime: 148 ms. 1 row ...
1
vote
1answer
26 views

Get rows that have ALL join criteria

I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I ...
1
vote
1answer
15 views

PostgreSQL 9.3: Aggregate collection of JSON arrays

I have a table inventory with a value column that contains JSON strings, with the following data structure: { "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, ...
1
vote
0answers
33 views

Postgresql with just index storage

I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated. ...
0
votes
3answers
756 views

pgAgent Job's “last result” is continuously “running”

I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content ...
0
votes
2answers
189 views

300 inserts per minute, 6 million rows per month

I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot ...
0
votes
0answers
24 views

Function - PostgreSQL 9.2

On the table users there is a code column: code CHARACTER VARYING, The customer can add any value he wants into that column, but he also can chose by not adding any value. That column can't be ...
0
votes
0answers
22 views

PostgreSQL: Slow JOIN on three large tables

Edit: Proposed this question as a LEFT JOIN problem, but as @wildplasser pointed out, due to my WHERE clause I was actually doing a JOIN all along. Adjusting the question from LEFT JOIN to JOIN. The ...
1
vote
1answer
106 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
0
votes
0answers
16 views

How can I improve this nested postgresql query?

I have a mildly complex query that is having rather poor performance: UPDATE web_pages SET state = 'fetching' WHERE web_pages.id = ( SELECT web_pages.id FROM ...