Re: plpgsql allowing null fields in insert commands?

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') || ')';