From: | dipti shah <shahdipti1980(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
Cc: | rod(at)iol(dot)ie, pgsql-general(at)postgresql(dot)org |
Subject: | Re: EXCEPT doesn't compare TIMESTAMP type? |
Date: | 2010-04-23 12:28:35 |
Message-ID: | v2od5b05a951004230528gdd2473bwc1b11df1f4dc7de1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)solfertje(dot)student(dot)utwente(dot)nl> 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!
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | சிவகுமார் மா | 2010-04-23 12:54:02 | Need help to identify stray row in a table |
Previous Message | Alban Hertroys | 2010-04-23 11:54:39 | Re: EXCEPT doesn't compare TIMESTAMP type? |