postgresql

PostgreSQL: Unknown Date Output Format. How Can You Convert Date Output Format?

I've been storing dates in a PostgreSQL 8.3 database table using a query resembling something like this: INSERT INTO files (date_uploaded) VALUES (now()); According to the documentation there are four formats that PostgreSQL 8.3 will output depending on the setup. My select statement is returning an unknown fifth format: 01:10:00.578...

How do I get detailed PostgreSQL-errors in Access via ODBC?

Is there a way to get the detailed error-messages PostgreSQL shows when running a query from command-line or a client like PG-Admin when using the ODBC-Interface-Driver? The Err-object in Access just tells you that something went wrong but is not very helpful - I have to run the same query again in PG-Admin to see where the problem is. ...

Transforming Results of PostgreSQL Query to XML, using PHP DOM

Hey everyone, Given SQL as an input, I have to query a PostgreSQL database and return the results as XML. I have done this with the following code: <?php $link = "host=localhost dbname=company user=pgsql password=password"; $connect = pg_connect($link); $query = "SELECT * FROM customer"; $result = pg_query($connect, $query); $doc =...

How can I access the points of the polygon stored in Postgres using Libpqxx?

I want to retrieve the points of a polygon that are stored in the postgres db. The contents of the db are: polygonid |vertices -----------+--------------------------------------------------------------------- 2 |((1,0),(1.5,-1),(2,-1),(2,1),(1,1),(0,0),(0,2),(3,2),(3,-2),(1,-2)) 4 | ((3,3),(4,4),(5,5)) The vertices ...

pgSQL or mySQL is more secure?

I want to know which database is more secure: mysql or pgSQL. Which ones support stored procedures? What are the advantages of one over the other? ...

PostgresSQL Connection Error once in a while

Hi all, Once in a while I am facing this error's in Postgres. org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. and org.postgresql.util.PSQLException: FATAL: sorry, too many clients already My database will work when i restar...

SUBSTR does not work with datatype "timestamp" in Postgres 8.3

I have a problem with the query below in postgres SELECT u.username,l.description,l.ip,SUBSTRING(l.createdate,0,11) as createdate,l.action FROM n_logs AS l LEFT JOIN n_users AS u ON u.id = l.userid WHERE SUBSTRING(l.createdate,0,11) >= '2009-06-07' AND SUBSTRING(l.createdate,0,11) <= '2009-07-07'; I always used the above query in ...

Combining XML with Reverse Paths

Hey everyone, I am looking for a way that I can query a database with multiple SQL Queries and then, once I have the results of the queries (in XML), merge the separate XML together into one XML document, using reverse paths. My reason for doing this is related to the concept of Parallel Details (same data, with different manipulations ...

Better way to write PL/pgSQL?

Is there a decent IDE-like tool for writing and debugging PL/pgSQL functions, e.g. for writing stored procedures? I find it an exercise in frustration using pgAdmin III, because the error messages are often deeply cryptic and things have a habit of failing in mysterious ways. ...

how do you ensure database Interoperability

I'm starting a new opensource project (for real estate) and wanted to focus on using MySQL, but would also like to ensure it works in PostgreSQL. What is the best way to doing this without having to continually test in both environments? I'm assuming the db schema is close to the same, but there could be some differences on the SQL scr...

Portable PostgreSQL for development off a usb drive

In order to take some development work home I have to be able to run a PostgreSQL database. I don't want to install anything on the machine at home. Everything should run off the usb drive. http://stackoverflow.com/questions/408511/what-development-tools-do-you-carry-on-your-usb-drive That question covers pretty much everything else,...

Considering move to PostgreSQL, feedback requested...

We are considering using PostgreSQL 8.4 on RHEL for some upcoming projects. We've been heavy users of MySQL since 3.23 (now on 5.1). The database servers are behind a load balanced cluster of web/app servers. Our usage is mostly business related web apps (user accounts with lots of emails / contacts / stats / projects / plans / task...

Do you observe PostgreSQL TEMP TABLE performance degradation since 8.3?

My app uses temporary tables in PostgreSQL. With new, 8.4 release I tested its performance compared to old 8.2 release and I observed that temp tables are ten times slower! Test of 8.3 release show that it was slower in 8.3 too. I compared configs of all bases and they are similar. All bases work on the same server. While my app uses JD...

Recursively build XML from PSQL Result Set (using PHP)

Hey everyone, I am building an XML document successfully with the following code: public function build($result) { $root = $this->append(new xmlElement('data')); $root->append(new xmlElement('collection')); while($row = pg_fetch_assoc($result)){ foreach($row as $fieldname => $fieldvalue){ $second = $roo...

storing uploaded photos and documents - filesystem vs database blob

My specific situation Property management web site where users can upload photos and lease documents. For every apartment unit, there might be 4 photos, so there won't be an overwhelming number of photo in the system. For photos, there will be thumbnails of each. My question My #1 priority is performance. For the end user, I want...

How can I create a polygon using fields in PostgreSQL?

I have 8 real values in a table that I'd like to combine into a polygon. I haven't been able to figure out how to create a polygon using these values though. I keep trying variations of SELECT polygon(lat1,lon1,lat2,lon2,lat3,lon3,lat4,lon4) FROM table; but keep getting errors about the polygon function not existing or an invalid inpu...

Changing tables datatype from varchar to text postgressql

I have a problem and search for a solution on Google but can't find any. I have a posgre Tabel with name products_199 and in that table there's a column with name parameter2 type varchar (255). I want to change the datatype to text but somehow I get the following error ERROR: parser: parse error at or near "TYPE" at character 50 My ...

Limits on PostgreSQL schema changes inside transactions?

My database background is with Oracle, so I was surprised to discover that Postgres includes schema changes in transactions - if you begin one, create a table and then rollback, the table goes away. It works for adding and removing columns as well. Obviously this is very nice. We're about to make some changes to the way we deploy schema...

Insert, on duplicate update (postgresql)

Several months ago I learnt from here how to perform multiple updates at once in MySQL using the following syntax INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2); I've now switched over to PostgreSQL and apparently this is not correct. It's re...

Why won't postgresql store my entire (PHP) float value?

I try to store the PHP floating point value 63.59072952118762 into a double precision column in postgres. Postgres stores the value as 63.59073. Does anyone know why? 8 byte should be more than enough for that value. I've tried with the data type numeric, which works when specifying the precision, but that shouldn't really be necessary. ...