Postgres mystery

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/

Hi Guys

Thanks again for your responses.
You were all RIGHT .. again!
a null column was returned, which bombed out the stored procedure.
This was resolved using ur advice.

Kind Regards,
Shaun Clements
B.Com (Hons) IST
Software Developer
Relyant Group IT
Business ConneXion (Pty) Ltd
As service provider for: Relyant

Office: +27 (0)31 3674722
Mobile: +27 (0)84 6166777
Fax: +27 (0)31 3055289
Email: Shaun.Clements@bcx.co.za
<mailto:Shaun.Clements@bcx.co.za>
Web Site: www.bcx.co.za <http://www.bcx.co.za/&gt;

" Obstacles are what we see when we take our eyes off the goal "

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.

Thanks : Zoltan

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...

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