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 ...

learn more… | top users | synonyms (1)

0
votes
1answer
26 views

accessing fields of returned record object

I need to use a function which exists on another database and was able to figure out how to access it via dblink. The function returns the name of an encryption algorithm and the key used for it, as ...
1
vote
1answer
21 views

Handling exceptions from inside a function

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
1
vote
0answers
40 views

Debugging Postgres 9.3 Triggers

Right now I have a trigger in my database that is giving me some issues. I've set up this line so I can check what is being fetched from a query RAISE NOTICE 'hstore_key = %',hstore_key; as well I ...
3
votes
0answers
56 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
2
votes
1answer
69 views

Most efficient way to do many UPDATES in Postgresql

I have to update cca 2.5 milion rows in a table with cca 20 milion rows. The updated column value is based on value of another column, but another select is required to determine it's value. The ...
2
votes
1answer
56 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
2answers
160 views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
3
votes
2answers
65 views

Removing all columns with given name

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion. When inserting records ...
1
vote
1answer
59 views

PostgreSQL: Another seq scan where an index scan is expected

Here's the query: SELECT DISTINCT ON(points.track_id) participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, ...
1
vote
1answer
107 views

Returning rows in PostgreSQL with a table called “user” [duplicate]

I have a table called user I have some SQL that is select User.* from User This fails because User is a reserved word however, I was under the impression that Postgresql didn't care about the case ...
3
votes
2answers
525 views

Is Data Warehousing possible in MySQL and PostgresSQL [closed]

I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I ...
0
votes
1answer
34 views

Change permissions of postgres copy to exported file

I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here. The original question is: ...
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
0answers
25 views

Postgresql Full Text Search sequence of strings

What I'm trying to do is that i have a bunch of html documents in which I need to search for certain groups of strings. I did build a ts_vector for all htmls but I seem not be able to find out if it ...
0
votes
0answers
25 views

Best way to setup postgresql load balancing and failover?

Ok so I know that my db is going to be postgres and I want to ensure that it is able to handle read/write queries in the future (instant messaging application so lots of small writes and a lot more ...
1
vote
0answers
79 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1. The scenario is: I receive heavy insertion into Table1 (about 100 rows per sec). For each new row, I have to check the previous and next row of the source table if those are ...
5
votes
1answer
60 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
0
votes
0answers
51 views

Reset password for 'postgres' on v9.3 (Win Srv)

I'm trying to reset the password of the user 'postgres' on a postgres 9.3 installation, but have been unable to do so... I've tried every combination in pg_hba.conf I could find online (ex. 'host all ...
1
vote
1answer
77 views

Best way to add Foreign Key to massive table?

Say I have 4 tables: arrival (~100 million rows), departure (~100 million rows), airport (~30 million rows), airplane (small). arrival and departure each have 2 ForeignKeys: one to airport, one to ...
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 ...
5
votes
1answer
190 views

Rounding issues?

Try this: create table test (f float); insert into test values (330.0); commit; select (8 + 330.0/60)::int; --14 select (8 + f/60)::int from test; --14 select (9 + 330.0/60)::int; --15 select (9 + ...
0
votes
0answers
26 views

What to do if pg_cancel_backend and pg_terminate_backend return False?

I cannot end/terminate the queries shown in the screenshot. The functions are not running anymore (usually they cause a high load on the server and the server was fine). I have tried both ...
2
votes
1answer
37 views

Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API. I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via ...
1
vote
1answer
41 views

What's the best solution for offline db syncing

I'm building a system which requires a master db (BIG master) that contains things like accounts, balances, actions, etc. This db then needs to be accessible at a physical place, like a fairground ...
1
vote
0answers
23 views

Postgres_Failover Not Working

I was able to configure and Replicate between master and Slave ,so when we insert anything on master db it will automatically replicate to slave, Now,requirement is that if we shutdown master can we ...
1
vote
0answers
48 views

Getting vacuumdb :invalid page header in block error while vacuuming

I am newbie to PostgreSQL and using version 9.2.Disk space for data folder is full causing DB to stop. When i am trying to perform a vacuum to reclaim space, i am getting below error. vacuumdb: ...
0
votes
0answers
16 views

Need separate columns that calculates percentage [duplicate]

I have the following table: NAME ------ Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage ------------------------- Alex 2 33% Bob 2 33% ...
2
votes
6answers
60 views

Need separate columns that calculate percentage

I have the following table: NAME Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage Alex 2 33% Bob 2 33% Roger 1 16.6% Tim 1 ...
0
votes
1answer
43 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
1
vote
1answer
46 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
1
vote
0answers
39 views

listen_addresses does not respect postgresql.conf

I have PostgreSQL 9.3, and I'm trying to set it up on CentOS. One part that has been driving me nuts is the listen_addresses setting. I set it to * inside postgresql.conf: listen_addresses = '*' ...
0
votes
1answer
685 views

psql: could not connect to server: No such file or directory

The error in its entirety reads: psql: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket ...
0
votes
1answer
29 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
0
votes
0answers
31 views

API on top of postgresql

I'm looking for one or several API's that can be put on top of postgres to make data input more dynamic. What I'm looking for is something that can handle a wider range of protocols and differently ...
5
votes
1answer
14 views

Postgresql 9.1 add nullable column costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel query because of lock time. ALTER TABLE mytable ADD ...
1
vote
1answer
27 views

create trigger hangs

I have a trigger function that just copies inserted values to another table. Then, each night I need to drop and recreate this trigger for some export operation. --trigger function CREATE OR ...
0
votes
1answer
25 views

Separation of users columns in another table

I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification ...
0
votes
3answers
68 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
1answer
22 views

Type of an id field that references another id field

I'm on postgresql. I have a table user (id serial not null) and another table client(id) i need to create a : foreign key client(id) references user(id). What the client(id) type should be? Int or ...
0
votes
3answers
42 views

postgresql cannot drop a table when autovacuum holds lock on it

Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE ...
1
vote
2answers
90 views

How do I maintain high INSERT-performance on PostgreSQL

I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database. At the core is a table (partitioned by month) which contains a row for each measurement point: ...
0
votes
0answers
47 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
36 views

Speed up sorting in this query

I'm trying to optimize the following plan and noticed that PostgreSQL spends a lot of time (relatively speaking) on sorting before the merge joins. Is there a way (using indexes, cluster the table, ...
0
votes
1answer
46 views

What's the difference between INSERT … SELECT and SELECT INTO

Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is WITH subset AS (SELECT ...) INSERT INTO ...
0
votes
1answer
99 views

What is the best approach to process huge amount of data insertion efficiently

I'm dealing with a HUGE amount of data entry. I need to know the best practice to insert/update table records after new entry inserted in another table. By huge i mean we get more than 2 million ...
1
vote
1answer
54 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
1
vote
0answers
39 views

How to restore everything (including “postgres” role) from pg_dumpall backup

I have a postgres 9.1 server running and I do backups with pg_dumpall -c -f /nfs/backup/fulldump.sql I'm testing restoring the backup to another server with psql -f /nfs/backup/fulldump.sql ...
1
vote
3answers
100 views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
0
votes
2answers
48 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
0
votes
1answer
28 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...