pypgsql-users

[Pypgsql-users] PgNumeric and NULL values

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

Hello!

I am playing around with PyPgSQL and found interesting behaviour:
Updating numeric field with values of '0.00' generates query, trying
update with 'NULL'. Since column is defined as 'NOT NULL', update fails
with exception:
"OperationalError: ERROR:  ExecUpdate: Fail to add null value in not
null attribute... "

Is this normal?

-- 
Best regards,
Ivar Zarans

[Pypgsql-users] PgNumeric and NULL values

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

Hello!

I am playing around with PyPgSQL and found interesting behaviour:
Updating numeric field with values of '0.00' generates query, trying
update with 'NULL'. Since column is defined as 'NOT NULL', update fails
with exception:
"OperationalError: ERROR:  ExecUpdate: Fail to add null value in not null
attribute... "

Is this normal? 

-- 
Best regards,
Ivar Zarans

Re: [Pypgsql-users] PgNumeric and NULL values

[Billy G. A. <bil...@mu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ivar Zarans wrote:
| Hello!
|
| I am playing around with PyPgSQL and found interesting behaviour:
| Updating numeric field with values of '0.00' generates query, trying
| update with 'NULL'. Since column is defined as 'NOT NULL', update fails
| with exception:
| "OperationalError: ERROR:  ExecUpdate: Fail to add null value in not
| null attribute... "
|
| Is this normal?
|

Can you post sample code that exhibits this behavior please.

Thanks.

- --
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/3RQcnmIkMXoVVdURAtxtAKCKW2GNrimqYmNDbqShJHrHVnLI4ACeOrdf
SpyoSH5VpPYED/P12NW+vJ8=
=jpdR
-----END PGP SIGNATURE-----

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

Re: [Pypgsql-users] PgNumeric and NULL values

[Billy G. A. <bil...@mu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ivar Zarans wrote:
| 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
|

The following patch will correct the problem.  I also updated CVS on
sourceforge.

*** pyPgSQL/PgSQL.py    22 Nov 2003 05:35:47 -0000      1.38
- --- pyPgSQL/PgSQL.py    17 Dec 2003 04:49:08 -0000
***************
*** 1674,1680 ****
~            return PgNumeric(-self.__v, self.__p, self.__s)

~      def _quote(self, forArray=0):
!       if self.__v:
~            if forArray:
~                return '"%s"' % self.__fmtNumeric()
~            else:
- --- 1674,1680 ----
~            return PgNumeric(-self.__v, self.__p, self.__s)

~      def _quote(self, forArray=0):
!       if self.__v != None:
~            if forArray:
~                return '"%s"' % self.__fmtNumeric()
~            else:



- --
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/3+JmnmIkMXoVVdURAtU8AJ9wBHD1WPT+M5ulypbBCCbgR1FLBACgkmRp
h+sD0X1dRRnYXGkTYcFkV/c=
=1mm6
-----END PGP SIGNATURE-----

Re: [Pypgsql-users] PgNumeric and NULL values

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

On Tue, Dec 16, 2003 at 11:58:14PM -0500, Billy G. Allie wrote:

> The following patch will correct the problem.  I also updated CVS on
> sourceforge.

Thanks!!

-- 
Ivar