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
29 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
20 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
73 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
31 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
19 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
37 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
8 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
23 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
22 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
62 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
112 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
67 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
20 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
71 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
19 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
18 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
41 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
47 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
60 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
38 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
32 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
18 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
22 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
82 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
39 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
29 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
30 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
51 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
29 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
41 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
113 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
147 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
47 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
59 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
21 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
161 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
80 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
61 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
47 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
23 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
33 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
28 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 "...