Thread: Postgres mystery

Can anyone tell me what the problem is here:
I am inserting into a table via a stored procedure, to a few columns within the table and postgres is throwing a
CANNOT EXECUTE NULL QUERY.

<code snip>
EXECUTE ''INSERT INTO table (column1, column2, column3, ''||quote_ident(column4)||'') values (''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.column2)||'',''''stringvalue'''',''||quote_literal(RECORDNAME.column2)||'')'';

</code snip>

Is this a bug, as I am ensuring that the NOT NULL columns are supplied, as well as one other.

Kind Regards,
Shaun Clements

On Wed, 30 Mar 2005 09:11:09 +0200, Shaun Clements <ShaunC@relyant.co.za> wrote:
> Can anyone tell me what the problem is here:
> I am inserting into a table via a stored procedure, to a few columns within
> the table and postgres is throwing a
> CANNOT EXECUTE NULL QUERY.
>
> <code snip>
> EXECUTE ''INSERT INTO table (column1, column2, column3,
> ''||quote_ident(column4)||'') values
> (''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.colu
> mn2)||'',''''stringvalue'''',''||quote_literal(RECORDNAME.column2)||'')'';
> </code snip>
>
> Is this a bug, as I am ensuring that the NOT NULL columns are supplied, as
> well as one other.

At least one of column4, recordname.column1, recordname.column2,
recordname.column2 is null.  If you append a null to a string, the
result is null.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

Shaun Clements wrote:
> Can anyone tell me what the problem is here:
> I am inserting into a table via a stored procedure, to a few columns within
> the table and postgres is throwing a
> CANNOT EXECUTE NULL QUERY.
>
> <code snip>
> EXECUTE ''INSERT INTO table (column1, column2, column3,
> ''||quote_ident(column4)||'') values
> (''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.colu
> mn2)||'',''''stringvalue'''',''||quote_literal(RECORDNAME.column2)||'')'';
> </code snip>
>
> Is this a bug, as I am ensuring that the NOT NULL columns are supplied, as
> well as one other.

I'm guessing one of your variables is null. Try explicitly checking all
of those.

--
   Richard Huxton
   Archonet Ltd

On Wed, Mar 30, 2005 at 09:11:09AM +0200, Shaun Clements wrote:
> Can anyone tell me what the problem is here:
> I am inserting into a table via a stored procedure, to a few columns within
> the table and postgres is throwing a
> CANNOT EXECUTE NULL QUERY.
>
> EXECUTE ''INSERT INTO table (column1, column2, column3,
> ''||quote_ident(column4)||'') values
> (''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.colu
> mn2)||'',''''stringvalue'''',''||quote_literal(RECORDNAME.column2)||'')'';

One of the operands to || is probably NULL, so the entire INSERT
string ends up being NULL.  Example:

SELECT 'abc' || 'def';
 ?column?
----------
 abcdef
(1 row)

SELECT 'abc' || NULL;
 ?column?
----------

(1 row)

Looks like you need to check for NULL or use COALESCE to convert
NULL to something else.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Szmutku Zoltán wrote:
> Hi ,
>
> Thanks your previous help. I have a new question :  how to store
> empty value to date field ? ( I always get error message, in vain use
> any format ) In my client be empty value for date fields ('    .  .
> ') , and I would like to use NULL values and empty values also.

How can "  " be a valid date?

You might want to reconsider your design.
--
   Richard Huxton
   Archonet Ltd

Michael Fuhr wrote:

>On Wed, Mar 30, 2005 at 09:11:09AM +0200, Shaun Clements wrote:
>
>
>>Can anyone tell me what the problem is here:
>>I am inserting into a table via a stored procedure, to a few columns within
>>the table and postgres is throwing a
>>CANNOT EXECUTE NULL QUERY.
>>
>>EXECUTE ''INSERT INTO table (column1, column2, column3,
>>''||quote_ident(column4)||'') values
>>(''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.colu
>>mn2)||'',''''stringvalue'''',''||quote_literal(RECORDNAME.column2)||'')'';
>>
>>
>
>One of the operands to || is probably NULL, so the entire INSERT
>string ends up being NULL.  Example:
>
>SELECT 'abc' || 'def';
> ?column?
>----------
> abcdef
>(1 row)
>
>SELECT 'abc' || NULL;
> ?column?
>----------
>
>(1 row)
>
>Looks like you need to check for NULL or use COALESCE to convert
>NULL to something else.
>
>
>
That something else you ought to do appears in the documentation User
Comments at

http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html

> In my client be empty value for date fields ('    .  .  ') , and I would
> like to use NULL values and empty values also.

What is your reason to put empty value ('') as alternative of null value?

I have a ODBC client which uses empty dates in its native data engine.
Unfortunately, empty data causes error if trying to store it.
This is serious Postgres limitation.
For a partial fix ODBC driver or postgres should convert empty dates to
NULLs automatically. This is better that producing error.

>> How can "  " be a valid date? <<
>> You might want to reconsider your design. <<

Using your logic, I can ask: How can be 0 valid number ?  If there is not
empty dates, number 0 cannot be also legal.

Empty data is a date which is less that all other dates. It can be used in
comparisons.
In contract, NULL date means that date is not entered.

Andrus Moor

"Richard Huxton" <dev@archonet.com> wrote in message
news:424A7831.6040805@archonet.com...
> Szmutku Zolt�n wrote:
>> Hi ,
>>
>> Thanks your previous help. I have a new question :  how to store
>> empty value to date field ? ( I always get error message, in vain use
>> any format ) In my client be empty value for date fields ('    .  .
>> ') , and I would like to use NULL values and empty values also.
>
> How can "  " be a valid date?
>
> You might want to reconsider your design.
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>

On Thu, Mar 31, 2005 at 09:43:27AM +0300, Andrus wrote:

> Empty data is a date which is less that all other dates.

Why would that be ? "<Empty> of type <unknown>" cannot be less
than (nor more than nor equal to) "<not empty> of type <date>".
If you want to express the valid idea of "<less-than-anything>
of type <date>" you are better off using a logical construct
tailored for that purpose. It may help to use "<4734-01-01 BC> of
type <date>" or some such. However the best would be to use
"-infinity". See the docs.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sun, Apr 03, 2005 at 11:06:22 +0200,
  Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> On Thu, Mar 31, 2005 at 09:43:27AM +0300, Andrus wrote:
>
> > Empty data is a date which is less that all other dates.
>
> Why would that be ? "<Empty> of type <unknown>" cannot be less
> than (nor more than nor equal to) "<not empty> of type <date>".
> If you want to express the valid idea of "<less-than-anything>
> of type <date>" you are better off using a logical construct
> tailored for that purpose. It may help to use "<4734-01-01 BC> of
> type <date>" or some such. However the best would be to use
> "-infinity". See the docs.

+/- infinity are only available as timestamps, not dates.

> > type <date>" or some such. However the best would be to use
> > "-infinity".
>
> +/- infinity are only available as timestamps, not dates.
Hm, any particular reason why ? Apart from no one having
gotten around to doing it...

Strange enough, on 7.4.6 I am unsure as to how to interpret
the output of:

select ('2000-10-10'::date < 'infinity'::timestamp::date);

 ?column?
----------

(1 row)

Further testing shows it seems to be of type boolean. Is it
NULL ? Coalesce did not help.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sun, Apr 03, 2005 at 15:46:18 +0200,
  Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> > > type <date>" or some such. However the best would be to use
> > > "-infinity".
> >
> > +/- infinity are only available as timestamps, not dates.
> Hm, any particular reason why ? Apart from no one having
> gotten around to doing it...

I think that is it. I seem to remember a discussion of that about 2 or 3
years ago, but nothing came of it.

> Strange enough, on 7.4.6 I am unsure as to how to interpret
> the output of:
>
> select ('2000-10-10'::date < 'infinity'::timestamp::date);
>
>  ?column?
> ----------
>
> (1 row)
>
> Further testing shows it seems to be of type boolean. Is it
> NULL ? Coalesce did not help.

area=> select 'infinity'::timestamp::date is null;
 ?column?
----------
 t
(1 row)

> area=> select 'infinity'::timestamp::date is null;
>  ?column?
> ----------
>  t
> (1 row)
Ah, thanks.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

on 3/30/05 11:43 PM, noeetasoftspam@online.ee purportedly said:

> I have a ODBC client which uses empty dates in its native data engine.
> Unfortunately, empty data causes error if trying to store it.
> This is serious Postgres limitation.
> For a partial fix ODBC driver or postgres should convert empty dates to
> NULLs automatically. This is better that producing error.
>
>>> How can "  " be a valid date? <<
>>> You might want to reconsider your design. <<
>
> Using your logic, I can ask: How can be 0 valid number ?  If there is not
> empty dates, number 0 cannot be also legal.

It's mathematics 101. 0 *is* a valid integer. The SQL standard defines that
any field must either have a valid value, or be NULL, which means "no
value"--and not any idea of "not entered" because you can very easily enter
a NULL value into any field. Therefore logic would dictate that a field
either has a value, or it doesn't, in SQL, this means it has a (valid) value
or is NULL.

> Empty data is a date which is less that all other dates. It can be used in
> comparisons.
> In contract, NULL date means that date is not entered.

There is no such thing as an "empty date". Just like there is no such thing
as an empty boolean, or an empty number. The mistaken notion of "empty" date
seems to proceed from the concept of an "empty string", which is in no way
applicable to any other data type. A string is an array of bytes of X length
where X is an integer. So an "empty" string is just an array of bytes of
zero length.

Looking at dates again, when is 0 date? I suppose Postgres could include the
current computations for the occurrence of the "big bang" but the storage
requirements for such an number, simply to store a current date, would be
highly impractical.

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> area=> select 'infinity'::timestamp::date is null;
>> ?column?
>> ----------
>> t
>> (1 row)
> Ah, thanks.

I think this is a bug BTW.  If we can't convert the value correctly,
we ought to raise an error not return NULL.

            regards, tom lane

The format_type function is great, except for the way it formats varchars,
it always returns Character Varying or whatever, it would be nice if it
had a option to return a short version of
these names.  i.e.

format_type(a.atttypid, a.atttypmod,true)  would return all short names

format_type(a.atttypid, a.atttypmod,false) would return full names

I don't know of any other system that shows varchars in this way, they
should just be formated as varchar(50).

If there is another way to do this without messy case statements please
let me know.


Thanks,

Tony

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> The format_type function is great, except for the way it formats varchars,
> it always returns Character Varying or whatever,
> ...
> I don't know of any other system that shows varchars in this way, they
> should just be formated as varchar(50).

Hmm ... AFAICS, "character varying(n)" and "varchar(n)" are equally
valid spellings according to the SQL spec.

I'd personally prefer the shorter spelling too, but I can't see any
particularly strong argument for changing it.

            regards, tom lane

Would it not be possible to add another param to the function like I mentioned
and keep everyone happy?  Users coming from MS SQL server look puzzled when
they see the huge Character Varying(n) in a admin tool.

I have not programmed in C since 1993, but I know in pascal you can have a
parameter for a function be set as a const, that way it would remain
compatible with the current function, then some logic could be added to make
the long names be short? or even a overloaded function would be ok.

I have got around this by using  a case statment but it would be so nice if I
could just call that function and have the results look like what users see
in tools such as MS SQL servers enterprise manager.

Thanks,

Tony

On Sunday 03 April 2005 20:49 pm, Tom Lane wrote:
> Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> > The format_type function is great, except for the way it formats
> > varchars, it always returns Character Varying or whatever,
> > ...
> > I don't know of any other system that shows varchars in this way, they
> > should just be formated as varchar(50).
>
> Hmm ... AFAICS, "character varying(n)" and "varchar(n)" are equally
> valid spellings according to the SQL spec.
>
> I'd personally prefer the shorter spelling too, but I can't see any
> particularly strong argument for changing it.
>
>    regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Sun, Apr 03, 2005 at 08:54:12PM -0400, Tom Lane wrote:
> >> area=> select 'infinity'::timestamp::date is null;
> >> ?column?
> >> ----------
> >> t
> >> (1 row)
> > Ah, thanks.
>
> I think this is a bug BTW.  If we can't convert the value correctly,
> we ought to raise an error not return NULL.
Which is *why* I wasn't sure how to interpret it.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Tony Caduto wrote:
> Would it not be possible to add another param to the function like I mentioned
> and keep everyone happy?  Users coming from MS SQL server look puzzled when
> they see the huge Character Varying(n) in a admin tool.
>
> I have not programmed in C since 1993, but I know in pascal you can have a
> parameter for a function be set as a const, that way it would remain
> compatible with the current function, then some logic could be added to make
> the long names be short? or even a overloaded function would be ok.
>
> I have got around this by using  a case statment but it would be so nice if I
> could just call that function and have the results look like what users see
> in tools such as MS SQL servers enterprise manager.

We don't have function overloading like that in C, so we would need a
new function and have to keep the old one around too.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073