I ran across this bug three times today in one of our projects. Putting the problem and solution online for future reference. impost psycopg2 con = connect(...) def save(long_blob): cur = con.cursor() long_data = struct.unpack('<L', long_blob) cur.execute('insert into blob_records( blob_data ) values (%s)', [long_data...
I have two tables: STUDENT GRADES ---------- ---------- id id name person_id address date city test_name phone grade Each Student will have several entries in the Grades table. I am wondering if it is possible using SQL (Postgres) to select all students along with their lates...
I have a table that stores records broken into fields, ie. if I have this record as follows { "name": "John Doe" "gender": "male" } Then this record is stored in the table in 2 rows, as follows [ { "id": "1", "col": "name", "value": "John Doe" }, { "id": "1", "col": "gender", "value": "male" } ] If I want to write a...
I'm trying to insert items into my postgres table via PHP But get the following error message ERROR: duplicate key violates unique constraint "search6_idx1" search6_idx1 is the index table for search6. I tried the following select setval('search6',45) and somehow that only works for sequences. Is there any way to solve this error? ...
Is there any case study available of any project that uses PostgreSQL 8.3+ Full Text Search on a large amount of data? ...
I've got a large dataset, some of which is is duplicate records, which are identifiable by dupes in two fields. To find these records, the following query works: SELECT * FROM supplierstuffs GROUP BY "Supplier Code", "Cost ex Tax" HAVING count("Description") > 1 Basically what I want to do is cat together all the values of "Descripti...
I have a piece of code that works in a background process which looks like from django.db import transaction try: <some code> transaction.commit() except Exception, e: print e transaction.rollback() In a test, I break <some_code> with data that causes a database error. The exception is following File "/home/comm...
I have a Tomcat and PostgreSQL installed on a server. I'm having a connection problem trying to connect from my servlet to PostgreSQL database using c3p0 pool. I can reach DB if I'm running Tomcat locally on my laptop. Also I can connect from server to DB using psql (i.e. command line sql utility). But when I'm trying to deploy my serv...
Postgres has a useful function called GREATEST (which returns the largest value of those passed to it, documented here: http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html), is there any equivalent in SQLite? As a note, I only need it to work with 2 arguments. ...
Postgresql has 3 keepalive settings for managing dropped connections (in postgresql.conf): tcp_keepalives_count tcp_keepalives_idle tcp_keepalives_interval By default these are 0. The behavior I would like is for Postgresql to drop client connections after a period of time, should the client lose its network connection or go to sleep....
Hi, Given a From Date, To Date and a Fiscal Year system, I want to get all the split-up duration within the given From & To Date based on the Fiscal Year system. Explained below with examples: Example 1: Fiscal Year system: Apr to Mar From Date: Jan-05-2008 To Date: May-15-2008 Based on Fiscal Year system, duration should be splitte...
I am about to embark on a PostgreSQL project for a client. They want to develop a huge professional database with many complex joins, so after consideration I have chosen to go with PostgreSQL over MySQL. An important consideration is how to effectively interface to the database with scripts. Currently, the client uses about a million s...
I had asked an earlier question which did not get any replies. Basically I get an error invalid database url when I try to do heroku db:push So I figured I can try explicitly providing the database url. so I tried heroku db:push postgres://postgres@localhost/myrailsdb but that gave error: Failed to connect to database: Sequel::...
For example, I have the following tables: animal ----------------------- animal_id | animal_name ----------------------- owners ----------------------- owner_id | owner_name ----------------------- owners_animals -------------------- owner_id | animal_id -------------------- I want to find the animals with no owners so I do the quer...
Did a new install of postgres 8.4 on mint ubuntu. How do I create a user for postgres and login using psql? When I type psql, it just tells me psql: FATAL: Ident authentication failed for user "my-ubuntu-username" ...
I have a Django application and I'm using postgres. I try to execute the bollowing line in one of my tests: print BillingUser.objects.all() And I get the following error: "current transaction is aborted, commands ignored until end of transaction block." My postresql log: ERROR: duplicate key value violates unique constraint "...
How can I output postgresql dates with ISO 8601-compliant timezones? e.g. I have the value 2006-11-10 07:35:30+01 and I need it in this format: 2006-11-10T07:35+01 I could do that easily enough with string manipulation, but the standard date formatting functions for to_char(<my date>, 'YYYY-MM-DDThh:mmTD') would give me this: ...
I have a Ruby on Rails application with a PostgreSQL database; several tables have created_at and updated_at timestamp attributes. When displayed, those dates are formatted in the user's locale; for example, the timestamp 2009-10-15 16:30:00.435 becomes the string 15.10.2009 - 16:30 (the date format for this example being dd.mm.yyyy - hh...
Hello there, i have a webapplication which performs searches on a postgresql view with about 33.000 rows. If i try this alone it takes about 1-2 seconds to get an result, which is ok i first thought. But then i downloaded the Web Application Stress Test Tool from Microsoft to give some load on my webapp. So i tried it first with 10 conc...
Hello, I am writing a PHP script (which also uses linux bash commands) which will run through test cases by doing the following: I am using a PostgreSQL database (8.4.2)... 1.) Create a DB 2.) Modify the DB 3.) Store a database dump of the DB (pg_dump) 4.) Do regression testing by doing steps 1.) and 2.), and then take another databa...