Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
Peter Hunsberger peter.hunsber...@gmail.com wrote: I still don't get it. I do want a zero for the subversion_flags to be stored in the table. But it returned an error because it didn't like subversion_flags='' in the UPDATE SQL statement. subversion_flags | integer | not null default 0 Right. '' is not 0. the old version of pgsql converted '' to 0 for you, incorrectly. Now if you want 0 you need to say 0. Or, since you have the default, set it to null (Which may be what you thought you where doing?) Setting it to NULL does not set it to the default value. You have to use the keyword DEFAULT for that. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Null vs. Empty String in Postgres 8.3.8
Can someone provide a link to the Postgres 8.x documentation that provides
information about Null vs. Empty String?
Here is the situation:
Currently, the source code performs the following SQL statement :
UPDATE user_group set subversion_flags='' WHERE user_id='5818' AND
group_id='438';
(This SQL statement worked in a very old version of Postgres)
and pgsql 8.3.8 returned ERROR: invalid input syntax for integer:
The table
\d user_group
Table public.user_group
Column | Type |Modifiers
--+---+
-
user_group_id| integer | not null default nextval(('user_group_pk_se
q'::text)::regclass)
user_id | integer | not null default 0
group_id | integer | not null default 0
subversion_flags | integer | not null default 0
.
.
I know probably the best way is to the put check in the application level
(making sure that subversion_flags has a value) before the actual update SQL
call; however, I really just want to port the code to work with Postgres 8.3.8
avoiding any code changes if that's possible. Is there something that I can
do at the database level (like alter the table) so that I can still use the
same SQL statement in the application level listed above and it wouldn't return
an error? Any ideas?
Thanks in advance
Mary
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
Wang, Mary Y mary.y.w...@boeing.com writes: Can someone provide a link to the Postgres 8.x documentation that provides information about Null vs. Empty String? Here is the situation: Currently, the source code performs the following SQL statement : UPDATE user_group set subversion_flags='' WHERE user_id='5818' AND group_id='438'; (This SQL statement worked in a very old version of Postgres) and pgsql 8.3.8 returned ERROR: invalid input syntax for integer: You were not getting a NULL there. You were getting an integer zero, as a result of sloppy input checking in the integer-input routine. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
I still don't get it. I do want a zero for the subversion_flags to be stored in the table. But it returned an error because it didn't like subversion_flags='' in the UPDATE SQL statement. subversion_flags | integer | not null default 0 Mary -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Sunday, April 04, 2010 7:50 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8 Wang, Mary Y mary.y.w...@boeing.com writes: Can someone provide a link to the Postgres 8.x documentation that provides information about Null vs. Empty String? Here is the situation: Currently, the source code performs the following SQL statement : UPDATE user_group set subversion_flags='' WHERE user_id='5818' AND group_id='438'; (This SQL statement worked in a very old version of Postgres) and pgsql 8.3.8 returned ERROR: invalid input syntax for integer: You were not getting a NULL there. You were getting an integer zero, as a result of sloppy input checking in the integer-input routine. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
On Sun, Apr 4, 2010 at 9:03 PM, Wang, Mary Y mary.y.w...@boeing.com wrote: I still don't get it. I do want a zero for the subversion_flags to be stored in the table. But it returned an error because it didn't like subversion_flags='' in the UPDATE SQL statement. subversion_flags | integer | not null default 0 Right. '' is not 0. the old version of pgsql converted '' to 0 for you, incorrectly. Now if you want 0 you need to say 0. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
On Sun, Apr 4, 2010 at 10:23 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Sun, Apr 4, 2010 at 9:03 PM, Wang, Mary Y mary.y.w...@boeing.com wrote: I still don't get it. I do want a zero for the subversion_flags to be stored in the table. But it returned an error because it didn't like subversion_flags='' in the UPDATE SQL statement. subversion_flags | integer | not null default 0 Right. '' is not 0. the old version of pgsql converted '' to 0 for you, incorrectly. Now if you want 0 you need to say 0. Or, since you have the default, set it to null (Which may be what you thought you where doing?) -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
On Sun, Apr 04, 2010 at 08:03:13PM -0700, Wang, Mary Y wrote: I still don't get it. I do want a zero for the subversion_flags to be stored in the table. But it returned an error because it didn't like subversion_flags='' in the UPDATE SQL statement. subversion_flags | integer | not null default 0 Thde default will not apply because you attempted to input a value. Unless I'm mistaken the provision of any value (erroneous or otherwise) obviates the activation of the default value. There is an exception to this and that is using the DEFAULT keyword (ie subversion_flags=DEFAULT). Otherwise the only way it activates is if you leave subversion_flags out totally. If you want input data mangling then a TRIGGER may be the way to go. -- A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier. - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
