Re: EXCEPT doesn't compare TIMESTAMP type?

[dipti shah <shahdipti1980@xxxxxxxxx>]

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;
INSERT 0 1

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

 

# Always use the prepared query if available
if (not exists($_SHARED{$query})) {
    $_SHARED{$query} = spi_prepare($query, 'INTEGER', 'INTEGER', 'TIMESTAMP');
}

 

spi_exec_prepared($_SHARED{$query}, 5, 123, now());

 

return;
$BODY$
  LANGUAGE 'plperl' VOLATILE SECURITY DEFINER

techdb=# SELECT insert_history_info();
ERROR:  error from Perl function "insert_history_info": INSERT has more target columns than expressions at line 15.

Thanks,

Dipti

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

On 23 Apr 2010, at 13:17, dipti shah wrote:

For this case you're using 3 values in the first half of the _expression_ and only 1 in the second:

> techdb=# INSERT INTO changelogtest (id, txid, txtime)
> values (5, 123, 'now')

        ^^  ^^^  ^^^^--- 3 columns, namely int, int & text.
> except
> select (id, txid, txtime)
       ^^^^^^^^^^^^^^^^^^^--- 1 column, a row-type containing (int, int, timestamp)

> from changelogtest
> where id=5;
> ERROR:  each EXCEPT query must have the same number of columns
> LINE 2: except select (id, txid, txtime)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:1050,4bd18a8610411242712669!