Re: EXCEPT doesn't compare TIMESTAMP type?

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)

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:

 

techdb=# INSERT INTO changelogtest (id, txid) values (5, 123)
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@iol.ie> wrote:

On 23/04/2010 11:31, dipti shah wrote:

> ERROR:  EXCEPT types text and timestamp without time zone cannot be matched
> LINE 2: except select id, txid, txtime

Try adding a cast to one of them.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie