PostgreSQL Discussion - Postgres mystery

Author

Postgres mystery

Shaun Clements

2005-03-30, 9:41 am

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(colu
mn4)||'') values
(''||quote_literal(R
ECORDNAME. column1)||'',''||quo
te_literal(RECORDNAM
E.colu
mn2)||'',''''stringv
alue'''',''||quote_l
iteral(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

Klint Gore

2005-03-30, 9:41 am

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(colu
mn4)||'') values
> (''||quote_literal(R
ECORDNAME. column1)||'',''||quo
te_literal(RECORDNAM
E.colu
> mn2)||'',''''stringv
alue'''',''||quote_l
iteral(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 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Richard Huxton

2005-03-30, 9:41 am

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(colu
mn4)||'') values
> (''||quote_literal(R
ECORDNAME. column1)||'',''||quo
te_literal(RECORDNAM
E.colu
> mn2)||'',''''stringv
alue'''',''||quote_l
iteral(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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Michael Fuhr

2005-03-30, 9:41 am

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(colu
mn4)||'') values
> (''||quote_literal(R
ECORDNAME. column1)||'',''||quo
te_literal(RECORDNAM
E.colu
> mn2)||'',''''stringv
alue'''',''||quote_l
iteral(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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Shaun Clements

2005-03-30, 9:41 am

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

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

Berend Tober

2005-03-30, 9:41 am

Michael Fuhr wrote:

>On Wed, Mar 30, 2005 at 09:11:09AM +0200, Shaun Clements wrote:
>
>
>
>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/...onditional.html

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sponsored Links