postgresql

Can't Connect to PostgreSQL with PHP pg_connect()

EDIT: I just realized that this question may be better suited to ServerFault. Instead of copying it, a moderator please move it over? Thanks. I've checked php-info, and the Postgresql extension is there (pg_connect() is not undefined). I am also able to connect to postgresql using psql on localhost (I've edited my pg_hba.conf file appro...

How to configure a Google Web Toolkit application with Datanucleus + PostgreSQL

Hello there! =) I created an application using GWT and support for the Google Datastore but now i'm trying to move my application to my own server and i'm also trying to detach my app from Google App Engine and from Datastore. To be more precise: I want to stop using Google Datastore and start using JDO and Datanucleus but with Postgre...

Setting up Postgresql to work with my local dev

I have Snow Leopard and am using the default apache. I installed postgresql via macports. So now I can do a psql from a terminal (I tried but failed to do a install of php5 through macports but there was an error) I have enabled php5 and restarted the apache so now I have php. But for the life of me I cannot figure out to hook up postgr...

PostgreSQL Math Function Error

I'm getting an error from a deterministic PG function that seems dependent based on when I call it in a query. The function is: CREATE OR REPLACE FUNCTION ircm(starting_money numeric, value numeric, days numeric) RETURNS numeric AS $$ BEGIN -- Calculate effective interest rate, compounded monthly. RETURN 12*(pow((value/starting_mon...

noSQL for storing product information?

Hi everybody, We are about to solve a customer's need of a web based application that holds a big amount of products and their data - including prices, weights, physical volyme, and much more. Everything but the prices are data that is data that will be stored once, and then probably not changed. The prices on the other hand will be u...

Ruby-on-rails + Postgres: configuration issue

Hello guys, I'm having some troubles after installing in Windows 7 ruby 1.8.6, rails 2.3.8, some basic gems(also ruby-postgres) and the IDE Rubymine from Jetbrains. So, after creating a simple project with Rubymine(default PostgresSQL configuration in database.yml), I run it in localhost:3000 but it seems not be recognizing nothing lik...

Use PostgreSQL Domain and Struct types in Java

After creating EntityManagerFactory instance, I receive error message: ... Exception Description: Predeployment of PersistenceUnit [aPU] failed. Internal Exception: Exception [EclipseLink-7157] (Eclipse Persistence Services - 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.ValidationException Exception Description: Entity cla...

How can I check a Python unicode string to see that it *actually* is proper Unicode?

So I have this page: http://hub.iis.sinica.edu.tw/cytoHubba/ Apparently it's all kinds of messed up, as it gets decoded properly but when I try to save it in postgres I get: DatabaseError: invalid byte sequence for encoding "UTF8": 0xedbdbf The database clams up after that and refuses to do anything without a rollback, which will be...

How to add parameter for postgres enum types in C#

Hi currently have the problem, that I want to insert values in a postgres database where the table contains self defined types like. CREATE TYPE TestEnum AS ENUM ('Value1','Value2'); When I try to add a parameter in C# I always get an error because of the wrong NpgsqlDbType. So my question is what NpgsqlDbType to use for such self def...

Postgresql vs. MySQL: how do their data sizes compare to each other?

For the same data set, with mostly text data, how do the data (table + index) size of Postgresql compared to that of MySQL? Postgresql uses MVCC, that would suggest its data size would be bigger In this presentation, the largest blog site in Japan talked about their migration from Postgresql to MySQL. One of their reasons for moving aw...

psycopg2 COPY using cursor.copy_from() freezes with large inputs

Hi, Consider the following code in Python, using psycopg2 cursor object (Some column names were changed or omitted for clarity): filename='data.csv' file_columns=('id', 'node_id', 'segment_id', 'elevated', 'approximation', 'the_geom', 'azimuth') self._cur.copy_from(file=open(filename), table=self.new_...

ActiveRecord/NHibernate DateTime problem

I'm using ActiveRecord/NHibernate for ORM mapping in my project with PostgreSQL 8.4. I have following issue For class mapping [ActiveRecord(Schema = "public", Table = "test_bean")] public class TestBean { [PrimaryKey(SequenceName = "test_bean_id_seq", Generator = PrimaryKeyType.Sequence)] public int ID { get; set; } [Castle.A...

Magical Object Database needed with .Net bindings

Hello, Lets assume im writing a program, my program has its own entities and such. And since I'm using an OOP .Net language (C#, but could be F# or Iron Python), my objects have weird OOP traits like inheritance, references to other objects, and hierarchical relationships. Naturally i want to save these all to my God damn database, an...

Funny characters in my db

My web app is breaking when I try edit a certain content type and I'm pretty sure it is because of some weird characters in my database. So when I do: SELECT body FROM message WHERE id = 666 it returns: <p>⢠<span></span></p><p><br /></p><p><em><strong>NOTE:</strong> Please remember to use your to participate in the discussion.</em>...

How do I move a database from one server to another in PgSQL?

I am trying to move a database from my old server to a new server. Any help would be appreciated. ...

ERROR: unterminated quoted string at or near

While executing below shown trigger code using ANT I am getting the error org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer" Position: 57 I am able to sucessfully execute the below code through PGADmin (Provided by postgres) and command line utility "psql" and the trigger functi...

Efficient way of phrasing multiple tuple pair WHERE conditions in SQL statement

I want to perform an SQL query that is logically equivalent to the following: DELETE FROM pond_pairs WHERE ((pond1 = 12) AND (pond2 = 233)) OR ((pond1 = 12) AND (pond2 = 234)) OR ((pond1 = 12) AND (pond2 = 8)) OR ((pond1 = 13) AND (pond2 = 6547)) OR ((pond1 = 13879) AND (pond2 = 6)) I will have hundreds of thousands pond1-po...

PostgreSQL UNIQUE index not unique?!

I have a table with a 12-column UNIQUE index. \d sales shows sales_uq UNIQUE, btree (a1, a2, a3, ... a12). I do the following query: SELECT a1, a2, a3, ... a12 FROM sales GROUP BY a1, a2, a3, ... a12 HAVING count(1) > 1; and I get a bunch of results. How is that possible?! Is it possible that the index is there but somehow disabled? ...

Postgresql pg_dump, doesnt work well on dumping and restore schema

dear comrades, I use code belows for dump a specific schema, before dumped it has 4 views on those schema. # pg_dump -U username -h host -n schema_name > some_schema.dump Then I restore it with psql tools # \i /path/to/mydump/some_schema.dump After psql finish his restore process , i just only got 3 views, i have no idea where th...

How to Improve UPDATE query result time in Postgresql?

I have the following query running for 3hours+: UPDATE eop_201007 set coord_x = gi.x_etrs89, coord_y = gi.x_etrs89,gr_type = 4 from eop_201007 as eop, geoindex201001 as gi where eop.cp7=gi.cp7 AND eop.gr_type=0; eop table has 300k+ records, and gi table 100k+. The cp7 field is indexed in both tables, and this is taking way too much t...