PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

4
votes
1answer
26 views

Dynamic casting from text within plpgsql

Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query ...
0
votes
0answers
45 views

Indexes are not created exactly as like mysql

I have connected ofbiz with postgres. It will create tables automatically. But when i compare mysql and postgres tables, i can see that some indexes are missing in postgres. If i migrate it from mysql ...
1
vote
2answers
15 views

Amazon RDS Postgresql adding new extensions

I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature ...
4
votes
1answer
181 views

How to model complex systems like networks in Postgres

I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented ...
0
votes
1answer
11 views

postGIS schema qualifier - best practice

There are a number of cases where the EXTENSION for postGIS is created iwth a schema qualifier. CREATE SCHEMA postgis; CREATE EXTENSION PostGIS WITH SCHEMA postgis; why is that? what are the ...
3
votes
1answer
53 views

Step through multiple arrays in parallel with FOREACH

Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like: for(int i = 0 ; i < array1.length ; i++){ ...
1
vote
0answers
16 views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
2
votes
1answer
32 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
4
votes
1answer
45 views

How to deduplicate SELECT statements in relational division?

I have a query that does a lot of duplicate work: SELECT visitor_id, '1'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value ...
1
vote
1answer
21 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
1
vote
0answers
40 views

Splitting logging into a separate database

In a postgresql database for a asp.net mvc website I have 3 tables (visits, uservisits, hits) that have update/insert query's running on them on every pageview. Rows in visits (for all visitors) and ...
2
votes
0answers
34 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
2
votes
1answer
87 views

PostgreSQL doesn't use all the RAM it could

I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config: # <snip> the default config ...
2
votes
1answer
64 views

How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?

If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work? I know that the database will need to store a ...
1
vote
1answer
37 views

PostgreSQL “size of temporary files”

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
4
votes
1answer
59 views

Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately?

The PostgreSQL 9.4 documentation states that adding the NOWAIT option to a SELECT FOR UPDATE means an error is generated when a row cannot be locked: To prevent the operation from waiting for ...
3
votes
1answer
34 views

PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity

I am investigating high load on a PostgreSQL database on RDS. As a part of the investigation, I'm running the following query: SELECT now()-query_start, state, query FROM pg_stat_activity ORDER BY 1 ...
1
vote
1answer
26 views

Postgres data integration

Consider a scenario where I have 3 standalone running Postgres engines that are installed on three separate machines. The data on this machine is inserted, updated or removed each month by employees. ...
2
votes
1answer
29 views

Adding new null column to table caused lock

Yesterday we ran a schema migration on our production database that we thought would be safe, but ran into an issue. It seems as though adding the new column caused a lock and our application could ...
0
votes
1answer
13 views

Any gotchas with changing IP address of Postgres Master?

We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated ...
1
vote
1answer
36 views

postgresql join - too long when no results found

I have two tables: controller_monitor and controller_monitor_reading. controller_monitor Column | Type | Modifiers ...
4
votes
1answer
76 views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
0
votes
1answer
25 views

How to get postgres to resume processes after it hangs when the RAID drive hosting the tablespace times out

Nearly once per day I'm getting a timeout that shows up in console like: 7:1:36 PM kernel: Areca RAID volume scsi command timeout (target=0 lun=0) which corresponds exactly to when all postgres ...
0
votes
1answer
15 views

PostgreSQL: Find rows for which any index of any value contained is over a given size

I have a database originally built with an instance of PostgreSQL with a custom page size much larger than the default. I'm trying to migrate its contents to a standard, 8k-page database. Because ...
1
vote
1answer
24 views

Comparing the data in two Postgres databases

I am looking for a way to compare the data in two Postgres databases. Basically a 'Before' and 'After' snapshot of a transaction being posted. What I am looking for is all the tables/records that ...
1
vote
0answers
17 views

Use pg_dump with postgis extensions?

I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION): $ pg_dump prescribing -U prescribing -h ...
0
votes
0answers
15 views

no pg_hba.conf entry for host “10.1.20.46”, user “redruby”, database “production”, SSL off

Two days ago I was able to ssh onto an utility server and perform some direct work on a production database. Yesterday, I received the error above in the title. What reason could this error have ...
3
votes
1answer
58 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
3
votes
1answer
64 views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON ...
0
votes
1answer
17 views

PgTap, unit test a foreign key is deferrable

I am using PgTap to unit test a Postgres (v9.4) database structure in a continuous migration process. It works great. Here is my question: « Is there a way to control that a foreign key constraint is ...
2
votes
2answers
251 views

Numeric vs Integer for a column - size and performance

I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer. The integer can be up to 6 digits, i.e. 0-999,999 , no negatives. ...
9
votes
1answer
35 views

When are privileges listed in \l and when not?

When are access privileges listed by \l, and when are they not? The access privileges listed by \l can change after a grant and revoke: $ createuser -EP my_readonly $ psql development development=# ...
3
votes
1answer
33 views

Pagination - Text comparison with greater than and less than with DESC

I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
1
vote
0answers
13 views

To minimize Cache misses in PostgreSQL?

You can calculate cache misses as described here. However, I am interested in how to minimize the phenomenon. I have some algorithms that are based on hash tables, which cause much cache misses by ...
4
votes
1answer
48 views

moving large PostgreSQL/PostGIS database

I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1). The upgrade process is well-documented. The ...
0
votes
1answer
21 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
6
votes
2answers
54 views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
1
vote
2answers
50 views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
0
votes
2answers
53 views

PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the ...
0
votes
0answers
16 views

Application uses all pgpool children up

I have few applications written in Java EE. They connect to PostgreSQL server through pgpool. PostgreSQL consists from master and slave. Datasources in my application server (Wildfly) establish ...
1
vote
1answer
27 views

Can't create user in postgres

When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ...
1
vote
0answers
24 views

SymmetricDS scheduled sync

I am trying to configure SymmetricDS to run a scheduled replication from one postgres database to another. I've seen people suggesting doing that by setting these fields in node config: ...
5
votes
2answers
70 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
6
votes
3answers
117 views

Can PostgreSQL use nulls in its indexes?

I've been reading this book which says that The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this ...
2
votes
2answers
23 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
1
vote
2answers
55 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
2
votes
1answer
39 views

Uniqueness constraint with date range

Consider a prices table with these columns: id (primary key) product_id (foreign key) start_date (date, non-null) end_date (date, non-null) quantity (integer) price (numeric) I'd like the ...
8
votes
2answers
177 views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
1
vote
1answer
30 views

Debugging Postgres error: “could not send data to client: Broken pipe”

I'm running a Django 1.8 site, served by nginx and Gunicorn, with a Postgres 9.4 backend. When I make lots of data-intensive calls to the site's API in succession, I've noticed about 1 in 100 ...
1
vote
1answer
162 views

PostgreSQL: How to get a sum of values and the total rows that went into each value? [closed]

I have a table, MyData in the following format: |id|theme_id|priority_id|value| I am trying to get the following result for all the rows: theme_id | priority_id | total | sum ...