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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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') || ')';
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2005-03-16 20:22:39 | Re: plpgsql allowing null fields in insert commands? |
Previous Message | Greg Lindstrom | 2005-03-16 17:31:39 | XML and Postgres |