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
1answer
101 views

Postgres Refresh Materialized View Locks

I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view. CREATE TABLE people ( name VARCHAR(30) ); INSERT INTO people VALUES ('Alice'), ('...
3
votes
1answer
87 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
0
votes
0answers
30 views

PGBouncer pausing hanging from ltm monitor

Currently I have the following setup: F5LTM --> PGBx2 --> DBx3 The PGBouncers are set up with priority groups on the F5 so in the event one fails or hangs traffic will still be sent to the database ...
4
votes
2answers
159 views

Best practice regarding concurrency for INSERT into a table with composite primary key?

Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / ...
0
votes
1answer
45 views

How to get first value from list of value

I have data as follows Id Col1 Col2 ================ 101 101 abc 101 102 def 102 102 ght 103 101 mgj 103 102 dkf 103 103 dfj I need output as follows Id Col1 ...
2
votes
2answers
67 views

Update a table after insertion to another table in PostgreSQL?

Let's say that my database has 2 tables (Person & Car): Person (personID, number_of_cars) Car (carID, owner) where the owner attribute in Car refers to the personID in the Person table. Now, ...
4
votes
1answer
148 views

How to avoid invoking functions twice when using GROUP BY and HAVING?

I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents. The following query works and returns the correct results: ...
0
votes
0answers
68 views

Postgresql configuration for tiny server and append-only data

Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware. It is: 512M memory 1 CPU My database works with these conditions: Data is never ...
0
votes
1answer
70 views

PostgreSQL-Backup from windows hard drive to linux

I'm in a bit of a pickle here. I managed to copy the whole postgresql installation directory to a backup drive but how would I retrieve it from Linux with the same architecture and version. Is this ...
2
votes
0answers
148 views

Data Corruption - ERROR: could not open segment 2 of relation

I am getting the below error for a production database. I am new to PostgreSQL databases. I need help to get this fixed. 2015-10-28 10:25:04,154 [monitor-thread-pool1-thread-6] ERROR [org.hibernate....
1
vote
1answer
73 views

Can sequential queries result in concurrent transactions?

I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed ...
1
vote
1answer
96 views

Postgres reindex on crash folklore

I'm struggling with a problem of folk memory re Postgres. The folklore I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered ...
2
votes
1answer
68 views

How to make unique check without creating heavy index in postgres

I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and ...
5
votes
1answer
2k views

Understanding “bitmap heap scan” and “bitmap index scan”

I'll try to explain my misunderstandings by the following example. I didn't understand fundamentals of the Bitmap Heap Scan Node. Consider the query SELECT customerid, username FROM customers WHERE ...
0
votes
1answer
26 views

How to read configuration option into variable? [duplicate]

I want to read configuration option as TEXT but SHOW statement is strange because it does not return any rows. create or replace function get_param() returns text as $$ begin return query show ...
0
votes
1answer
92 views

Database modelling : Creating a model for restaurant application which gives statistics

I am working on a server-side application in which users will be able to give ratings and comments to different factors of a 'Restaurant'. What the webapp does is takes all the ratings and creates ...
0
votes
1answer
35 views

How to create a dynamic comment to an object?

I want to embed variable information in a table that is created from a script. The following works: SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03'); But it seems I can't use it for ...
0
votes
0answers
38 views

limit aggregate function across join

I have a facts table, and each fact has one-or-more distributors in fact_distributors. A distributor is an entity, and for reporting purposes entities are grouped together into an entity_group via ...
2
votes
0answers
37 views

Postgresql on external HDD

I had Postgresql installed on a previous MacBook. However, something happened and now the Macbook won't boot up. The HDD was okay, so I converted it to an external HDD. I have another computer with ...
0
votes
1answer
246 views

Postgres restore unclear behaviour

I have following: 2 nodes one of which is current production db and another is for fail recovery. I made base backup on first node with following command pg_basebackup -h 127.0.0.1 -U postgres -D bckp ...
2
votes
1answer
229 views

How does unnest() treat NULL values and empty arrays?

I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
0
votes
1answer
200 views

psql: could not connect to server

when I'm executing U postgres -d "phoenix" -a -f "/northstar/phoenix/runtime/Daily-script/query.sql" this command i got the following errore psql: could not connect to server: No such file or ...
2
votes
1answer
470 views

How to recover Postgres replication slave after fatal error

On 4 Debian 8 Jessie servers, I have PostgreSQL 9.4.3 master + 3 slaves. After substantial data changes on master, slave logs showed this error: LOG: started streaming WAL from primary at 182/0 on ...
1
vote
1answer
582 views

Postgres function to merge two json objects with overlapping keys into one object

I've got the following json object: { "a" : { "0" : 2, "1" : 4, "3" : 6, } "b" : { "2" : 8, "1" : 10, /*note this key exists in "a" too*/ "...
0
votes
1answer
20 views

Independent serial for each foreign key value

I have a table shared by many different users called players and a table called users. Each player has the primary key id (serial) and a foreign key called owner_id which matches to users.id. In ...
0
votes
0answers
64 views

Filter on UUID data type in Navicat

I am a first-time user of Navicat for Postgres 11.1.15 on Mac OS X. Tried to filter a list of rows on a column named "uuid_" of type UUID. I pasted in a string of the hex representation of a UUID ...
0
votes
0answers
96 views

PostgreSQL streaming replication with slots works for one slave, but not for another slave

Master (10.0.0.1): Edit postgresql.conf listen_addresses = '*' wal_level = logical max_wal_senders = 3 max_replication_slots = 3 hot_standby = on Edit pg_hba.conf host replication ...
0
votes
0answers
55 views

What role for plpythonu function's in the file system?

As a preliminary test for a further work I'm trying to use a simple plpythonu function in Postgresql 9.2 to create a folder in my filesystem. So I have this code : CREATE OR REPLACE FUNCTION "...
0
votes
1answer
42 views

How to handle this complicated join condition(s)?

drop table if exists company_tags; drop table if exists project_tags; drop table if exists companies; drop table if exists projects; drop table if exists tags; create table companies (id serial ...
0
votes
0answers
157 views

Postgres user credentials for copying WAL files to a network storage in Windows

I have 3 postgres server setup with streaming replication - 1 primary and 2 secondary servers, one intended as a failover server and another as DR. I want to setup WAL archiving on to a network ...
1
vote
1answer
221 views

Where can I see the exact row count of a table in pgAdmin III?

Where can I see the exact row count of a table in pgAdmin III? I know where to find the approximate row count: I am also aware I can SELECT COUNT(*) FROM my_table. But I want to know whether the ...
0
votes
2answers
189 views

SELECT 1 - idle in transaction

Across my problems with the load on the database, I just have found a new, odd one. I understand, that some queries might be in IDLE IN TRANSACTION status, when I did something wrong in my application ...
0
votes
1answer
126 views

XML load fails on postgresql 9.1, but not in 9.3, any workaround?

I have a problem loading an xml on an xml field of a table. The load is done by ruby code, but this is not the point. Everything worked well for years, but xml is increasing size.... So one day, ...
3
votes
1answer
106 views

Need for recursive lookup, stuck in query design

In PostgreSQL 9.4, here is my table: with r(pk, userid,partneruserid) as (values (1,1,2), (2,1,3), (3,1,6), (4,2,5), (5,2,6), (6,3,1), (7,4,1), (8,5,3), (9,6,2), (10,6,3) ) select * from r; In a ...
4
votes
1answer
105 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
0
votes
1answer
56 views

Why is this PostgreSQL query table scanning

I am running PostgreSQL 9.3. The database is of all of the medical NPIs (national provider numbers). I have created the following indexes: CREATE INDEX ...
1
vote
1answer
43 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
1
vote
1answer
46 views

Create a quick report for a client

I have an Amazon RDS (postgres) database and an client asked me to have access to certain data stored in this database preferably in the form of a comma separated file. If the data changes, the client ...
1
vote
1answer
55 views

What is the valid string representation of tstzrange[]?

I am trying to find string representation of tstzrange[] type (array of timestamp ranges): select '{[2014-01-01,2015-01-01]}'::tstzrange[]; select '{''[2014-01-01,2015-01-01]''}'::tstzrange[]; select ...
2
votes
2answers
118 views

Union tables by mask in PostgreSQL [closed]

In database I have many tables like with names: "t_2015_09_01_z15" "t_2015_09_02_z15" "t_2015_09_03_z15" "t_2015_09_04_z15" "t_2015_09_05_z15" ... "t_2015_10_01_z15" "t_2015_10_02_z15" "...
1
vote
1answer
103 views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
5
votes
1answer
126 views

Fastest query for distinct IDs in a many-to-many relationship

I have this table in PostgreSQL 9.4: CREATE TABLE user_operations( id SERIAL PRIMARY KEY, operation_id integer, user_id integer ) The table consists of ~1000-2000 different ...
2
votes
1answer
32 views

Implications of many materialized views in Postgres?

I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be ...
1
vote
1answer
204 views

PostgreSQL: Installing a contrib extension on windows

I am trying to install this Temporal Tables extension into my localhost installation of PostgreSQL. (pg9.4, windows 7) I have figured out I needed to put the files into the 9.4/share/contrib folder (...
-2
votes
1answer
788 views

Error subquery uses ungrouped (columns correlation)

Recently I tried to create a query to obtain some statistical data, but I got this error: subquery uses ungrouped column "vegetacionpotencial.ident" from outer query select veg_pot , sum(...
0
votes
1answer
39 views

Using default values in primary key

Good day. I have a table like this: CREATE TABLE mytable ( myname varchar(30), mydate date DEFAULT CURRENT_DATE, PRIMARY KEY(myname, mydate) ); EDIT: Forgot to mention I am using a ...
0
votes
1answer
68 views

JSON array as text to join in Postgres?

I have a table t1 with a TEXT column (t1textcolumn) that looks like: [ {"id":"x.f","bar":""}, {"id":"f.foo","bar":""}, {"id":"f.bar","bar":""} ] This array's "id" elements have a relation to ...
1
vote
1answer
50 views

Postgres 9.3 copy data from another table with relation [closed]

Hi I have two tables in my database which is related, which is forms and qa_forms. forms ->id ->name -address -phone -calldatetime and then ` qa_forms ->id ->forms_id ->name ->...
1
vote
0answers
60 views

How to check the program/applicationname that triggered function?

There are two applications that used the same function (trigger) in postgresql. I have to ignore notification by applicatonname. How do I identify the current program/application that called the ...
1
vote
0answers
120 views

PostgreSQL DML statements

I am trying to trick an application into executing a DML statement AFTER starting a SELECT statement without using stacked queries so I cannot use the ; character anywhere in my query. I have resorted ...