PostgreSQL 9.4 : 2014 release of PostgreSQL
0
votes
1answer
26 views
Working with Materialized View
I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4.
When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH ...
2
votes
1answer
46 views
Can bdr only replicate one database per server?
I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm.
My Problem: I want to add more databases to the replication but every time if I added the other database and ...
1
vote
2answers
46 views
Postgresql 9.4 high availability
I currently have a master postgresql 9.4 server containing many databases on Ubuntu 14.04.
I tried to use Barman to setup backup, but would like to set up streaming replica so that when the master ...
0
votes
0answers
20 views
Insert into a specific partition, create if not exists
So I have my master table:
p_reports: id(pk), web_id, other(data)
At the moment I create my partitions which inherits from p_reports by using:
CREATE TABLE IF NOT EXISTS ...
1
vote
2answers
54 views
Postgres Index a query with MAX and groupBy
Is there any way to index the following query?
SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id;
I've tried creating sorted (non-composite) indexes on frame and ...
0
votes
1answer
35 views
Delete index while query is running, is it possible?
I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still ...
1
vote
1answer
41 views
Rely on .pgpass in CREATE USER MAPPING
I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do ...
4
votes
1answer
110 views
PostgreSQL operator uses index but underlying function does not
I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
3
votes
1answer
97 views
PostgreSql JSONB SELECT against multiple values
I have a very simple JSON table which I populate with some sample data:
CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO ...
1
vote
0answers
18 views
Error when creating Trigger (PostgreSQL 9.4)
I created a table 'Company', and I want to create a trigger so a company can only present in one city, but it can be present in several different cities in the same country. For example, there's only ...
1
vote
1answer
53 views
Use result of aggregate in same select?
Is it possible to feed the result of an aggregate select into the same select with Postgresql?
I'm aware of the WITH clause -- but, this would be true syntactical sugar, wouldn't it?
SELECT ...
2
votes
1answer
58 views
Retrieving latest record using DISTINCT ON is slow [duplicate]
Using Postgres 9.4.
I have a table cartests with 5.5M rows. Each row is a car test:
\d log.cartests;
Table "log.cartests"
Column | Type | Modifiers
...
4
votes
1answer
97 views
Is there a way to insert multiple rows into a table with default values for all columns?
I can insert multiple rows into a table with default values for all columns the RBAR way:
create table course(course_id serial primary key);
do $$
begin
for i in 1..100000 loop
insert into ...
0
votes
0answers
399 views
Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade
I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this:
sudo pg_dropcluster 9.4 main ...
1
vote
0answers
31 views
PostgreSQL timezone setting
While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku ...
0
votes
0answers
20 views
Is PostgreSQL JSONB @> operator equal to ->''=?
Is the jsonb_column @> '{"key":value}'::jsonb operator equal to jsonb_column->'key' = value? in terms of result, performance, and indexes that will be used?
0
votes
0answers
51 views
Does someone have a cannonical example of setting up PostgreSQL with PLV8?
I'm hoping that someone can either post or point me to a guide for setting up PostgreSQL (9.4) and PLV8.
I'm not an expert at PostgreSQL by any means and have been reading about and wanting to play ...
1
vote
2answers
247 views
Refresh materalized view incrementally in PostgreSQL
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph (
...
0
votes
0answers
78 views
Are A or D series VMs better for deploying PostgreSQL on Azure?
In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series). Considering this space is wiped on reboot...
Is there a benefit to choosing the SSD option for ...
2
votes
2answers
286 views
PostgreSQL joining using JSONB
I have this SQL:
CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}');
INSERT INTO test(data) ...
3
votes
1answer
149 views
PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key
If I have a table containing:
CREATE TABLE test(
id SERIAL PRIMARY KEY,
name VARCHAR(200),
age INT,
data JSONB
);
and data column populated with {"name": xxx, "age": yyy}, sometimes ...
2
votes
1answer
111 views
SELECT query with DISTINCT on a table-structure for graphs is very slow
I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph.
CREATE TABLE edges (
node_from bigint NOT NULL,
...
