EXCEPT doesn't compare TIMESTAMP type?

it tells you that it is not able to compare timestamp with text. Different
types. Cast if you have to explicitly.

--
GJ

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

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:

dipti shah, 23.04.2010 13:17:

Thanks but I don't have text type in my table.
sysdb=# \d changelogtest
techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now')
except select id, txid, txtime
from changelogtest
where id=5;

'now' *is* a text type value

Thomas

On Friday 23 April 2010 14:17:32 dipti shah wrote:

Thanks but I don't have text type in my table.

But you are trying to insert text value 'now' into table, that's why appears this error.
If you want to insert current time try using function now() not text 'now'

In Your case insert should be following
INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now())
except select id, txid, txtime from changelogtest where id=5;

--
Alexandr Popov

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:737,4bd18a9110411947912088!

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@solfertje.student.utwente.nl> wrote:

On 23 Apr 2010, at 14:28, dipti shah wrote:

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;
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

You need to remove the braces from the query in your trigger too, they change the meaning of the query. You use brackets in this way if you need to reference fields from a composite type.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4bd1a0c310411470018361!

Thanks again Alban. I didn't mention but if I remove the braces then it
gives below error but the meaning is same as the error when I specify
braces.

techdb=# SELECT insert_history_info();
ERROR: error from Perl function "insert_history_info": each EXCEPT query
must have the same number of columns at line 15.

Thanks,
Dipti

On Fri, Apr 23, 2010 at 6:59 PM, Alban Hertroys <
dalroi@solfertje.student.utwente.nl> wrote: