I have a Django project, and I'm somewhat of a newbie in it. I have the following PyUnit test trying to save an object into a PostgreSQL database: import unittest from foo.models import ObjectType class DbTest(unittest.TestCase): def testDBConnection(self): object_type = ObjectType() object_type.name = 'Test Obje...
Is it possible to apply multiple window functions to the same partition? (Correct me if I'm not using the right vocabulary) For example you can do SELECT name, first_value() over (partition by name order by date) from table1 But is there a way to do something like: SELECT name, (first_value() as f, last_value() as l (partition by na...
Hi, I have a PostgreSQL\PostGIS spatial database which contains Hebrew text columns. The system runs on Ubuntu, and everything works flawlessly with UTF-8. I am trying to dump some tables into shapefile for a Windows program which can only read Windows-1255 strings. Unfortunately, pgsql2shp has no encoding option, although shp2pgsql ha...
I've been reading a couple of docs regarding postgres memory allocation configuration but need a little help. my process runs thousands of SELECT SUM(x) FROM tbl WHERE ??? type queries, some of which take 10-30 seconds to run. the combined total for these queries is multiple days in some cases. besides that, i have a few statements whi...
I am new to PostgreSQL and am using the query tool in PGAdmin. I'm trying to run pgsql queries that use variables, but I can't seem to get the syntax right. Here's a sample query that gives a syntax error: DECLARE num INTEGER; BEGIN num := 3; PRINT num; END; ...
I am new to PostgreSQL, until now I have used MySQL, so when I wanted to see an error from a query sent from PHP I would use; mysql_query("....") or die (mysql_error()); is there a similar function in PostgreSQL as it seems I cannot find it. ...
I'm running Postgres 8.4 on Windows XP and I'm wondering if it is possible to move the WAL to a different disk? I have looked all over the place and can only find documentation on how to do this on Linux. Thanks in advance, Jeff ...
Given a table that looks like this: sensor_id | time | voltage -----------+------------------------+------------ 12292 | 2009-12-01 00:50:04-07 | 2270 12282 | 2009-12-01 00:50:04-07 | 93774 12192 | 2009-12-01 00:50:04-07 | 9386 12609 | 2009-12-01 00:50:05-07 | 0 125...
This problem struck me as a bit odd. I'm curious how you could represent a list of prime numbers in a database. I do not know of a single datatype that would be able to acuratly and consistently store a large amount of prime numbers. My concern is that when the prime numbers are starting to contain 1000s of digits, that it might be a bit...
I have a php script to insert new parts into a postgres database. I want to check if the part exists before it is created. How can I do this. I am using the usual INSERT INTO clause. ...
Is there a way to get the total number of 1's in a Postgres "bit string" type? ...
Hey y'all, I'm trying to add a database-enabled JSP to an existing Tomcat 5.5 application (GeoServer 2.0.0, if that helps). The app itself talks to Postgres just fine, so I know that the database is up, user can access it, all that good stuff. What I'm trying to do is a database query in a JSP that I've added. I've used the config exam...
Hello, I am looking for a tool/command which can compare data between two PostgreSQL databases. The reason to do this is to have some external verification that the SQL script responsible for data migration from one PostgreSQL database to the other have been written correctly. Any pointers would be appreciated regards Sameer ...
What would be equivalent of this in postgresql run('echo "CREATE DATABASE %s;"|mysql --batch --user=%s --password=%s --host=%s' % (dataname, env.mysqluser, env.mysqlpassword, env.mysqlhost), pty=True) The above works fine for creating mysql database, but how would you create one in postgresql without getting stuck at password prompt. ...
The relates to OpenNMS where I'm trying to write an automation in SQL. I have a system which sends events to a table every time the backup succeeds. I want to detect if the backup is overdue for any given node. So for example the (simplified) table looks like: nodeid, eventid, eventuei, eventtime 1 , 1 , backupOk, 09:20 15/12/0...
Hello All, I am having a table orders orders ( id int unsigned not null, fcr_date TIMESTAMP, completion_date TIMESTAMP, factory_no varchar(255), vendor_no varchar(255)) Please ignore the data type typos if any. I want to write a sql query that helps me filter the data per vendor factory. The data to fetch includes the number of ...
Ok, I have a question from a "client" perspective. Let's say we are talking about website designed for distribution: products + their logistics info. Definitely less than a 2k rows, rarely changed but often accessed. Typical row with several columns will have to consist of a picture so it might make it a bit "heavy". I was proposed a w...
Hi, I'm looking for an ANSI-SQL method to do a Select query without returning any record, but fill a TDataSet's Fields structure. The method I found is by adding a "where 1=0" in any query, for example: Select Id, name, province from customers where 1=0 This is a fairly trivial example, it turns a little more complicated when I have ...
There are several ActiveRecord styled query builder libraries out there. Some are stand alone and some come built into frameworks. However, they really have trouble with WHERE and HAVING clauses when it comes to complex SQL. Setting other databases aside - I am trying to come up with a MySQL and PostgreSQL compatible WHERE() method that ...
I have a MySQL database that I'm porting to PostgreSQL (because of GIS features). Many of the tables have hundreds of thousands of rows, so I need to keep performance in mind. My problem is that PostgreSQL seems abysmally slow... For example, if I do a simple SELECT * FROM [table] on a particular table in the MySQL database, let's say...