Re: [Pypgsql-users] PgNumeric and NULL values

[Ivar Z. <if...@al...>]

On Sun, Dec 14, 2003 at 08:53:33PM -0500, Billy G. Allie wrote:

> | Updating numeric field with values of '0.00' generates query, trying
> | update with 'NULL'. Since column is defined as 'NOT NULL', update fails
> 
> Can you post sample code that exhibits this behavior please.

Creaed table as:
create table testtable (
    ident int not null,
    price numeric not null
);

Inserted test values:
insert into testtable (ident, price) values (10, 0.00);

Running follofing short test:
====
#!/usr/bin/env python

from pyPgSQL import PgSQL

connstr = '::DBname:DBuser:DBpass:'
DBConn = PgSQL.connect(connstr)
Cursor = DBConn.cursor()

price = PgSQL.PgNumeric('0.00')
ident = 10

qry = """
UPDATE
    testtable
SET
    price = %s
WHERE
    ident = %s
"""

Cursor.execute(qry, price, ident)
====

throws exception:

====
Traceback (most recent call last):
  File "./numtest.py", line 21, in ?
      Cursor.execute(qry, price, ident)
  File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3072,
                in execute
      raise OperationalError, msg
      libpq.OperationalError: ERROR:  ExecUpdate: 
                Fail to add null value in not null attribute price
====

PostgreSQL log has following query:

====
postgres[23448]: [240-1] LOG:  query:
postgres[23448]: [240-2] UPDATE
postgres[23448]: [240-3]     testtable
postgres[23448]: [240-4] SET
postgres[23448]: [240-5]     price = NULL
postgres[23448]: [240-6] WHERE
postgres[23448]: [240-7]     ident = 10
postgres[23448]: [241] ERROR:  ExecUpdate: Fail to add null value in not null attribute price
postgres[23448]: [242] DEBUG:  AbortCurrentTransaction
postgres[23448]: [243] DEBUG:  StartTransactionCommand
postgres[23448]: [244] LOG:  query: ROLLBACK WORK
====

PostgreSQL version 7.3.4
PyPgSQL version 2.4
OS is Debian Woody


-- 
Ivar