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

learn more… | top users | synonyms (1)

0
votes
1answer
75 views

A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I have the following tables in my postgres database - power_relations - (id, properties) - power_relation_members - (id, power_relation_id, member_id, member_type) - power_lines - (...
1
vote
1answer
44 views

PostgreSQL: Pass table as argument in function

I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example: CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId ...
0
votes
0answers
13 views

Partition elimination and date variables

I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older ...
0
votes
0answers
22 views

SET ROLE xxx command extremely slow

On Amazon RDS, I setup a Postgresql database. I have a custom user table, each user in my database maps to a specific role. The role are created via a trigger and I keep the role in the same custom ...
0
votes
1answer
14 views

Migrate schema updates onto existing schema

So we have three development environments from a programming point of view. Each environment has its own postgres database. Let's say a table gets an extra column in our development environment, we ...
0
votes
1answer
38 views

Background Jobs in PostgreSQL

I want to run a query all X seconds on my PostgreSQL database (9.5 on Windows Systems). I read that you can use Background Jobs to achieve this. The pgAgent Jobs (https://www.pgadmin.org/docs/1.4/...
1
vote
0answers
17 views

Debugging random slow query spikes on Index Only Scans

We have a query that for majority of requests (about 400rpm) executes in around 2ms but occasionally we have one request that will spike to 100ms+. My Googleing may be terrible but does anyone have ...
2
votes
1answer
25 views

Require JSON to have an element

When working with the json datatype, is there a way to ensure the input JSON must have an element? I don't mean primary, I want the JSON that gets inserted to at least have the id, name element. It ...
0
votes
0answers
9 views

can't connect to pgbouncer administration database

I installed pgbouncer and I setup the pgbouncer.ini and my application can connect to pgbouncer and make queries. But when I try to connect to the virtual administration database of pgbouncer by ...
0
votes
1answer
71 views

intel p3600 SSD no increase in read speed - postgres rdbms vs hdd

We run a custom application that produces analyzable data-sets for medical research. the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in ...
0
votes
0answers
29 views

Postgresl database deadlock problem

My Postgresql had following deadlock problem. [2016-06-15 15:38:55.657 MMT] ERROR: deadlock detected [2016-06-15 15:38:55.657 MMT] DETAIL: Process 2488 waits for ShareLock on transaction 1050868701;...
0
votes
0answers
17 views

Limits to decision tree as SQL query?

I'm trying to figure out the fastest way to calculate a prediction and this is one of the possible ways. The idea is to first train the decision tree with some outside tool and then use the weights ...
0
votes
0answers
26 views

pg_partman + zabbix

I'm very new to postgresql, pg_partman and zabbix. Idea would be to partition zabbix's postgresql-9.5 database with pg_partman. Trend data should be saved for one year and history data maybe 2 months, ...
0
votes
0answers
7 views

Add Access to stv_recents in redshift

How can I grant access to all users in a group so that they can see all events in the stv_recents table, not just their own jobs?
0
votes
0answers
17 views

Restrict database size

I'm surpised that I did not find this Q on SO. However, googling for it brings up very old pages. Likely I did not find the correct keywords. I'm pretty sure that I've read about a feature in ...
0
votes
1answer
21 views

PostgreSQL: psql command-line client does not show all schemas

I have PostgreSQL 9.3 server. Connecting using an JDBC client (Squirrel) and executing select schema_name from information_schema.schemata shows me all the schemas on the server. Connecting the ...
1
vote
0answers
17 views

Postgres - Trying to backup Functions only

I'm trying to follow Craig Ringer's answer to this question: http://stackoverflow.com/questions/13758003/how-to-take-backup-of-functions-only-in-postgres My command is: psql -U username -AT db_name -...
3
votes
1answer
55 views

PostgreSQL not using index scan when a second ORDER BY column is present

I need to query the latest entry from a table with >130mn entries using PostgreSQL 9.5. There are two columns of interest, row_id (integer, the primary key), and row_time (timestamp, not null, with ...
1
vote
4answers
100 views

Find IDs from a list that don't exist in a table

Say I have the following schema and data: create table images( id int not null ); insert into images values(1), (2), (3), (4), (6), (8); I want to perform a query like: select id from images ...
-3
votes
1answer
66 views

Why do we have Geometric Datatypes in SQL databases? [closed]

What is the purpose of a specific SDI datatypes in SQL tables? They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them? What ...
0
votes
1answer
18 views

Postgres: Missing data after Binary Copy - needs VACUUM FULL?

I'm running Postgres 9.0 on a relatively big (10TB) and old Database. I must move that Database to new Server Hardware, so i copied whole $PGDATA Dir via rsync of a LVM Snapshot and used archive/...
2
votes
2answers
65 views

PL/pgSQL regclass quoting of table named like keyword

I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new ...
1
vote
0answers
17 views

postgresql 9.2 hash join issue

This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time. When should ...
1
vote
0answers
28 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
16 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
37 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
34 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
51 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 ...
3
votes
1answer
54 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
27 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
30 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) ...
0
votes
0answers
18 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 ...
1
vote
0answers
17 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
18 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
80 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 ...
2
votes
1answer
38 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 ...
1
vote
1answer
22 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
1answer
20 views
+50

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
41 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
72 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
34 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
17 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
65 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 ...