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

learn more… | top users | synonyms (1)

-1
votes
2answers
22 views

How to select sets of rows for which some condition is met for each of sets?

Question title might no be clear, sorry for that, but I don't know how to describe my problem. I have following table: create table test ( company_id integer not null, client_id integer not ...
1
vote
0answers
8 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 ...
0
votes
1answer
20 views

potgresql foreign key index unused for some columns on one specific server

I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and ...
1
vote
1answer
28 views

Why PostgreSQL scans a lot of rows?

I have not a big table with only 135000 rows inside. Here is schema id integer NOT NULL nextval('history_sum_id_seq'::regclass) zone_id integer NOT NULL spot_id character varying(24) ...
2
votes
2answers
29 views

Postgres - Regroup rows with at least one common attribute

I have the following table : Letter | Figure -------|------- A | 1 B | 1 B | 2 C | 2 C | 3 D | 3 E | 4 I would like to group rows that share ...
0
votes
0answers
10 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 ...
0
votes
1answer
28 views
+50

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
0answers
37 views
+50

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
4
votes
1answer
244 views

PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows: CREATE FUNCTION public.assert ( in_assertion boolean, ...
0
votes
1answer
37 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 ...
0
votes
1answer
15 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 ...
3
votes
2answers
575 views

DROP TABLE … CASCADE does not drop any dependent table

I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My ...
0
votes
1answer
212 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
1
vote
0answers
10 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) ...
4
votes
2answers
3k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
0
votes
0answers
13 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
115 views

When does pg_dump not export CREATE EXTENSION?

I have a production database that I am trying to dump then load (in plain text format) locally. The problem is, the database being dumped uses the hstore extension in several tables, and contrary to ...
1
vote
1answer
228 views

PostgreSQL on Windows fails to start - 'could not create shared memory segment'

PostgreSQL 9.4 & 9.5 (beta) on my Windows 7 laptop fails to start. In the Windows event log I see the following error: 2015-11-10 13:53:49 IST FATAL: could not create shared memory segment ...
0
votes
1answer
17 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 ...
2
votes
1answer
40 views
+100

Increase in IO Wait after adding indexes with postgres

I've recently been put in charge of a system that has been fairly untended for a period of time. The majority of activity is via queued worker jobs based off of data feeds from other systems/data ...
0
votes
0answers
26 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 ...
0
votes
0answers
24 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 ...
1
vote
1answer
257 views

pg_ctl promote is not working

I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared ...
1
vote
1answer
142 views

PostgreSQL WAL recovery not working due to missing WAL

I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable ...
0
votes
1answer
24 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 ...
1
vote
1answer
23 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 ...
0
votes
0answers
21 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 ...
2
votes
2answers
3k views

Postgres corrupted DB recovery

Is there a way to recover data from partially damaged Postgres DB? My HDD was damaged in transport, I was able to recover most of the files but I cannot connect to the DB or use dump: I'm getting ...
3
votes
1answer
174 views

PostGIS: Cutting Holes in Self Geometry

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an ...
0
votes
1answer
20 views

PostgreSQL vs MariaDb On Read/Write when table has about 200M Rows [on hold]

I want to build a new web application with my collected data, but I don't know witch DBMS is best choose for my Project, MariaDB or PostgreSQL there is 2 table : categories and data more than 200 ...
2
votes
2answers
523 views

Why postgresql pg_dump exports a view as a table?

I am using postgresql 9.3 pg_dump tool for extracting only the public schema definition using: pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql but when I check schema.sql one ...
2
votes
1answer
29 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
23 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
16 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
11 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
20 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 ...
1
vote
1answer
18 views

The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which ...
0
votes
0answers
18 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
24 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
613 views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
2
votes
1answer
21 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 ...
1
vote
1answer
18 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 ...
1
vote
0answers
25 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.
0
votes
0answers
10 views

Postgres archive_command on Ubuntu 16.04

Ubuntu 16.04: Postgres 9.5: When i start psql -c "select pg_stop_backup();" command to execute for hot standby replica. It gives me warning as archive_command as: NOTICE: pg_stop_backup cleanup ...
0
votes
2answers
19 views

Query Amount of entries per column from multiple tables + provide table name

I have five tables with all having the same column (objart): I queried for the number of entries and combined the result with UNION, which worked well. Two questions: 1) Is there a shorter/ better ...
1
vote
0answers
21 views

psql in windows

I want to connect to Amazon Redshift and run scripts like exports to s3 and loads through command line. is psql the only way to go? If that is the case, is there a package that would install just ...
13
votes
3answers
6k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
1
vote
1answer
110 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
1
vote
0answers
24 views

Optimal query to show active records by date

I'm trying to determine what the optimal query might be for associating a date with a record that would have been active for that day. Here is a setup to demonstrate what I'm trying to do (demo at ...
1
vote
1answer
22 views

Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that? I am creating an index in a ...