Thread: plpgsql allowing null fields in insert commands?

Hi:

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.

Thanks,

Celia McInnis

Celia,

On Mar 15, 2005, at 5:53 PM, 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.

You can insert null values in an insert statement or have row values be
null by not including them in the insert statement. But if you are
getting errors because of this it is likely the column specification of
the table is setup disallow null values.

If this does not answer your question, post an insert example with the
error and the table specification.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

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

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

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?

Ok - there was a column misordering in my rather complicated procedure.
The form which works to handle possibly null-valued (timestamp) values is indeed:

u:='INSERT INTO ' || mytable || ' VALUES(' ||
COALESCE(quote_literal(mytime),'NULL') || ')';

Thanks for your help. I hope to be good at this plpgsql at some point!

Celia

> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq


--
Open WebMail Project (http://openwebmail.org)