Search Postgresql Archives

Re: EXCEPT doesn't compare TIMESTAMP type?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux