Skip site navigation (1) Skip section navigation (2)

Re: plpgsql allowing null fields in insert commands?

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 (view raw or flat)
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?


In response to

Responses

pgsql-novice by date

Next:From: Celia McInnisDate: 2005-03-17 01:10:01
Subject: Re: plpgsql allowing null fields in insert commands?
Previous:From: Celia McInnisDate: 2005-03-16 19:05:52
Subject: Re: plpgsql allowing null fields in insert commands?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group