PostgreSQL vs Postgres Plus
Does anyone know what substantial differences there are between the regular PostgreSQL (www.postgresql.org) and Postgres Plus (http://www.enterprisedb.com/products/postgres_plus.do)? ...
postgresql
Postgres - how to return rows with 0 count for missing data?
I have unevenly distributed data(wrt date) for a few years (2003-2008). I want to query data for a given set of start and end date, grouping the data by any of the supported intervals (day, week, month, quarter, year) in PostgreSQL 8.3 (http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC). The prob...
In PostgreSQL, Is It Faster To Include TEXT Columns In The Same Table, Rather Than A Separate Table?
Which design do you think runs faster on PostgreSQL? A. Making a 15 column table of varchars and the like, but putting all TEXT columns in a separate table with an fkey link back to this table. And let's imagine you want to search for the record with ID of "4" but then pull all the rows back, including the stuff from the TEXT columns in...
Optimal DB structure for additional fields entity
I have a table in a DB (Postgres based), which acts like a superclass in object-oriented programming. It has a column 'type' which determines, which additional columns should be present in the table (sub-class properties). But I don't want the table to include all possible columns (all properties of all possible types). So I decided to ...
Postgres Best Practice
What are your set of best practices when working with PostgreSQL? I'm interested in every aspect of postgres-management/development. Configurations, rules, functions, users, ... There are a lot of documentation about PostgreSQL, but I haven't found any clear best-practice/cookbook source yet. Just to clarify: I know about views, transa...
Prevent stemming of proper nouns in PostgreSQL?
In its enthusiasm to stemm tokens into lexemes, PostgreSQL Full Text Search engine also reduce proper nouns. For instance: essais=> select to_tsquery('english', 'bortzmeyer'); to_tsquery ------------ 'bortzmey' essais=> select to_tsquery('english', 'balling'); to_tsquery ------------ 'ball' (1 row) At least for the first one, I'm s...
What is PostgreSQL for developer best book
I know it's kind of lame question, but I need to learn how to improve my usage of this DBMS and I need a book (programmers do read books!). It's quite expensive and time-consuming to order form Amazon from my country so I'd like to take a best choice, I can. So, what is the best PostgreSQL book for a developer? PS For reference, I tre...
What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?
What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres? ...
How to profile PostgreSQL Database?
I want to profile (keep an Eye on) all the activities that goes on in a Database which is in PostgreSQL. Is there any such utility which will help me do this? ...
I'm a beginner at PostgreSQL, How do I create a simple counter which I can reset?
Hello! I want to achieve the following: ID | Counter ------------ 0 | 343 1 | 8344 Now say that I want to update counter for ID 1,,, what is the easiest way to do it? Do I use sequences? do I simply read the value and update? Is there any special type for it? I was thinking about using sequence but then I have to create one for ea...
Tips on Speeding up JDBC writes?
I am writing a program that does a lot of writes to a Postgres database. In a typical scenario I would be writing say 100,000 rows to a table that's well normalized (three foreign integer keys, the combination of which is the primary key and the index of the table). I am using PreparedStatements and executeBatch(), yet I can only manag...
PostgreSQL changing returned rows order
I have a table named categories, which contains ID(long), Name(varchar(50)), parentID(long), and shownByDefault(boolean) columns. This table contains 554 records. All the shownByDefaultValues are 'false'. When I execute 'select id, name from categories', pg returns me all the categories, orderer by its id. Then I update some of the ro...
Out Of Memory using Postgres in Java
Hi. Well, we have a web app, running over JBoss and we're having an "OutOfMemory" error when trying to insert a lot of rows in several tables of a postgres DB. This is the complete environment for this error: * JBoss 4.3.x GA * Java 1.6.0 * Hibernate 3.0 * postgreSQL-8.3 (driver) About actual code-work environment: * The heavy part abou...
284 could not be located in the dynamic link library SSLEAY32.dll
Postgres 8.3 is installed on a windows 2008 server. Ruby 1.8-6 installed. gem install ruby-postgres. When trying a simple connect I get ruby.exe - Ordinal Not Found The ordinal 284 could not be located in the dynamic link library SSLEAY32.dll. OK There seems to be some conflict betweeen the ssleay32 shipped with postgres 8.3 an...
How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?
How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin? ...
Which JDBC jar should I use with java 1.5.0_16 and PostgreSQL 8.3.5?
Title says it all: Which JDBC jar should I use with java 1.5.0_16 and PostgreSQL 8.3.5? ...
Why can't I connect to postgres from Perl?
I believe I have set up Pg properly, but my script doesn't seem to be connecting to the database. I am testing with: $database="networkem"; $user="postgres"; $password=""; $host="localhost"; $dbh = DBI->connect("DBI:Pg:dbname=$dbname;host=$host", $user, $password); My pg_hba reads: host all postgres 127.0.0.1 255.255.25...
Problem looping data in a postgres function using %ROWTYPE
Hi all... I am trying to create a function in postgres that retrieves data from one table and inputs into another. I am using the %ROWTYPE type to store the temporary data from the select statement and then iterate through it with an insert statement but have been unsuccessful! The following is my code : CREATE OR REPLACE FUNCTION rm_...
SELECT on a nullable reference
Hello, I have a relationship between two tables, authors and styles. Every author is associated with a style, with the special case where an author doesn't have a style (IS NULL). There's no problem in setting the reference to NULL, but there's a problem doing a query to select the authors and styles. For example, the query: SELECT ...
Select unlocked row in Postgresql
Is there a way to select rows in Postgresql that aren't locked? I have a multi-threaded app that will do: Select... order by id desc limit 1 for update on a table. If multiple threads run this query, they both try to pull back the same row. One gets the row lock, the other blocks and then fails after the first one updates the row....