question 1
Hi, i have this query:
select * from libro where id_libro_key in (select id_libro from
libro_autore where id_auto
re in (select id_autore from autore where ((cognome = 'King' and nome =
'Stephen') OR (cog
nome = 'Clancy' and nome = 'Tom'))))\g
that return me this error:
ERROR: There is no operator '=' for types 'int4' and 'varchar'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR
but if I split the query in 2 part I obtain:
1)select id_libro from libro_autore where id_autore in (select id_autore
from autore where
((cognome = 'King' and nome = 'Stephen') OR (cognome = 'Clancy' and nome
= 'Tom')))\g
id_libro
--------
11
12
(2 rows)
2)esame=> select * from libro where id_libro_key in ( 11,12)\g
titolo |id_libro|id_utente|collocazione|casa_edit|
data_publ|tipo|difetto|id_libro_ke
y
------------+--------+---------+------------+---------+----------+----+-------+-----------
-
It |d1 | |d1 |apo |05-12-1940| 1|
| 1
1
Il talismano|s1 | |d1 |aop2 |05-12-1985| 1|
| 1
2
(2 rows)
why I can not make the union/join ?
where I'm wrong ?
these are the table:
esame=> \d libro
Table = libro
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| titolo | varchar() not null |
80 |
| id_libro | varchar() not null |
10 |
| id_utente | int4 |
4 |
| collocazione | varchar() not null |
10 |
| casa_edit | varchar() |
20 |
| data_publ | date |
4 |
| tipo | int4 |
4 |
| difetto | varchar() |
40 |
| id_libro_key | int4 not null default nextval ( |
4 |
+----------------------------------+----------------------------------+-------+esame=> \d libro_autore
Table = libro_autore
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| id_libro | varchar() not null |
10 |
| id_autore | int4 not null |
4 |
+----------------------------------+----------------------------------+-------+"Il divertimento e' giusto se la scimmia ci prende gusto"
--
Italian Linux Press: http://ziobudda.enter.it/ILP/
--
Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group
michel@michel.enter.it - http://ziobudda.enter.it/
Linux Problem? Ask to linux@media.dsi.unimi.it
"/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple"
I don't know why you are experiencing a problem here but I think I can
speed up that query. Let's try a rewrite.
SELECT * FROM libro l WHERE EXISTS(
SELECT la.id_libro FROM libro_autore la, autore a
WHERE la.id_libro = l.id_libro_key AND
la.id_autore = a.id_autore AND
((a.cognome = 'King' AND a.nome = 'Stephen') OR
(a.cognome = 'Clancy' AND a.nome = 'Tome'))
See if that gives you what you are expecting.
Show quoted text
-----Original Message-----
Hi, i have this query:
select * from libro where id_libro_key in (select id_libro from
libro_autore where id_auto
re in (select id_autore from autore where ((cognome = 'King' and nome
=
'Stephen') OR (cog
nome = 'Clancy' and nome = 'Tom'))))\g
that return me this error:
ERROR: There is no operator '=' for types 'int4' and 'varchar'
You will either have to retype this query using an explicit
cast,
or you will have to define the operator using CREATE OPERATORbut if I split the query in 2 part I obtain:
1)select id_libro from libro_autore where id_autore in (select
id_autore
from autore where
((cognome = 'King' and nome = 'Stephen') OR (cognome = 'Clancy' and
nome
= 'Tom')))\g
id_libro
--------
11
12
(2 rows)2)esame=> select * from libro where id_libro_key in ( 11,12)\g titolo |id_libro|id_utente|collocazione|casa_edit| data_publ|tipo|difetto|id_libro_ke y ------------+--------+---------+------------+---------+----------+---- +-------+----------- - It |d1 | |d1 |apo |05-12-1940| 1| | 1 1 Il talismano|s1 | |d1 |aop2 |05-12-1985| 1| | 1 2 (2 rows)why I can not make the union/join ?
where I'm wrong ?
these are the table:
esame=> \d libroTable = libro +----------------------------------+---------------------------------- +-------+ | Field | Type | Length| +----------------------------------+---------------------------------- +-------+ | titolo | varchar() not null | 80 | | id_libro | varchar() not null | 10 | | id_utente | int4 | 4 | | collocazione | varchar() not null | 10 | | casa_edit | varchar() | 20 | | data_publ | date | 4 | | tipo | int4 | 4 | | difetto | varchar() | 40 | | id_libro_key | int4 not null default nextval ( | 4 | +----------------------------------+---------------------------------- +-------+esame=> \d libro_autore
Table = libro_autore +----------------------------------+---------------------------------- +-------+ | Field | Type | Length| +----------------------------------+---------------------------------- +-------+ | id_libro | varchar() not null | 10 | | id_autore | int4 not null | 4 | +----------------------------------+---------------------------------- +-------+"Il divertimento e' giusto se la scimmia ci prende gusto"
--
Italian Linux Press: http://ziobudda.enter.it/ILP/
--
Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group
michel@michel.enter.it - http://ziobudda.enter.it/
Linux Problem? Ask to linux@media.dsi.unimi.it
"/dev/ziobudda: access to /var/tmp/beer denied, use
/var/adm/pineapple"
Import Notes
Resolved by subject fallback