This tag is specifically for PostgreSQL version 9.2

learn more… | top users | synonyms

2
votes
1answer
212 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
95 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
229 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
177 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
297 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
495 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 ...
1
vote
1answer
157 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
1
vote
0answers
1k views

PostgreSQL isn't listen on 5432 in Windows 7

I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented: listen_addresses="*" port=5432 And this is the ...
2
votes
1answer
125 views

PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'. Various revisions of this SQL isn't constraining '-/+infinity' input: CREATE TABLE bill ( id ...
2
votes
1answer
196 views

How to solve 'tuple concurrently updated' error?

The following query is performed concurrently by two threads logged in with two different users: WITH raw_stat AS ( SELECT host(client_addr) as client_addr, pid , usename ...
1
vote
3answers
288 views

Newly installed Postgresql 9.2 on same box as 9.1

I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed. I have ...
0
votes
1answer
76 views

How to index refined null values?

I have table with two indexes: latest_channel_snapshots_views_idx (view_count DESC NULLS LAST) latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST) What I want is ...
0
votes
1answer
193 views

Trigger - Add to a date using a subquery

(postgresql 9.2) I'v 2 tables Table 1 (extinguisher) extinguisher_id (serial) type_designation_extinguisher_type(text) manufacturing_date (date) life_date(date) Table 2 (extinguisher_designation) ...
2
votes
1answer
540 views

postgresql replication - pg_stat_replication is showing empty columns

I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list. $ ps aux ...
0
votes
1answer
2k views

Error: Unable to write inside TEMP environment variable path

I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1. I get the following, rather famous it seems, error: There has been an error. Unable to write inside TEMP ...
1
vote
1answer
47 views

Parsing the value and copying it to another column in postgresql

column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, ...
2
votes
1answer
291 views

Finding by ID in postgresql very slow on big table

I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is ...
1
vote
0answers
132 views

Getting multiple queries with “show transaction isolation level” in pg_activity

I am using PostgreSQL server for my production use. When I fire a query like select * from pg_stat_activity on my server, I am getting 98% of queries like SHOW TRANSACTION ISOLATION LEVEL and ...
0
votes
0answers
115 views

Can't connect to service after updating ODBC driver

I am upgrading a VB6 program at work and one of the changes is that it now uses PostgreSQL 9.2.4 instead of 8. I was getting a 'client encoding mismatch' error, so I updated the ODBC driver, and the ...
1
vote
0answers
81 views

postgres-xc : Address already in use

I am starting a postgres-xc server with two datanodes and one coordinator. I used same parameter for both nodes but from one node I am getting the following error: LOG: could not bind IPv4 socket: ...
0
votes
0answers
45 views

postgres-xc - ERROR: object already defined [closed]

I am configuring two data nodes and a coordinator on postgres-xc. I get eh following error: ERROR: PGXC Node datanode1: object already defined CREATE NODE pgxc_pool_reload ------------------ t (1 ...
1
vote
1answer
201 views

Client Encoding Mismatch when creating new database

I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run ...
0
votes
1answer
312 views

Running a UPDATE on 3 million records

I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table ...
0
votes
0answers
28 views

Login in pgAdmin without password (windows 7) [duplicate]

I want login in pgAdmin without user password (I forgot password) As I konw, I can change pg_hba.conf so, that can be possible to login into pgAdmin without password. Can you tell please what must ...
1
vote
1answer
43 views

Show clients with staff assigned and no staff assigned

I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query: SELECT g.name AS group, cl.name_lastfirst_cs AS client FROM clients ...
1
vote
1answer
412 views

Setting up binary replication between two PostgreSQL instances

I have two virtual machines: dbrepa (-.175) and dbrepb (-.191). Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone. I need to have the ...
0
votes
1answer
1k views

Create Language plperl - Error: could not load library plperl.dll

When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found. But in my computer, ...
0
votes
1answer
75 views

Is PostgreSQL appropriate for processing this large but simple dataset? [closed]

I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y ...
-3
votes
1answer
77 views

Make “NOT NULL” default in Postgresql 9.2 [closed]

I use NOT NULL a lot when creating columns. Is there a way to make this default when defining columns? In other words, make column is_nullable default to NO. (I realize this would make it harder ...
2
votes
0answers
238 views

PostgreSQL index array of int4range using GIN / GIST - custom operator class

Here is my table: CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range[] ); I want to index the val column: CREATE ...
0
votes
1answer
104 views

changing server roles in streaming replication

I want to implement streaming replication using postgresql-9.2.4 in my reporting application. I came stuck on the below scenario. create master and slave with synchronous replication which is ...
4
votes
2answers
986 views

PostgreSQL CREATE TABLE creates with incorrect owner

I'm using PostgreSQL 9.2.4. When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly ...
1
vote
1answer
189 views

Do I need to perform any tasks after Postgres goes into recovery mode?

For starters, I do not have any sort of automated failover in place. After two scenarios, I'm unsure of the state of the database and any required actions, if any, to take: My master server ...
2
votes
1answer
475 views

I need help understanding Postgres's archive cleanup functionality

I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform ...
1
vote
2answers
98 views

Constraint on Composite Type

How can I create a constraint on a sub-field of composite type? Pseudocode create type axis( major_axis float, minor_axis float, angle float constraint angle_constraint check(angle ...
3
votes
1answer
311 views

Group By primary key or DISTINCT increase query time over 1000x with limit

Also see http://stackoverflow.com/questions/17741167/hibernate-jpa-improve-performance-of-distinct-query but I realized this is mainly a PostgreSQL issue. My application uses a 3rd party extension to ...
2
votes
1answer
401 views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My database ...
0
votes
1answer
53 views

Wrong return results

I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
0
votes
1answer
138 views

pg_upgrade fails with lc_ctype cluster values do not match

I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X. When trying to upgrade the database I ...
4
votes
1answer
234 views

Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?

The pg_xlog directory for my master cluster looks like this: -rw------- 1 postgres postgres 16M Jun 21 21:42 000000010000000000000001 -rw------- 1 postgres postgres 16M Jun 21 22:42 ...
1
vote
1answer
250 views

How does Postgres know which logs have been archived?

I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave ...
0
votes
1answer
1k views

How to retrieve data from Postgresql index using java

I am writing an application for searching, using text search of Postgresql. I am a beginner in Database. I created a Gin index and i am trying to get data from it. My code is import ...
6
votes
1answer
353 views

PostgreSQL “freeze”/“unfreeze” command equivalents

In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply ...
4
votes
2answers
385 views

PostgreSQL: Executing DDL on every schema

I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my ...
1
vote
1answer
593 views

Why does `pg_lsclusters` not list my Postgres cluster?

I apt-get installed Postgres 9.2.4 (using PPA). I used pg_dropcluster --stop 9.2 main to remove the default database cluster, because I intended on creating a new cluster on a separate SSD volume (a ...
0
votes
0answers
409 views

How to import a text file with '|' delimited data to PostgreSQL database? [closed]

I have a text file with | delimited data that I want to import to a table in PostgreSQL database. PgAdminIII only exports CSV files. I converted the file to a CSV file using MS excel but still was ...
2
votes
1answer
881 views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
4
votes
1answer
59 views

Workaround the snapshot isolation in a writable CTE

The postgres docs say: The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the ...
2
votes
2answers
949 views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
4
votes
1answer
119 views

LISTEN / NOTIFY privileges

I have a single postgres database, with two users; Alice and Bob. I would like to be able to do a NOTIFY alice_channel 'sensitive data' without Bob being able to sneakily LISTEN in just by guessing ...