Skip site navigation (1) Skip section navigation (2)

Re: plpgsql allowing null fields in insert commands?

From: "Celia McInnis" <celia(at)drmath(dot)ca>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql allowing null fields in insert commands?
Date: 2005-03-16 19:05:52
Message-ID: 20050316182820.M87190@drmath.ca (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Stephan - Yes, I am making a query string for execute, and this query
string ends up being null one or more of the values to be inserted is null.
Thanks - the COALESCE function allws me to form a non-null query string, but I
haven't yet got the usage quite right. The variable which I'm dealing with is
of type TIMESTAMP and I currently have something like:

q:='INSERT INTO ' || mytable || 'VALUES (' ||
CAST(COALESCE(quote_literal(mytime),'NULL') AS TIMESTAMP) || ')';

I get an error when running the procedure:

ERROR: invalid input syntax for type timestamp: "NULL"

I had earlier tried:

q:='INSERT INTO ' || mytable || 'VALUES (' ||
COALESCE(quote_literal(mytime),'NULL') || ')';

and received the error:

ERROR: column "mytime" is of type timestamp without timezone but expression is
of type integer.
HINT: You will need to rewrite or cast the expression.
CONTEXT: SQl STATEMENT "INSERT INTO mytable VALUES(NULL)

Can you tell me how I should do this?

Thanks,
Celia


On Wed, 16 Mar 2005 06:07:59 -0800 (PST), Stephan Szabo wrote
> On Tue, 15 Mar 2005, Celia McInnis wrote:
> 
> > In a plpgsql procedure is there any way to form an insert command which has
> > some null values for values of the inputs?
> >
> > Currently when I form such a command, the command becomes null if there are
> > any null values inserted for the fields.
> 
> Are you making a query string for execute?
> Something like:
>  querystring := 'insert into foo(col1) values (' || variable || ')';
> won't work if variable is null. You'd probably need something like:
>  querystring := 'insert into foo(col1) values (' ||
>   COALESCE(variable,'NULL') || ')';



In response to

Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2005-03-16 20:22:39
Subject: Re: plpgsql allowing null fields in insert commands?
Previous:From: Greg LindstromDate: 2005-03-16 17:31:39
Subject: XML and Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group