PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
0
votes
1answer
43 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 ...
3
votes
1answer
145 views
In Postgres, How can I merge two tables?
I have a set of web services that all need updating with a new set of static data.
I can't use a distributed transaction, but would like to be able to ask each service to rollback it's changes if I ...
4
votes
1answer
120 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
3
votes
1answer
115 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
1
vote
3answers
70 views
PostgreSQL newbie - how to create objects in a database
I've just finished installing PostgreSQL and pgadmin3 on Ubuntu. Using pgadmin, I've been able to create a new user called 'test' and also a new database with 'test' as the owner.
Now I'm trying to ...
3
votes
3answers
109 views
Is a REINDEX required after CLUSTER?
I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications ...
0
votes
0answers
49 views
Define default operator class for gist on enum data type
I have my own enum data type (called direction), which can be IN or OUT and I want to use EXCLUDE USING gist constraint to prevent inserting row with the same direction (and two more columns) like ...
3
votes
3answers
218 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
1
vote
0answers
43 views
PGPool Frequent Queries
We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the ...
3
votes
2answers
49 views
disallow SET command in a postgresql server
Im planning to publish my postgresql server to a few untrusted clients.
I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the ...
1
vote
1answer
40 views
POSTGRES DB Server Blunder
This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive.
Is it possible to recover ...
0
votes
3answers
86 views
Incremental backup in postgresql
I want to migrate a production database from one linux server to other.
I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum ...
4
votes
2answers
141 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
2
votes
1answer
46 views
How to turn off all RAISE NOTICE on production server?
I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
6
votes
2answers
28 views
Postgres performance difference on insert
I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions?
I went quite straight forward way (i know that it is not ...
0
votes
1answer
120 views
Object-oriented programming CRUD design pattern
At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
1
vote
1answer
55 views
How to grant only functions while revoking tables?
I am a newbie to PostgreSQL, and configuring a new database.
I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table.
GRANT CONNECT ON ...
0
votes
1answer
38 views
Postgres 9.1 wait events
Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle?
I need to find queries which are waiting for a long time, and the events they are blocked on.
1
vote
0answers
44 views
Write performance of Postgresql 9.1 with read-only slave
I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
0
votes
0answers
83 views
How I can copy from local file to remote DB in PostgreSQL?
I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?
I am using the following command
\COPY test(user_id, product_id, value)
FROM '/Users/testuser/test.tsv' ...
0
votes
1answer
27 views
Comparing two tables for a UUID change and fix it
I have two tables which I'm trying to reconcile the differences of in postgresql.
Table A is old and needs updating.
Table B is an updated, schema identical version of Table A which I have the data ...
1
vote
1answer
81 views
Postgresql querying trends
Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches
So I have a database which records motor races, with the ...
0
votes
1answer
46 views
Set field values to newly imported rows in PostgreSQL table with existing data
I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.
Question: For the newly imported ...
4
votes
2answers
170 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
0answers
65 views
How to execute a non-table-locking update operation on PostgreSQL? [closed]
Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
-4
votes
1answer
30 views
Postgresql output doubt [closed]
I am getting output and I have to press spacebar after one page of output, I want to get rid of that, how can I and which option I have to enable.
By automatically, how can I automatically execute ...
3
votes
1answer
108 views
Most efficient to prevent duplicates
I'm building an application that will fetch messages with JSON every once in a while and store these in a postgres db. Now I want to prevent duplicates, so I calculate the sha-512 of the message and ...
1
vote
1answer
26 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
2
votes
1answer
80 views
Limiting number of results in a Partition using OVER(PARTITION BY)
In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
0
votes
1answer
45 views
Very fast replication
I used this guide to make replication between my two db servers : http://wiki.postgresql.org/wiki/Streaming_Replication
But, when I updated a row on master server it will be available in slave ...
0
votes
1answer
179 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 ...
0
votes
1answer
76 views
Getting error while importing large data through csv
I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.
I want to disable all of the database constraints temporarily - I do not know how to do this.
0
votes
1answer
134 views
How to use array variable in query in PostgreSQL
Create table t1 ( xcheck varchar[], name text );
CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
RETURNS record AS
DECLARE xrc as record;
execute 'select name from t1 where xcheck @> ...
1
vote
1answer
39 views
Can pg_hba.conf trust processes?
Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?
2
votes
2answers
97 views
Postgres: Use result of group by as column names
I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many ...
2
votes
1answer
40 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
6
votes
1answer
600 views
Using MongoDB and PostgreSQL together
My current project is essentially a run of the mill document management system.
That said, there are some wrinkles (surprise, surprise). While some of
the wrinkles are fairly specific to the ...
4
votes
1answer
90 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
-1
votes
1answer
69 views
Passing argument in trigger dynamically
CREATE TRIGGER audit_proc_tr
AFTER INSERT OR UPDATE OR DELETE
ON "log".hi
FOR EACH ROW
EXECUTE PROCEDURE "log".audit_proc(argument);
CREATE OR REPLACE FUNCTION ...
2
votes
0answers
35 views
Postgres caching and bytea
I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB ...
0
votes
0answers
74 views
REST webservice as a Foreign Data Wrapper in postgresql
I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
1
vote
0answers
38 views
MySQL Federated acces to PostgreSQL table
For example 2 DBases:
DBMS Postgresql
DBMS MySQL
In each DB include one DB and one table in it. Can I work with table in PostgreSQL through MySQL?
Is it possible to link as federated postgresql ...
2
votes
1answer
107 views
How to make postgresql SUM to be more accurate on large amount of floating point data?
I'm trying to SUM 8 million floating point (REAL) values with simple query like this:
SELECT SUM(metric) FROM metrics;
However, it returns very inaccurate result.
It should return 137,586.77, but ...
0
votes
0answers
39 views
PostgreSQL : Londiste issue
I am new to Londiste， And I want to install Londiste, accroding the
Londiste Tutorial after I intall skytool (http://pgfoundry.org/frs/?group_id=1000206) , I can not find the pgqadm.py script ， ...
1
vote
0answers
37 views
Inheritance and its alternatives in PostgreSQL
I make an archaeological database in PostgreSQL 9.2. The description of archaeological context has a subdivision between different types of context. I'm not sure how to implement it:
1) to violate ...
2
votes
2answers
78 views
Postgres RIGHT JOIN with custom array
I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following:
SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)
...
0
votes
1answer
162 views
Error: set_valued function called in context that cannot accept a set. What is it about?
I use Postgresql 9.1, with ubuntu 12.04.
Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
0
votes
1answer
75 views
How to turn the foreign key constraints on and off while db restore in postgres?
After using below the backup cmd in a db with 250+ tables:
pg_dump -h localhost -p portNum -U userName -f fileName -o dbName
I tried restoring it with the following command:
psql -h localhost -p ...
1
vote
1answer
87 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
1
vote
1answer
56 views
Postgres continuous backups - should WAL numbers be sequential after pg_start_backup call?
I'm using PostgreSQL WAL archiving and pg_start_backup as outlined in the Continuous Archiving section of the Postgres manual to backup a large database. Twice a month we do a full backup and every ...
