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

learn more… | top users | synonyms (1)

0
votes
0answers
16 views

cannot call json_array_elements on a scalar [closed]

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"}, ...
1
vote
1answer
41 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 ...
2
votes
0answers
25 views

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

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. ...
2
votes
3answers
49 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: ...
1
vote
0answers
13 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
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 ...
7
votes
2answers
114 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. ...
2
votes
2answers
56 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), ...
0
votes
0answers
31 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
13 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. ...
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
0answers
11 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 ...
1
vote
1answer
47 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
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, ...
0
votes
1answer
22 views

Using a view to count specific values of a column [closed]

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
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. ...
1
vote
1answer
27 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
34 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. ...
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 ...
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
0answers
39 views

The definitive fastest radius search query with Postgis 2.0

I come from mysql world. I read the manual and saw some examples, after that, I'm not able to say witch method is the fastest for a radius search. Nobody seems to agrees. My simple case: I need to ...
1
vote
0answers
48 views

PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume

We have a problem in upgrading postgresql 9.2 cluster to 9.5 using pg_upgradecluster, because the data directory is on a different volume from the root one. At the beginning, we just tried: # ...
0
votes
0answers
12 views

Prevent Update/Insert/Delete based on Date

I have a simple Table that holds financial data with a date. I would like to endure, that historic values are protected to INSERT, UPDATE or DELETE. Example: All values of March 2016 should not be ...
0
votes
0answers
14 views

Diagnosing why postgresql query is going in a seemingly endless processing loop

I maintain a social forum built with Django and postgresql 9.3.10. There's a maintenance query I run via psql, to clean up my postgresql table. It goes like this: begin; DELETE FROM links_seen WHERE ...
0
votes
1answer
48 views

How to convert postgresql dump file into mysql dump file

I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance
1
vote
1answer
19 views

Top N Results Ordered by Joined Table Column

I'm trying to optimize a fairly simple query that just gets a list of the top N users ordered by the number of followers they have. I am running into performance issues when I run the query a certain ...
1
vote
1answer
26 views

Alias Column Name [closed]

I am trying to run a basic select Query in PostgreSQL, but I am having trouble using the column alias. I tried to create the alias as randomfield As [Space In Name] but it gave an error of Error: ...
2
votes
1answer
53 views

Aggregate count over variable number of json keys

How can one get a list of json objects like this: [ {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2}, {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1} ] from ...
1
vote
0answers
16 views

parallel pg_restore with materialized views

I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS. To troubleshoot the restore process, I have split the restore ...
0
votes
2answers
14 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 ...
1
vote
1answer
28 views

Trigger for a particular row?

I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like: CREATE ...
0
votes
1answer
35 views

Updating column from another table - PostgreSQL 9.2 [closed]

There are two tables: ja_jobs junk.test_test1 I need to update the time_job column from junk.test_test1 to ja_jobs. Here is what I'm doing now: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM ...
0
votes
1answer
12 views

select duplicated rows by modified_date - PGSQL 9.2

Query: SELECT j.id INTO junk.ja_jobs FROM ( SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id FROM ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
0
votes
0answers
78 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
1
vote
1answer
19 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and ...
2
votes
1answer
20 views

Multiple columns from one table rely on one single table

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables: The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
0
votes
1answer
26 views

Start, stop, configure PostgreSQL through Mac terminal

I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
1
vote
0answers
24 views

When was a table created? [duplicate]

Is there any way to find out when a table was created in PostgreSQL or Greenplum, other than finding the underlying table and looking at the OS creation timestamp?
0
votes
0answers
10 views

Remedying psycopg2:OperationalError in postgresql 9.3.10 database

I have a Django app + postgresql 9.3 DB, hosted on Ubuntu VM. Recently my VM ran out of disk space, resulting in no space left on device errors showing up in my logs. I cleared the space on the device ...
0
votes
0answers
5 views

Ubuntu: How to soft-link to the latest version of PostgreSQL CLI tools [duplicate]

I have just upgraded my RDS PostgreSQL version from 9.3 to 9.5. In order to access the server, I need to upgrade the Ubuntu CLI tools as well. I have upgraded the tools using the apt.postgresql.org ...
0
votes
1answer
10 views

Postgresql error: failed to re-find parent key in index

I haven't seen this before and am stumped. I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently I VM ran out of disk space (resulting in no space left on device ...
1
vote
1answer
12 views

Delete duplicated rows by modified_date - PostgreSQL 9.2

I've got some duplicated rows on a table called ja_jobs: To find those duplicated data, I'm running this Query: select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted ...
0
votes
0answers
19 views

How can I restrict users to see only tables they can SELECT from

I have a redshift database with many schemas and tables. I created a group of users and granted them with permissions to view (select) tables in some particular schema: CREATE GROUP data_viewers; ...
0
votes
0answers
10 views

postGIS out-of-db still has error when environment file is updated

I'm running postGIS 2.1 with a PostgreSQL 9.1.21 and am trying to get rasters back out of a database and am running into the error: ERROR: rt_raster_load_offline_data: Access to offline bands ...
2
votes
1answer
14 views

PostgreSQL read-only user for pg_dumpall

I'm running a PostgreSQL instance which is used by several related applications, each of which has its own database. For backups I want to create snapshots of the complete data in the instance and I ...
1
vote
1answer
18 views

How to make trigger function to make daily entering-exiting report?

I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in. CREATE TABLE daily_report ( id serial, ...
0
votes
0answers
34 views

Improving query performance involving similarity and full-text search

I have been trying to improving the performance of this query involving similarity and to_tsquery. I have a table (440,000 rows, PostgreSQL 9.5.2) with the following characteristics: test_db=> \d ...
0
votes
1answer
15 views

Schema size [PG TOAST] - PostgreSQL 9.2

I've got the following query to measure the schema's size on my DB: WITH schema_size AS ( SELECT tab.table_catalog AS database_name, tab.table_schema AS schema_name, tab.table_name, ...