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
0answers
24 views

determine what prevented Postgres from starting

I'm in a bit of a quandry over a test machine that is running a Java Web App on top of a Postgres database. When I started the machine today (running Centos 6.1), I navigated to my site page and my ...
0
votes
0answers
20 views

conditional trigger function for history table

I have a history table that tracks modifications to attributes/geometry for each layer I need to set a condition that if the attribute is 'Y' for the column "export" in the "span1" layer, the trigger ...
1
vote
0answers
27 views

Key/value table: what is a good way to organize it if value could be foreign key?

Suppose a have a structure of two tables Table Key names: |_Key name_|_Parent_key_id_| | key1 | NULL | | key2 | key1 | Table Key values: |_Key__|_Value__|_Version__| | ...
1
vote
0answers
24 views

LISTEN/NOTIFY pgconnection goes down java?

I am using PostgreSQL DB and applying it's LISTEN/NOTIFY functionality. So my listener is at my AS (Application Server) and I have triggers configured on my DB such that when CRUD operations are ...
0
votes
0answers
18 views

RedShift Roles For Superuser-like Role with Read Only Access

I'm managing a RedShift cluster and our engineers need more access than is generally provided to them with their roles. Essentially they would require the right to read EVERYTHING in all tables but ...
3
votes
1answer
38 views

Temp postgres table ignores “excluding constraints”

In PostgreSQL 9.5. I'd like to create a temporary copy of my table without its primary key. Postgres has a feature { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ...
1
vote
1answer
37 views

Use Alias In Join Condition [closed]

In the syntax below I want to use "MaxID" as part of my join condition, but it keeps throwing an error of ERROR: syntax error at or near "[" LINE 29: AND t1.["MaxID"]=t2.id This is my ...
3
votes
1answer
29 views

How to copy part of a database with inconsistent privileges?

I have a Postgres database and I would like to copy certain tables from a single schema into a new empty database. So far I have copied a schema-only dump from from the old DB and applied it to a ...
7
votes
1answer
75 views

Who is “focker” and why does he own my schema?

I have a Postgres DB deployment in Compose.io, and I am using the standard "compose" db for my app. I would like to wipe out the current DB contents so that I can recreate the DB with a new schema. ...
0
votes
0answers
27 views

Force Postgres to write log to a new file

I've updated the value of log_line_prefix, and I wanted that the log file will contain only logs with the new prefix. I deleted the log file (postgresql-9.3-main.log), assuming that once the file is ...
1
vote
1answer
51 views

How can CREATE OR REPLACE fail with already exists?

DETAIL: Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS How on earth is that even possible? How can CREATE OR REPLACE ...
0
votes
0answers
15 views

How are Postgres partitions counted for the table size limit?

Postgres has a limit of 32T in a single table, but what if the table is partitioned - is that 32T per partition now?
1
vote
1answer
16 views

Run lo_export as client through psycopg2

Python 2.7 psycopg2 When I run: cur.execute("SELECT lo_export(123456, '/tmp/test.jpg')") Produces error - need to be superuser to do this. This makes sense as it is running a server side ...
1
vote
1answer
53 views

SELECT count(*) from a materialized view with unique index is slow

I have a materialized view with a unique index (on the id column) and both of these queries are slow (they scan the whole table): SELECT count(DISTINCT a.id) from the_view a; SELECT count(*) from ...
0
votes
2answers
33 views

pgbouncer on separate server than postgresql

Right now I am hosting postgresql and pgbouncer on the same Linode with 48GB of RAM and 12 Cores. The server gets many short lived connections per second. The server load will increase with time and ...
0
votes
1answer
26 views

How can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows?

CREATE TABLE foo ( id TEXT NOT NULL, bar INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (id) ); CREATE INDEX foo_bar_idx ON foo(bar) WHERE NOT bar = 0; I can sort on the bar column in DESC order ...
1
vote
3answers
67 views

How to optimize a join based on a “like” in postgres

Using a pg_trgm index, I have the following query that runs in aprox 20 to 80 milliseconds : SELECT count(*) from protein_seq p WHERE p.sequence LIKE '%SMYSFCKASFTQHNFYFFKN%'; the protein_seq ...
0
votes
0answers
39 views

Postgres hstore or jsonb to store many to many relations

There is a table 'Products' with columns (id, tile, price, description, shop_id, timestamps) Column price and shop_id are dynamic. The rest of column are static. Also there is a table 'Shops' with ...
0
votes
1answer
76 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
56 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
1answer
21 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
28 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
44 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
21 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 ...
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
18 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
35 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
22 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
19 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
57 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
109 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
19 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
17 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
40 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
42 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
59 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
35 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 ...