All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
0answers
6 views
Recovery From Failure in DBMS
we consider a recovery from failure on running in the following way:
initially log analysis allows to determine the dirty pages
The second phase is to redo all operations validated before failure ...
1
vote
1answer
14 views
Can't find names with criteria that has special symbols in PostgreSQL
I am running the following query which gives syntax error:
select distinct e.ahrims_id,e.card_num,e.name_related,e.d_name,e.father_name,
e.d_father_name from hr_employee e left join ...
1
vote
0answers
23 views
Return Maximum Rolling/Moving Count For Each Person For Any 90-day Period
Basics
I have a table of patients with visit dates. There can be an arbitrary number of patients each with an arbitrary number of visits. A visit is recorded by saving the date of the visit. Only one ...
0
votes
1answer
28 views
Error text: “Key … is not present in table” even though the key is there?
Using Postgresql 9.5 on Ubuntu 16.04. Importing large .csv files into a new database using COPY FROM. The database is created by the same script that attempts to import the .csv files, so I know the ...
0
votes
1answer
31 views
PostgreSQL to MySQL migration to use database for WordPress site - how to's?
I have a complex issue to solve, related to database migration. I have a website full of news to migrate that is currently based on a PostgreSQL database and written in PHP, without a CMS. I have a ...
2
votes
1answer
36 views
PostgreSQL CAST() giving unexpected results
I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of:
Round up if last digit is 5 or higher.
Round to even if last digit is 5.
Randomly round up or down ...
2
votes
0answers
13 views
Postgres role security for multi-tenant database
I have a multi-tenant application where the Postgres DB schemas are set up as follows:
_admin (tables that are invisible in the UI to standard users but need to be read-accessible, common across all ...
1
vote
0answers
22 views
Robust way to build a DWH without downtime
I have a process that build a data warehouse using Postgres. The process runs every day at midnight and works in three steps:
Copy the production database (prod) to a temporary database (dwh_tmp) ...
1
vote
0answers
16 views
Forked database doesn't use same query plan as original even after Vacuum Analyze
I forked a production database for some testing. I'm running a query select distinct store_id from <table>, where there is an index on "store_id" (the index was created via create index ...
2
votes
0answers
15 views
Can you get the “CREATE” statement for a domain in PostgreSQL?
In PostgreSQL, for many objects like functions (among other things), there exists a way to get the CREATE command for that specific object (with functions you can use pg_get_functiondef(oid), for ...
1
vote
0answers
15 views
Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5
I have two tables: hub and huboperator
hub table is defined as
CREATE TABLE public.hub
(
parent_hub_id integer,
ancestors jsonb,
descendants jsonb,
name character varying(1024),
id integer ...
3
votes
2answers
44 views
Automate COPY command - Postgres 9.2
I've a backup table with several distinct IDS... I wanna do a BACKUP of those rows, by ID.
The function has to be executed by a limit of account_id ( select dump(21); - Where 21 = number of ...
3
votes
1answer
35 views
How to reset a postgres database that has used all of the available disk space?
I have a small ~10G PostgreSQL 8.4 database used for development that has filled up the /var directory and is now unusable. I am unable to connect to the database in this state to delete any data or ...
2
votes
1answer
18 views
password confusion in postgres sql
I have logged into postgresql server using
sudo -u postgres psql
Then I also created a database from within /usr/local/bin directory using the command.
createdb -h localhost -p 5432 -U postgres ...
0
votes
0answers
12 views
Get the Replication/Lag time of Redshift
I'm currently running RedShift with pushes of all data from our production Postgresql databases every 10 minutes or so. Periodically, the ETL process from Postgresql to Redshift gets delayed or backed ...
3
votes
1answer
37 views
help in the initial configuration of postgreSQL9.6
I recently installed postgreSQL9.6 version on windows 7 64 bit machine. During the installation process, I was asked to key in the super user password and the locale of the database (I chose default ...
1
vote
1answer
26 views
Postgresql perl : “NOTICE: elseif should be elsif at line …”
It's not the first time I get this strange NOTICE when adding perl functions in Postgresql :
NOTICE: elseif should be elsif at line 11.
This happens at CREATE FUNCTION as well as when using the ...
0
votes
1answer
29 views
Is it possible to set PostgreSQL roles with privileges this way?
I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now).
Following ...
4
votes
2answers
40 views
Is it possible to have two copies of a table clustered in different arrangements seamlessly?
I have a table with around 100M rows. It only gets data inserted once/day but we need to do selects a lot. The selects are usually simple but need to return 100s of thousands of rows sometimes.
...
1
vote
1answer
33 views
Select rows and add their previous rows
I have a table structure that holds historical data for another table. Something like
id | name | date
----------------------------------------
1 | John | 2016-01-01 ...
0
votes
2answers
35 views
Increasing Postgresql 9.5 max_connections to at least 300
Right now I am using the default postgresql.conf which has maximum connections set to 100. I need to increase max_connections to 300. Postgresql 9.5 is the only thing running on my Linode.
I am ...
8
votes
2answers
135 views
What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?
I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this ...
1
vote
1answer
25 views
Consolidate multiple rows into single row
In PostgreSQL 9.5, I have a table named reports:
CREATE TABLE public.reports (
id BIGSERIAL PRIMARY KEY,
id_station character(11) NOT NULL,
date date NOT NULL,
element character(4) NOT NULL,
...
0
votes
0answers
43 views
Pgpool not load balancing
I am configuring Pgpool to load balance select queries among a master, and two slaves. However, all queries are still going to the master server, and none to the slaves.
I have set load_balance_mode, ...
1
vote
1answer
15 views
Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?
Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
6
votes
3answers
137 views
Calculate 40 day moving average w.r.t to a field
I have a table that stores the information about user calls in a call center. The table has a call_id, date when the call was made, actual date and time of the call, call type and a score associated ...
1
vote
1answer
57 views
Create Materialized View from foreign table
I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server.
What do I have to add to make the query access the foreign ...
1
vote
0answers
22 views
Querying pg_buffercache: relation “pg_buffercache” does not exist
I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine:
psql -qAtX -h *** -p *** -U postgres -c "select count(*) from ...
3
votes
3answers
42 views
SQL, category tree with multiple parents
I need to make a category tree where some leafs can have multiple parents, for exaple
g
\--b
\--a
c
\--d
\--a
above category 'a' has two parents(g and c)
My first attempt was to create the ...
2
votes
0answers
18 views
How do I organize database initialization so it can be executed in a fixed order?
I am trying to update an established database system to leverage change management. The main point is to make the development and production environments follow the same process. I need help ...
0
votes
1answer
19 views
Database in postgresql with missing rows, how to fill the missing rows with a backup copy?
I have a big problem and I'm not that good with postgresql since I use Django to make queries and I only have the basics of sql for querys.
My problem is that there was a problem where a super user ...
0
votes
1answer
21 views
Create pgAdmin database on local machine
I am trying to create databases on my local machine using pgAdmin. When I try to connect to a new server using the "New server registration" entry below it gives me the "Server doesn't listen" error ...
2
votes
2answers
31 views
Repeat total count value
I have a table called paths that looks like this:
id parent_id
-- ---------
1 null
2 1
3 2
4 3
5 4
6 5
7 5
8 5
9 ...
0
votes
1answer
23 views
Why my Postgres 9.3 database is logging crazily autovacuum alerts
The latest days, Postgres has started to log very frequently (about 100 logs per second!!!!) this kind of traces:
autovacuum: found orphan temp table "pg_temp_5"."p14528" in database ...
1
vote
1answer
38 views
2ndQuadrant BDR installation not working anymore
I previously used the following to install BDR (9.4) on Ubuntu:
echo "deb http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant main" > "/etc/apt/sources.list.d/2ndquadrant.list"
wget ...
0
votes
0answers
26 views
postgresql Idle for transaction while migrating 30GB database
I'm migrating part of a mysql database into a postgresql database with a different Schema.
I've written a python script, using mysql-connector and psycopg2 modules. It takes care(should) to ...
0
votes
1answer
17 views
Viewing disk usage of temporary buffers of running queries in Postgres
I know how to get the sizes of relations or total table disk usage per table in Postgres 9.5 (https://wiki.postgresql.org/wiki/Disk_Usage), but I have some queries running whose temporary disk buffers ...
-1
votes
1answer
42 views
How do you create SQL INSERT script that includes value of AUTOINCREMENT column? [on hold]
How do you create SQL INSERT script that includes value of AUTOINCREMENT column ? I have two table with identical schema that I need to synchronize.
I tried using IntelliJ IDEA but it doesn't export ...
0
votes
1answer
22 views
Postgresql: Storing a table index on a separate disk to the data
I have development a postgresql 9.5 database which I originally set up on an SSD. However, as this project will grow to ~200TB (and likely more), I have moved my current test of ~200GB on to a HDD, ...
1
vote
1answer
33 views
Count approximation with where clause
I have the following problem, we have a table of facilities for apartments that looks something like this:
I would like to perform the following query
SELECT crawled_name, count(f.id) AS count ...
1
vote
1answer
21 views
Insert column name as value
Consider the following, in PostgreSQL:
drop table test ;
create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ;
insert into test values ('red',1,2,3) ;
I ...
2
votes
1answer
64 views
Placing a uniqueness constraint on a date range
I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).
How do I place a constraint on the table such that bookings are not ...
1
vote
0answers
26 views
PostgreSQL merging identical tables from separate databases into single database
I've started working on a project where the application data is split across multiple databases that all have the same schema (basically there's a database per "organisation").
After some discussion ...
3
votes
1answer
40 views
Why does PostgreSQL scan a lot of rows?
I have a small table of 135,000 rows.
Here is the schema:
id integer NOT NULL nextval('history_sum_id_seq'::regclass)
zone_id integer NOT NULL
spot_id character varying(24) ...
3
votes
4answers
58 views
Select rows for which at least one row per set meets a condition
I have following table:
create table test (
company_id integer not null,
client_id integer not null,
client_status text,
unique (company_id, client_id)
);
insert into test values
(1, 1, ...
0
votes
1answer
23 views
pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing
I'm using pgAdmin III v1.22.1 on Windows 7 to connect to a PostgreSQL server version 9.4.8 on Red Hat.
We have scripts to REFRESH MATERIALIZED VIEWS during the night.
When they are refreshing, I ...
1
vote
1answer
18 views
Get the highest value [duplicate]
I have the following data:
account_id note_id size id
---------- ------- ------ --------
48 1554 196036 28229509
48 1554 18777 28229588
48 1554 4861 28229566
...
1
vote
1answer
41 views
Insert into multiple tables with one query
I would like to add data to at least three tables with one query. I thought of something like this:
WITH ins AS (
INSERT INTO core.adr
(street, "number", postal_code, city)
VALUES
('test ...
1
vote
0answers
19 views
Can a BRIN index be used instead of Table Partitioning in Postgres?
Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) ...
0
votes
0answers
16 views
PostgreSQL Redhat Cluster Implementation(Active-Passive) With Streaming Replication setup For DR failover
We have a scenario Where Customer is looking For Cluster kind of solution with DR failover Setup.
I am Planning to suggest the attached Architecture with customer.
Few Clarification as per ...
