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

learn more… | top users | synonyms (1)

3
votes
1answer
42 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
27 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 ...
1
vote
1answer
33 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 ...
7
votes
3answers
51 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. It'...
1
vote
1answer
34 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 14:38....
0
votes
2answers
78 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 ...
7
votes
2answers
142 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
35 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
54 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
18 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 ...
5
votes
3answers
150 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
67 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
32 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
52 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 ...
1
vote
0answers
23 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
38 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
22 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 (...
1
vote
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
25 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 "...
0
votes
1answer
48 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
33 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 convert/...
0
votes
1answer
22 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 ...
0
votes
1answer
28 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
35 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
23 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
65 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
1answer
33 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
42 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) NULL::...
4
votes
4answers
75 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
31 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 can'...
0
votes
1answer
21 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 ...
0
votes
1answer
50 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
32 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
21 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 ...
0
votes
1answer
23 views

PostgreSQL identifier cross-check returns different results for qualitatively-identical query

Setup: PostgreSQL 9.3.5 I have two tables with a common identifier (propid) that is unique in one table (property) and not unique in the second table (addressstring). There are no NULL propid in ...
0
votes
0answers
31 views

Considerations to take into account for moving a big table into another database

In our application, we a have 67 GB table ( very big compared to the rest of our tables ) and it acts almost as an archive table since its record are not modified and the ratio of read operations is ...
0
votes
0answers
31 views

Postgresql: Windows Server 2012 R2 or SuSE Linux Enterprise Server 11 SP4

I am given the possibility to chose between Windows Server 2012 R2 and SuSE Linux Enterprise Server 11 SP4, while renting a virtual server. For the needs of the server I want to create, I wish to ...
0
votes
1answer
33 views

Long queries on PostgreSQL hot standy

Is there a way to convince PostgreSQL to execute long queries on a hot standy? It kills the query with User query might have needed to see row versions that must be removed. I understand that, but I ...
0
votes
0answers
43 views

PostgresSql - Flattening nested JSON arrays

Interesting problem trying to query nested arrays stored in JSON... Context I'm using PostGreSQL and Heroku Dataclips to navigate some data I'm pulling from a server. After doing some reading, I ...
1
vote
1answer
35 views

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does). My query is this INSERT INTO journals (ext_ids, title) VALUES ('{"...
0
votes
1answer
23 views

How to create tables with geometry in Postgres?

I need to create 2 tables with distinct geometry. One needs to be a table that has point information. The other needs to be a table that has line information. They do not need to be populated as ...
0
votes
0answers
18 views

Postgres: How to log materialized view refresh

I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it ...
0
votes
1answer
25 views

How to create a table with data from other tables in Postgres?

How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6. This is a spatial data with the Postgis extension enabled so ...
0
votes
0answers
32 views

How we can make “statement_timeout” work inside a function?

I have a requirement to perform a query operation in a specified time limit in postgresql. If the query is not performed in that time limit, we need to catch a exception/a action and need to kill all ...
2
votes
0answers
29 views

Could I use both UUID and Sequential ID or is it bad design

Sequential IDs have pros and cons. UUIDs have pros and cons too. Why not use both for each table? (Let's say I have -and will have- no problem about storage space) I could use: UUID for exposition, ...
0
votes
1answer
45 views

DB schema for storing the process tree of linux

I search a db schema to store the process tree of linux every half hour. Platform: PostgreSQL on Linux. I am unsure where to split all the data into columns or to use a json column. Which one is ...
1
vote
1answer
47 views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
1
vote
0answers
32 views

Why is Server Configuration in Pgadmin3 inaccessible for me?

My Postgres server is running (localhost:5432), but Tools > Server Configuration is greyed out for me as below. I was attempting to access it to edit/locate my postgres.conf file.
1
vote
1answer
23 views

Storing email inbox information

I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure ...
2
votes
1answer
25 views

Postgres LEFT JOIN with recurring dates and users

I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL. What I have so far is a I create a sequence of days, then LEFT JOIN it, if I ...