All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
0answers
9 views
Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range
I am building a database in PostgreSQL for financial data, where the table looks like this:
create table fin_data(
brokerid text,
stock int,
holding bigint,
stake float,
value ...
0
votes
0answers
13 views
index being used during equality operator but not comparison operator
Using a btree as my index. From what I understand(http://www.postgresql.org/docs/9.0/static/indexes-types.html), btree works for comparison operators. For equality operator it works fine
explain ...
1
vote
3answers
20 views
Copy Indexes From One Table to Another in PostgreSQL
I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
1
vote
0answers
26 views
Replicating two PostgreSQL databases without using master db
I'm trying to create a simplest possible db cluster for my application and i'm not sure if that's the best way to achieve that so i'm gonna try to explain my situation the best i can.
Im basically ...
0
votes
0answers
25 views
Is there replication by “pushing” instead of “pulling”?
Here is the situation:
pg 9.4
pg instance "A" in intranet which is often loosing connection with
WAN because of satellite problems (primary). This server has dynamic public ip.
pg instance "B" ...
0
votes
0answers
21 views
What is the best recent referential SQL book that covers PostgreSQL & MySQL for an already C programmer? [on hold]
I am a C programmer & I am targeting SQL for PostgreSQL & MySQL
I know just the basics about SQL (SELECT, INSERT, CREATE, UPDATE, DELETE).
I need to know all about SQL.
So I am looking for a ...
1
vote
1answer
92 views
return subquery value into main query
I have the following query:
SELECT *
FROM item_ledger
WHERE to_id='7'
AND (SELECT SUM(qty) AS qty_in
FROM item_ledger
WHERE from_item_ledger_id='5') < intransit_qty
the query ...
1
vote
0answers
17 views
Why does SemiJoin node receive so bad statistic even after runnning ANALYZE
PosgtreSQL 9.4
I have two tables:
CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer );
CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL ...
0
votes
1answer
18 views
Unused Postgres sequence values
Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
1
vote
1answer
22 views
How to verify that a PostgreSQL base + WAL backup has been restored correctly
Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various ...
0
votes
2answers
48 views
PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?
I have the following PostgreSQL schema:
CREATE TABLE User (
ID INTEGER PRIMARY KEY
);
CREATE TABLE BOX (
ID INTEGER PRIMARY KEY
);
CREATE SEQUENCE seq_item;
CREATE TABLE Item (
ID ...
0
votes
1answer
17 views
What are nonremovable row versions and how to remove them?
I did full vacuum on postgresql-9.4 database and I have a lot of tables with nonremovable row versions. I would like to know what are these or where I can read about them.
INFO: vacuuming ...
0
votes
1answer
14 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
36 views
Preserve order of array elements after join
I have a query that returns a CTE looking like
+-----------+-------------+
| node_id | ancestors |
|-----------+-------------|
| 1 | [] |
| 2 | [] |
| 3 ...
-1
votes
0answers
24 views
Create tables for subtypes in PostgreSQL? [on hold]
After spending much time googling around, I still can't seem to find instructions on how to create tables for subtypes in PostgreSQL.
Let's say for example that I have the following relational ...
0
votes
0answers
15 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
47 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
23 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 ...
1
vote
2answers
43 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, ...
3
votes
1answer
44 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
13 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
0answers
12 views
Postgresql - Total Runtime or cost? [on hold]
In postgresql 9.2, if the cache is setted which query below can be approved ?
First query:
SELECT ... ,
not exists ( Select 1 From bigtable Where a.result = result_a or a.result => result_b ) ...
0
votes
1answer
16 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 ...
0
votes
0answers
21 views
How to calculate the EOD balance in SQL? [on hold]
Hi i am trying to calculate the end of the day balance in postgres sql , Please find the below sample. From the transactional data i have
done the summation and arrived on finding the ...
-2
votes
0answers
24 views
Epoch Date Conversion [on hold]
I'm trying to get the epoch date for the time zone adjusted time for the day. For example, if the date PST right now is 10/28/15 21:54:00. The current GMT time is 10/29/15 05:54:00. I would like ...
-3
votes
0answers
40 views
Very slow running queries [closed]
I have a database with millions of records and multiple tables. The problem is that there are very complicated queries developed that include multiple joins and are overall very slow in terms of ...
2
votes
0answers
19 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 ...
0
votes
0answers
18 views
Retrieve correct epoch date from postgres time zone adjusted [on hold]
What I am trying to do is the following:
Get the epoch day (10/15/15) from the current time on the server and time zone adjust it.
Firstly, our server is on GMT time. If the current date in GMT is ...
1
vote
1answer
39 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
47 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 ...
1
vote
1answer
18 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 ...
4
votes
1answer
293 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
19 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
26 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
18 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
21 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
29 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
27 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 ...
0
votes
0answers
16 views
How array unnset treats NULL values in an array?
I am trying to achieve something like LEFT JOIN with array UNNEST function - I want query to return row with null value if array is empty. Therefore, by using CASE WHEN, I wanted to pass fake array ...
0
votes
1answer
25 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
35 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 ...
0
votes
0answers
12 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
17 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
9 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
16 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 ...
-2
votes
1answer
20 views
how do I create many tables from many text file with headers posgresql [closed]
how do I create many tables from many text file with headers with all columns data type varchar (max) in posgressql Thanks so much
0
votes
0answers
34 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
33 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
18 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
16 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 ...
