PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
2answers
452 views

sum of double precision gives weird results

I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making ...
0
votes
3answers
1k views

get row count of last select query

I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs: COPY (SELECT ... FROM ...
0
votes
1answer
142 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement this? ...
0
votes
0answers
41 views

Suppression of statement in Postgresql logging

Given the following SQL function: CREATE OR REPLACE FUNCTION log_a_notice() RETURNS void AS $BODY$ BEGIN RAISE NOTICE 'This message is coming from log_a_notice()'; END; $BODY$ LANGUAGE plpgsql ...
4
votes
2answers
142 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
1
vote
1answer
302 views

Can a PostgreSQL Trigger be used to ignore an insert?

I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database. I was hoping that while testing ...
-1
votes
1answer
53 views

Permission denied when importing a database

This is my first time here and i have a question. I am working with postgresql on windows 8 for the first time. I am trying to import a database into postgresql with the command line. Type in: psql ...
0
votes
1answer
53 views

Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters

I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais ...
1
vote
1answer
1k views

Why use RDS over EC2 for Postgres?

I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up. Pricing: I can get an EC2 c3.large instance with two SSDs (16GB each), two ...
3
votes
2answers
266 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
1
vote
1answer
492 views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
2
votes
2answers
104 views

Does the system process slow down my Database access?

I have a Fairly large DB containing mostly html documents. I wrote a python script that fetches always 1000 html documents at once from my database cur.execute("SELECT id,url,html_file FROM html ORDER ...
3
votes
4answers
302 views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive ...
0
votes
1answer
31 views

Solutions to migrate databases

I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql ...
1
vote
1answer
164 views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
3
votes
1answer
3k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
2
votes
1answer
497 views

Handling user logins while using a multiple schema design to support multi-tenancy

I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I ...
0
votes
1answer
755 views

Delete returns nothing in Psycopg2?

I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2. Take the following minimal example: def testDelete(): db = DbInterface() cur = ...
0
votes
0answers
27 views

Cascading pgbouncer setup?

I am wondering if anyone has tried a cascading pgbouncer setup, where pgbouncer A connects to pgbouncer B and that in turn connects to postgres. From 1Km away this seems a possible setup, but I am ...
3
votes
2answers
313 views

Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

Per the PGSQL 9.3 docs: Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on ...
1
vote
3answers
58 views

How do I keep a database physically secure? [closed]

This is more of a conceptual question. I am learning how to integrate Java and databases (using PostgreSQL at the moment) and my initial questions is: What are the guarantees that the data I ...
2
votes
2answers
322 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
0
votes
1answer
220 views

repmgr: Can I replicate databases from two different masters to a third slave?

If I have a master node 1 (with database A) and a master node 2 (with database B) can I use repmgr and have both A and B replicated to a hot standby node 3 ? [ EDIT: They would all be running ...
2
votes
0answers
69 views

Can you force pgpool to require SSL connections?

Can you force pgpool to drop non-ssl connections? I know you can with postgresql (Force PostgreSQL clients to use SSL?), but dropping that same syntax (hostssl instead of host) results in a ...
0
votes
1answer
34 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
33 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
325 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
199 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
184 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
455 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 / ...
5
votes
2answers
5k 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
76 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
86 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
507 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 ...
5
votes
2answers
5k 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
453 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
183 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
36 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
161 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
81 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 ...
7
votes
1answer
208 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
111 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
91 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
110 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
272 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
141 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
383 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
158 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
31 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
247 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: ...