I have certain initializing functions that I use to set up audit logging on the DB server side (ie, not rails) in PostgreSQL. At least one has to be issued (setting the current user) before inserting data into or updating any of the audited tables, or else the whole query will fail spectacularly. I can easily call these every time befor...
Background: I have a PostgreSQL (v8.3) database that is heavily optimized for OLTP. I need to extract data from it on a semi real-time basis (some-one is bound to ask what semi real-time means and the answer is as frequently as I reasonably can but I will be pragmatic, as a benchmark lets say we are hoping for every 15min) and feed it...
http://stackoverflow.com/questions/1152405/postgresql-is-better-using-multiple-databases-with-1-schema-each-or-1-database I am new in schema concept for PostgreSQL. For the above mentioned scenario, I was wondering Why don't we use a single database (with default schema named public) Why don't we have a single table, to store multipl...
I have written the following function but it's isn't returning anything when I run it. Can somebody help identify the issue? CREATE OR REPLACE FUNCTION GenerateReadableRandomString ( len INT ) RETURNS varchar AS $$ DECLARE validchars VARCHAR; randomstr VARCHAR; randint INT; i INT; BEGIN validchars := 'ABCEFHJKLMNPRTWXY3478'; i := 0; ...
I am building an automation test suite which is running on multiple machines, all reporting their status to a postgresql database. We will run a number of automated tests for which we will store the following information: test ID (a GUID) test name test description status (running, done, waiting to be run) progress (%) start time of t...
Hi, I need some ideas for a weekend project about Hadoop and OpenStreetMap. I have access to AWS EC2 instance with OpenStreetMap snapshot in my EBS volume. The OpenStreetMap data is in a PostgreSQL database. What kind of MapReduce function can be run on the OpenStreetMap data, assuming I can export them into xml format, and then place...
I'm trying to load a bunch of latitude/longitude pairs into a PostGIS geography type so as to be able to query by location. In particular I have a table with float latitude and longitude columns and a geography(Point, 4326) column. I would like to do update mytable set geography = ??? The documentation appears to suggest that the fo...
I'd like to do something similar to what's described in http://www.bestbrains.dk/Blog/2010/03/25/HowToAssertThatYourSQLDoesNotDoFullTableScans.aspx but for that I'd need postgres to keep track of any full table scans it does. Is there such a thing for postgres? ...
Scenario Multiple application servers host web services written in Java, running in SpringSource dm Server. To implement a new requirement, they will need to query a read-only PostgreSQL database. Issue To support redundancy, at least two PostgreSQL instances will be running. Access to PostgreSQL must be load balanced and must auto-...
I'm a J2EE developer & we are using hibernate mapping with a PostgreSQL database. We have to keep track of any changes occurs in the database, in others words all previous & current values of any field should be saved. Each field can be any type (bytea, int, char...) With a simple table it is easy but we a graph of objects things are m...
Is it possible to loop through a query so that if (for example) 500,000 rows are found, it'll return results for the first 10,000 and then rerun the query again? So, what I want to do is run a query and build an array, like this: $result = pg_query("SELECT * FROM myTable"); $i = 0; while($row = pg_fetch_array($result) ) { $myArray[$...
** EDIT ** Nevermind, just needed to take out the parens... I get this error: ERROR: could not identify an ordering operator for type record when trying to use DISTINCT Here's the query: select DISTINCT(g.fielda, g.fieldb, r.type) from fields g LEFT JOIN types r ON g.id = r.id; And the errors: ERROR: could not identify an ord...
I'm considering using postgreSQL 8.4x db on windows/Mac for development and linux for production. wondering what front-end tools are out there that are comparable to Toad (for Oracle) ? PostgreSQL comes with PgAdminIII. It's OK but I feel there might be something better than that. I prefer free or open source but if something is NOT too...
I'm working on PostgreSQL 8.4 in read committed mode. I know that for each query, the server makes a snapshot of db state so that the query behaves consistently. Does it include triggers that are called in response to this query? Or is there a new snapshot created for each query called from within a trigger? ...
The function below keeps returning this error message. I thought that maybe the double_precision field type was what was causing this, and I tried to use CAST, but either that's not it, or I didn't do it right... Help? Here's the error: ERROR: input is out of range CONTEXT: PL/pgSQL function "calculate_distance" line 7 at RETURN *...
We have a PostgreSQL database. And we have several tables which need to keep certain data in several languages (the list of possible languages is thankfully system-wide defined). For example lets start with: create table blah (id serial, foo text, bar text); Now, let's make it multilingual. How about: create table blah (id serial, f...
I would like to perform something similar to this (ie get the sum of distinct event amounts in a payment table then group the payments by event details and total money paid for them. Also getting users and what they have paid for an event will be done) in Django using PostgreSQL. My models are as below: class UserProfile(User): on...
I have a table that looks something like this: id | firstperson | secondperson 1 | jane doe | 2 | bob smith | margie smith 3 | master shifu | madame shifu 4 | max maxwell | I'm trying to count all of the firstpersons + all of the secondpersons, if the secondpersons field isn't blank... Is there a way to do that? ...
Ok... changing the question here... I'm getting an error when I try this: SELECT COUNT ( DISTINCT mid, regexp_replace(na_fname, '\\s*', '', 'g'), regexp_replace(na_lname, '\\s*', '', 'g')) FROM masterfile; Is it possible to use regexp in a distinct clause like this? The error is this: WARNING: nonstandard use of \\ in a string lit...
Just a little confused here... I have a function in postgres, and when I'm at the pg prompt, I just do: SELECT zp('zc',10,20,90); FETCH ALL FROM zc; I'm wondering how to do this from php? I thought I could just do: $q = pg_query("SELECT zp('zc',10,20,90)"); But, how do I "fetch" from that query? ...