This tag is specifically for PostgreSQL version 9.2
0
votes
0answers
32 views
Osmosis, Import to different schema in postgres [on hold]
Does anyone know how to use osmosis to save to a different schema?
I used the pgsnapshot_schema_0.6.sql to create a blank data table, but it always creates it in the public schema. I need to create ...
0
votes
0answers
31 views
Preserving tablespace and databases from previous install?
I goofed and destroyed my postgresql 9.2/postgis 2.1 installation under freebsd 9.2 by trying to install mapnik which, for my inconvenience, overwrote it with 9.0.
I have, on another spindle, a ...
0
votes
2answers
54 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
36 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
48 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
24 views
Postgres 9.2 under FreeBSD 8.4 - How do I find the databases & tables?
I'm trying to create an osm tileserver and am at the point where i've created the database to hold the output of osm2pgsql. But I can find neither the database (map_data) nor, of course, its tables. ...
0
votes
1answer
51 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
57 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 = ...
1
vote
1answer
44 views
query errors when all data not present
I have a working query that show success rate of staff. When I run it in for all staff it works flawlessly.
However, when I insert it as a function within a java based page I get an error:
ERROR: ...
0
votes
1answer
63 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
107 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
76 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
62 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
96 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
136 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, ...
2
votes
1answer
78 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 ...
0
votes
1answer
69 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 ...
0
votes
0answers
227 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
65 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
70 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
145 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
41 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
99 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)
...
1
vote
1answer
202 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
574 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
35 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
163 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
69 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
77 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
66 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
36 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
102 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
156 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
41 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
156 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
494 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
68 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
70 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
150 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
63 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
451 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
108 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
230 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
59 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
255 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 ...
1
vote
1answer
136 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
42 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
78 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
182 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 ...
