0
votes
1answer
17 views

PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

I have created DB instance on RDS and used below function it gaves error while with same postgreSQL version on local development machine works fine. create or replace function uuid() returns uuid as ...
1
vote
1answer
37 views

Problem with LISTEN - NOTIFY mechanism in PostgreSQL

I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism. I open two consoles and connect to my database using psql client: psql -U postgres test_db In the first console I subscribe to some ...
2
votes
1answer
41 views

Diagnosing the error in a Postgresql to Redshift migration through psycopg2

I am running a job class that contains the following: A Postgresql connection that can issue SQL statements A Redshift connection that can do the same an S3 connection to function as an intermediary ...
0
votes
1answer
30 views

PostgreSQL replication with periodic log shipping

I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally ...
0
votes
1answer
27 views

Need some help with this query or some ideas for change it to better model

I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can ...
0
votes
1answer
22 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
2
votes
1answer
51 views

How to determine if postgres database contains GiST indexes and what type?

Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?
0
votes
2answers
88 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
1
vote
0answers
66 views

Where Can I find *Current* PostgreSQL advice for a SQL Server DBA? [closed]

Been a MSSQL DBA for about 7 years. I have the fundamentals of performance, backups, replication, security and design down for MSSQL. I'm not setting up new applications using PostgreSQL 9.2. I have ...
0
votes
0answers
75 views

On Heroku in production, how can I copy database A to database B on command?

From my local terminal, I can use the following commands to do what I need to do: heroku pgbackups:capture --expire heroku pgbackups:restore charcoal --confirm max-kiosk However, I want to do this ...
0
votes
1answer
49 views

Creating unique constraint to be validated from input

I have this table name tbl with this table definition: create table tbl ( id serial primary key not null, counter integer not null default 1, ref_id integer not null, date_booked date ...
0
votes
1answer
41 views

PostgreSQL: After restoring data, unable to use Views (permission denied)

I'm attempting to get a PostgreSQL environment running locally. I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment. The restoration worked fine ...
1
vote
2answers
128 views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
1
vote
1answer
33 views

Limit balancing results by a column

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed. I also have a function that returns me the ...
3
votes
1answer
333 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
2answers
51 views

Get distinct results based on column and satisfy condition

I have the below select which is selecting from a table with the columns: id, project_id, keyword_id, position, Data example: i, p_id, k_id, p 1, 1, 1, 4 2, 1, 1, 5 3, 1, 1, 6 5, ...
2
votes
1answer
67 views

Postgresql AUTOVACUUM ANALYSE parameters for a specified table

I would like to set autovacuum parameters for a specified table. My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete. This table has only 2 columns (including the ...
1
vote
1answer
217 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
3
votes
2answers
114 views

PL/pgSQL fuction to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
2
votes
1answer
197 views

Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

Steps to reproduce Create database CREATE DATABASE citiesdb WITH OWNER = citiesowner ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' ...
1
vote
1answer
158 views

INSERT using results of CTE INSERT to provide unique id values

I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as ...
0
votes
0answers
31 views

Postgresql replica lagging behind

I restored a master database after a fallback by creating a new basebackup and making the promoted slave stand_by again. Per the logs everything is fine, however the following functions are not ...
0
votes
1answer
39 views

Postgresql: How to add a role that inherits from another?

Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?
1
vote
0answers
55 views

Postgresql 9.2 - Auto vacuum running continuously for 18+ hours

I currently have a table that is updated daily (easily 20%+ rows are updated, all floats/integers) and the vacuum is kicking off and running for more than 18 hours. There are 19 million rows total. ...
-1
votes
0answers
55 views

Bloating effect in Postgresql [closed]

I've done some performance analysis on my DB and I have releved that: Read head 8192 CPU power used 25% Bloating 56% (84GB of 150) 20% occupied by index unused Now I have find that 99% of ...
1
vote
1answer
98 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
4
votes
1answer
147 views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
0
votes
1answer
180 views

Close all connections to postgres without having a connection

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
1
vote
2answers
148 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
1
vote
2answers
56 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
0
votes
0answers
72 views

Postgres 9.2 on Windows

Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
1
vote
1answer
52 views

Postgres settings reported differently in pgsql

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = ...
2
votes
1answer
105 views

PostgreSQL 9.2; Altering a table to use another sequence

I've created a table like so: create table doki_data_item2 (like doki_data_item including defaults); but this new table uses the same sequence as the old table. So two questions: How can I copy ...
0
votes
1answer
368 views

Streaming replication between postgresql 9.2 and 9.3

Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?
2
votes
1answer
199 views

Postgresql 9.2.6 ignoring archive_command

I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between. My ...
3
votes
1answer
221 views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
2
votes
1answer
438 views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
1
vote
1answer
607 views

Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB. These DB are running on ...
0
votes
2answers
246 views

Can't run PostgreSQL locally

I am working to run PostgreSQL is running locally but I cannot connect. I installed postgress using brew, then I downloaded http://postgresapp.com When I try to create a database createdb: ...
1
vote
1answer
75 views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
0
votes
3answers
79 views

Postgresql: Make Column Default a Query

Is there a way I can set up a column in PostGreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
0
votes
1answer
77 views

Complex “activity” based query optimization

I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's ...
1
vote
1answer
146 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
0
votes
1answer
342 views

pg_upgrade 9.2 to 9.3 error

I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server. I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it ...
2
votes
1answer
261 views

Strange Postgres Hstore syntax with curly braces {key=value}

I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program ...
5
votes
1answer
100 views

Is Postgres ignoring my function cost annotation?

Why does this: create or replace function dummy() returns double precision as $$ SELECT random() $$ LANGUAGE SQL COST 777; explain select dummy(); return this: Result (cost=0.00..0.01 rows=1 ...
2
votes
2answers
407 views

How to define alias in an ARRAY_AGG expression?

I'm trying to return pure JSON from a Postgres 9.2 table. SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW)) FROM ( SELECT album, max(release_year) AS release_year, ...
4
votes
1answer
223 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
1
vote
0answers
363 views

PgPool is pushing Postgres to 100% cpu usage

I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
3
votes
1answer
878 views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...