Yep!
it works perfectly now.
CREATE or replace VIEW sent_enquiry_eyp_iid_ip_cat2 as ((((((SELECT
eyp_rfi.rfi_id, eyp_rfi.sender_uid, eyp_rfi.receiver_uid, eyp_rfi.subject,
cast(eyp_rfi.generated as timestamp with time zone ) FROM ONLY eyp_rfi)
UNION (SELECT iid_rfi.rfi_id, iid_rfi.sender_uid, iid_rfi.receiver_uid,
iid_rfi.subject, iid_rfi.generated FROM ONLY iid_rfi))) UNION (SELECT
ip_rfi.rfi_id, ip_rfi.sender_uid, ip_rfi.receiver_uid, ip_rfi.subject,
cast(ip_rfi.generated as timestamp with time zone ) FROM ONLY ip_rfi)))
UNION (SELECT catalog_rfi.rfi_id, catalog_rfi.sender_uid,
catalog_rfi.receiver_uid, catalog_rfi.subject, catalog_rfi.generated FROM
ONLY catalog_rfi));
tradein_clients=# explain analyze SELECT rfi_id from
sent_enquiry_eyp_iid_ip_cat2 where sender_uid=38466;
QUERY
PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan sent_enquiry_eyp_iid_ip_cat2 (cost=641.62..644.67 rows=20
width=55) (actual time=0.17..0.17 rows=0 loops=1) -> Unique (cost=641.62..644.67
rows=20 width=55) (actual
time=0.17..0.17 rows=0 loops=1) -> Sort (cost=641.62..642.12 rows=204
width=55) (actual
time=0.17..0.17 rows=0 loops=1) Sort Key: rfi_id, sender_uid,
receiver_uid, subject, generated
-> Append (cost=0.00..633.80 rows=204 width=55) (actual
time=0.08..0.08 rows=0 loops=1) -> Subquery Scan
"*SELECT* 1" (cost=0.00..376.11
rows=117 width=42) (actual time=0.03..0.03 rows=0
loops=1) -> Index Scan using
eyp_sender_uid_idx on
eyp_rfi (cost=0.00..376.11 rows=117 width=42)
(actual time=0.03..0.03 rows=0 loops=1)
Index Cond: (sender_uid = 38466)
-> Subquery Scan "*SELECT* 2" (cost=0.00..81.33
rows=21 width=47) (actual time=0.02..0.02 rows=0
loops=1) -> Index Scan using
iid_sender_uid_idx on
iid_rfi (cost=0.00..81.33 rows=21 width=47)
(actual time=0.02..0.02 rows=0 loops=1)
Index Cond: (sender_uid = 38466)
-> Subquery Scan "*SELECT* 3" (cost=0.00..160.18
rows=57 width=42) (actual time=0.02..0.02 rows=0
loops=1) -> Index Scan using
ip_sender_uid_idx on
ip_rfi (cost=0.00..160.18 rows=57 width=42)
(actual time=0.02..0.02 rows=0 loops=1)
Index Cond: (sender_uid = 38466)
-> Subquery Scan "*SELECT* 4" (cost=0.00..16.19
rows=8 width=55) (actual time=0.01..0.01 rows=0
loops=1) -> Index Scan using
catalog_sender_uid_idx on
catalog_rfi (cost=0.00..16.19 rows=8 width=55)
(actual time=0.01..0.01 rows=0 loops=1)
Index Cond: (sender_uid = 38466)
Total runtime: 0.41 msec
(18 rows)
regds
mallah.
> <[EMAIL PROTECTED]> writes:
>> the column 'generated' was timestamp in 2 place and date in 2 place, i
>> wanted it in my and did a typecasting in the view below
>> but it suffers from the same problem .
>
> AFAIR it should work if you insert casts into the UNION's member
> selects. Maybe you didn't get the casting quite right? (For instance,
> "timestamp" isn't "timestamp with time zone" ...)
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: Have you searched our list
> archives?
>
> http://archives.postgresql.org
-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
