From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Celia McInnis <celia(at)drmath(dot)ca> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: plpgsql allowing null fields in insert commands? |
Date: | 2005-03-16 20:22:39 |
Message-ID: | 20050316122112.F65825@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Celia McInnis | 2005-03-17 01:10:01 | Re: plpgsql allowing null fields in insert commands? |
Previous Message | Celia McInnis | 2005-03-16 19:05:52 | Re: plpgsql allowing null fields in insert commands? |