This tag is specifically for PostgreSQL version 9.2
1
vote
1answer
40 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
28 views
Linking third table with other two table to have a dynamic populated data inside using postgresql [closed]
I am in a phase where I need to have a data populated dynamically in a table which is associated with two other tables using foreign key constraint. Below is the schema/table that I have created in ...
0
votes
1answer
31 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
20 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
122 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
35 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 ...
-1
votes
0answers
59 views
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [closed]
I am trying to open a program for the first time on Windows XP Pro that uses PostgreSQL 9. I'm getting an error message that says :
A problem was encountered while trying to log into or create the ...
0
votes
1answer
73 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 ...
5
votes
1answer
77 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 ...
3
votes
2answers
45 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
44 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
80 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 ...
0
votes
0answers
69 views
PostgreSQL 9.2.4 (Windows 7) - Service won't start, “could not load pg_hba.conf”
I am trying to get Postgres 9.2.4 to run as a service on Windows 7. After installing postgres, the service was running fine. However, after setting postgres up as a server for another program, the ...
0
votes
0answers
34 views
postgres-xc: ERROR: cannot set transaction read-write mode during recovery!
I am trying to make a HA in postgres-xc After creating an environment of streaming replication I am trying to create a table in default database as coordinator logged in. But it gives me the following ...
1
vote
1answer
64 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 ...
3
votes
1answer
24 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
83 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
54 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 ...
0
votes
0answers
28 views
Gathering data from independent databases to a central one
I need to do the following. In a LAN there are 4 postgres boxes each having a small postgres database. These servers are gathering data from the internet and insert them as records in a table in their ...
0
votes
1answer
53 views
How to install and configure Postgres-XC in windows?
Can anybody suggest me any resources of the complete installation procedure of postgres-XC in windows. I've installed postgresql-9.2 in my windows and can use it. Now I need to know the installation ...
2
votes
1answer
73 views
Is replication from SQL Server 2008 to PostgreSql possible?
Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
0
votes
1answer
189 views
Use cases for hstore vs json datatypes in postgresql [closed]
In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts:
You can nest with json; you can't with hstore
...
0
votes
1answer
90 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
2answers
114 views
Creating a PostgreSQL SERIAL column using pgAdmin3
When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?
Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
1answer
60 views
Postgres: Given some parent id's how can I SELECT X child records for each?
In Postgres, if I have :
Parent Table
id, name
1, Parent1
2, Parent2
.....
5, Parent5
Child Table
id, parent_id, name
1, 1, Child1
2, 1, Child2
.....
6, 2, Child6
.....
25, 5, Child25
so 5 ...
2
votes
2answers
106 views
Different queries result in big difference in performance
I have 2 similar functions, different way result in big diff in performance.
The PostgreSQL version: 9.2.1.
Function 1
create or replace function func_1() returns text as
$$
declare
v_i ...
0
votes
0answers
26 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
0
votes
1answer
49 views
Postgres sql dump import error
I'm facing a issue while importing the dump in my system, i'm getting the error as
" Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;"
and exiting with "out of memory"
We had ...
0
votes
1answer
208 views
which postgres do i have installed? what directory needs to be in my path to run initdb?
I need to use postgresql with ruby on rails.
I have these directories
/Library/PostgreSQL
and
/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2
This 2nd directory name is ...
1
vote
2answers
145 views
Recursive query using plpgsql
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.
I have a data table and a table to link the data together:
DATATABLE
-----------
id ...
1
vote
1answer
64 views
clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server
After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.
Below is the actual command:
...
1
vote
1answer
312 views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
0
votes
0answers
207 views
Install PostgreSQL 9.2 on Windows using WIN1252 encoding.
I had installed PostgreSQL 9.2 earlier and it always installed with the encoding being WIN1252 (the default database was WIN1252). I then some time ago reinstalled it with the encoding being UTF8 (I ...
0
votes
1answer
89 views
Postgresql Run 'Jobs' At Specific Times
I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute .
Scenario :
PostgreSQL 9.2
Need To Run A ...
2
votes
2answers
806 views
PostgreSQL user can not connect to server after changing password
I've met this with 4 roles I created:
After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more.
pgAdmin III show error message:
An error has occurred:
...
4
votes
1answer
94 views
Materialized View PostgreSQL
Is there any possibility of having built-in materialized views in PostgreSQL ?
I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something ...
2
votes
1answer
80 views
Running a TPC-C Benchmark Without sleep() i.e. key-in + think time
We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost ...
0
votes
0answers
195 views
Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability
I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability.
I have 1 master and 1 hot standby configured to replicate the data from ...
1
vote
1answer
237 views
Tuning Postgres config for new Database Server
I have finally gotten a Purpose Built Database machine for a project i'm working on.
The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives.
I have just got the database moved ...
0
votes
1answer
189 views
Ubuntu 12, PostgreSQL 9.2, PostGIS 2.0
At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2?
I checked their website and to my understanding it is not possible...
I hope that's not the case. Can anyone ...
0
votes
1answer
150 views
slow queries - set weight to token type in tsquery - postgresql
Postgresql version 9.2.3!
I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm ...
0
votes
1answer
477 views
Why is a new user allowed to create a table?
I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core:
postgres=# \l
List of ...
2
votes
1answer
96 views
Tools and methodologies to keep to DBs aligned [closed]
2 DBs having schemas that represent the same semantic objects.
The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres.
Once in a while ...
0
votes
1answer
85 views
Can this array group count query be improved?
So I have the following query
explain analyze
with tags as (
select unnest(tags) as tag_name from tasks where user_id = 1
) select
count(9),
tag_name
from
tags
...
3
votes
2answers
349 views
Struggling with pgBouncer
I've got Postgres 9.2 and PgBouncer (The version that came with the 'stack builder'). I have a .net application connecting via Npgsql version 2.0.12 on a win7 machine.
My application can happily ...
3
votes
2answers
196 views
Full Text Search With PostgreSQL
i have a table with this rows:
Stickers
------------------------------------------------------
ID | Title |Keywords (ts_vector)
------------------------------------------------------
...
1
vote
2answers
156 views
inet_client_addr in PostgreSQL don't return IP
i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return is '::1/128'.
This is a problem with my script or with the server?
CREATE OR REPLACE FUNCTION ...
0
votes
1answer
46 views
Basic Question on input to PostgreSQL
I have installed PostgreSQL on a Windows XP laptop so I can learn about using PostgreSQL. My problem is that the PostgreSQL tutorial starts of with the instruction
To create a new database, in this ...
3
votes
1answer
382 views
How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?
I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
1
vote
1answer
277 views
Lost postgreSQL password in Windows installation
I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password.
I looked at answers given to a similar question asked 6 months ago. The answer given there required the ...
