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

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
0
votes
1answer
21 views

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 ...
1
vote
0answers
25 views

Check Postgres Listener is up/down?

I am using PostgreSQL LISTEN/NOTIFY functionality in Java. So I have made a ListenerBean which is run at Application startup. LISTENER class in java: @Singleton @Startup ...
1
vote
1answer
19 views

Renaming a column in a materialized view with an unknown name

I have a materialized view with an unknown column name, and I need to give that column a name. I can't just recreate the view because it took a long time to create. dba=# \d temp_name_relationships; ...
1
vote
1answer
49 views

Can I iterate a value depending if the previous is what I want?

Suppose that I have this table in PostgreSQL 9.4: CREATE TABLE trajs ( id serial NOT NULL PRIMARY KEY, obj_id integer, traj_id integer, geom geometry, t timestamp without time ...
3
votes
1answer
55 views

Self-JOIN with occurrence count

I use PostgreSQL 9.4 and I have a table called certificates containing the following columns: CREATE TABLE certificates ( id serial PRIMARY KEY, common_name varchar(255) NOT NULL, state ...
0
votes
0answers
26 views

trigger/trigger function to work on all column but 1

I need a trigger/trigger function to fire on all insert/updates for all changes in all columns but 1 if a user changes the value in that column, it is not significant and the trigger does not need to ...
0
votes
1answer
61 views

How do I save result of a join to another table?

In PostgreSQL I can save result if SELECT to another table with SELECT INTO: SELECT id INTO new_record_ids FROM new_table EXCEPT SELECT id FROM old_table ORDER BY id; However this doesn't ...
0
votes
0answers
25 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
39 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
38 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
80 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
16 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
17 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
68 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
59 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
30 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
36 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 ...