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