Обсуждение: EXCEPT doesn't compare TIMESTAMP type?
Hi, could anyone please tell me what is wrong in below query. Does it mean that EXCEPT doesn't allow comparing TIMESTAMP type?
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
^
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
^
Thanks,
Dipti
it tells you that it is not able to compare timestamp with text. Different types. Cast if you have to explicitly.
--
GJ
--
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)
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@iol.ie> 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@iol.ie
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
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$
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
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');
}
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
$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.
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 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:^^ ^^^ ^^^^--- 3 columns, namely int, int & text.
> techdb=# INSERT INTO changelogtest (id, txid, txtime)
> values (5, 123, 'now')
> except
> select (id, txid, txtime)
^^^^^^^^^^^^^^^^^^^--- 1 column, a row-type containing (int, int, timestamp)> from changelogtestAlban Hertroys
> where id=5;
> ERROR: each EXCEPT query must have the same number of columns
> LINE 2: except select (id, txid, txtime)
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:1050,4bd18a8610411242712669!
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 pleasehelp 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 bracketsin 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
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:
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 columnsYou 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.
> 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
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:1050,4bd1a0ba10411000594626!