Re: plpgsql allowing null fields in insert commands?

On Wed, 16 Mar 2005, Celia McInnis wrote:

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

Hmm, my simple tests worked, can you show the full function definition and
the table definition of the table you're trying to insert into?