Thanks but I don't have text type in my table.
sysdb=# \d changelogtest
...
...
Table "sysdb.changelogtest"
Column | Type | Modifiers
----------------+-----------------------------+------------------------------------------
id | integer | not null
txid | integer | not null default txid_current()
txtime | timestamp without time zone | not null default transaction_timestamp()
Foreign-key constraints:
"changelogtest_id_fkey" FOREIGN KEY (id) REFERENCES logtable(id)
Column | Type | Modifiers
----------------+-----------------------------+------------------------------------------
id | integer | not null
txid | integer | not null default txid_current()
txtime | timestamp without time zone | not null default transaction_timestamp()
Foreign-key constraints:
"changelogtest_id_fkey" FOREIGN KEY (id) REFERENCES logtable(id)
Moreover, the query behaves differently when I proive brackates after SELECT 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: EXCEPT types text and timestamp without time zone cannot be matched
LINE 2: except select id, txid, txtime
^
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)
^
Below works:
except select id, txid, txtime
from changelogtest
where id=5;
ERROR: EXCEPT types text and timestamp without time zone cannot be matched
LINE 2: except select id, txid, txtime
^
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)
^
Below works:
techdb=# INSERT INTO changelogtest (id, txid) values (5, 123)
except select id, txid
from changelogtest
where id=5;
INSERT 0 0
except select id, txid
from changelogtest
where id=5;
INSERT 0 0
I don't know how to resolve my issue. Could you please help me out.
Thanks,
Dipti
On Fri, Apr 23, 2010 at 4:08 PM, Raymond O'Donnell <rod@xxxxxx> wrote:
On 23/04/2010 11:31, dipti shah wrote:Try adding a cast to one of them.
> ERROR: EXCEPT types text and timestamp without time zone cannot be matched
> LINE 2: except select id, txid, txtime
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@xxxxxx
