Re: EXCEPT doesn't compare TIMESTAMP type?

[dipti shah <shahdipti1980@xxxxxxxxx>]

Thanks again Alban. I didn't mention but if I remove the braces then it gives below error but the meaning is same as the error when I specify braces.

techdb=# SELECT insert_history_info();
ERROR:  error from Perl function "insert_history_info": each EXCEPT query must have the same number of columns at line 15.

Thanks,
Dipti

On Fri, Apr 23, 2010 at 6:59 PM, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

On 23 Apr 2010, at 14:28, dipti shah wrote:

> Great!  Thanks Alban, Alexander, and Thomas.
>
> That solved the issue but could you tell me what is the issue when I give brackets in second query?
>
> techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now())
> except select (id, txid, txtime)
> from changelogtest
> where id=5;

> ERROR:  each EXCEPT query must have the same number of columns
> LINE 2: except select (id, txid, txtime)

> I need brackets because this query actually I am using from trigger like below and it gives the same error: Could you please help me with it.
>
> CREATE OR REPLACE FUNCTION insert_history_info()
>   RETURNS VOID AS
> $BODY$
> my $query = (<<ENDQUERY);
>     INSERT INTO changelogtest(id, txid, txtime)
>     SELECT  (\$1, \$2, \$3)
>     EXCEPT
>     SELECT (id, txid, txtime)
>     FROM changelogtest
>     WHERE id = \$1
>     AND txid = \$2
>     AND txtime = \$3;
> ENDQUERY

You need to remove the braces from the query in your trigger too, they change the meaning of the query. You use brackets in this way if you need to reference fields from a composite type.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:1050,4bd1a0ba10411000594626!