[SQL] Casting integer to boolean
Hi, I am using postgresql 7.2.1. How do i cast an integer value to boolean? I did try the below sequence of SQLs and was little bit confused, by the way it behaves. It casts the integer value to boolean in one case but not ever again. bhuvan=> SELECT count(*)::int::boolean from my_table; ERROR: Cannot cast type 'integer' to 'boolean' bhuvan=> -- The SQL similar to the above SQL is my requirement bhuvan=> SELECT true where (1); ERROR: WHERE clause must return type boolean, not type integer bhuvan=> SELECT true where (1::boolean); bool -- t (1 row) bhuvan=> SELECT true where (1::int::boolean); ERROR: Cannot cast type 'integer' to 'boolean' bhuvan=> I donot know whether i am wrong or its a bug. I request someone to correct me if i am wrong or please suggest me the right way to cast an integer to boolean as, returning true for non-zero value, false otherwise. regards, bhuvaneswaran ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Casting integer to boolean
On Fri, 16 Aug 2002, Bhuvan A wrote: > How do i cast an integer value to boolean? You can always do something like this: select not count(*) = 0 from my_table; Basically, for any integer i, convert to boolean with: not i = 0 -- Tod McQuillin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Casting integer to boolean
> select not count(*) = 0 from my_table; > > Basically, for any integer i, convert to boolean with: not i = 0 Or i != 0 of course... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] references definition to multi-field primary key
Hi all, I've got some fuel pumps, each of which sell a number of fuel grades - one nozzle per grade, each nozzle connected to a specified tank containing that grade. I can define the tanks, the pump numbers, and the pump grade/nozzle config using the tables below. create table grades ( -- different types of fuel sold gid character, gdesc varchar(20), gprice float, primary key (gid) ); create table tanks ( -- storage tanks tid int4 not null, tgrade character references grades(gid), primary key (tid) ); create table pumps ( -- list of pumps pid int4 not null, primary key (pid) ); create table pgrades ( -- list of nozzles/grades per pump pgpid int4 not null references pumps(pid), pgnozzle int4 not null, pgtank int4 not null references tanks(tid), primary key (pgpid, pgseq) ); My problem is that I want to be able to define a 'Pump Readings' table to show per pump/nozzle the opening and closing reading. However, my problem is that I don't know how to define the references so that I can only create a preadings row for an existing pgrages entry. Here's the table less the required references entry. create table preadings ( -- daily reading per pump/nozzle prdate date not null, prpump int4 not null prnozzle int4, propen integer, prclose integer, primary key (prdate, prpump, prseq) ); I only want the insert to work if prpid matches pgpid and prnozzle matches pgnozzle. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] references definition to multi-field primary key
On Fri, Aug 16, 2002 at 16:15:57 +0100, Gary Stainburn <[EMAIL PROTECTED]> wrote: > > I only want the insert to work if prpid matches pgpid and prnozzle matches > pgnozzle. There are several typos in your sample that make it hard to know for sure what you want to do. However I suspect you just want to add a foreign key clause to your table definition. These can refer to more than one column (unlike references). My memory is that you need a matching unique index defined for the table you are referencing. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Results from EXECUTE
How can I obtain results from an EXECUTE statement within a pl/PgSQL function? E.g., something like value := EXECUTE ''SELECT '' || quote_ident(field_name) || '' FROM ...''; Thanks in advice. -- Fduch M. Pravking ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Results from EXECUTE
Am Freitag, 16. August 2002 18:26 schrieb Alexander M. Pravking: > How can I obtain results from an EXECUTE statement > within a pl/PgSQL function? >From the docs: plpgsql-statements.html postgres 7.2.1 "The results from SELECT queries are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So, the only way to extract a result from a dynamically-created SELECT is to use the FOR-IN-EXECUTE form described later." Look at the example in the docs. janning -- Planwerk 6 /websolutions Herzogstrasse 86 40215 Duesseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Difference between is true and = 't' in boolean feild. & bitmap indexes
Any can anyone explain me why in a query of a boolean feild "is ture" does not indexes where as = 't' does? is "is true" not a more standard SQL than " = 't' ". Also is there any working implementation of BITMAP INDEXES in postgresql as found in ORACLE? regds mallah. tradein_clients=> explain analyze select c.email,date(a.generated),c.expired from eyp_rfi a join users b on (a.receiver_uid = b.userid) join grace_mytradeindia c on (b.email = c.email) where generated_date = '2002-08-17' and c.expired is true; NOTICE: QUERY PLAN: Hash Join (cost=1948.01..2214.60 rows=14 width=67) (actual time=9.32..40.03 rows=12 loops=1) -> Seq Scan on grace_mytradeindia c (cost=0.00..246.17 rows=4051 width=26) (actual time=0.65..27.76 rows=5372 loops=1) -> Hash (cost=1947.46..1947.46 rows=218 width=41) (actual time=2.66..2.66 rows=0 loops=1) -> Nested Loop (cost=0.00..1947.46 rows=218 width=41) (actual time=0.16..2.57 rows=31 loops=1) -> Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..677.81 rows=218 width=12) (actual time=0.09..0.57 rows=50 loops=1) -> Index Scan using users_pkey on users b (cost=0.00..5.80 rows=1 width=29) (actual time=0.03..0.03 rows=1 loops=50) Total runtime: 40.26 msec EXPLAIN tradein_clients=> explain analyze select c.email,date(a.generated),c.expired from eyp_rfi a join users b on (a.receiver_uid = b.userid) join grace_mytradeindia c on (b.email = c.email) where generated_date = '2002-08-17' and c.expired = 't'; NOTICE: QUERY PLAN: Hash Join (cost=1948.01..2144.70 rows=14 width=67) (actual time=6.26..49.92 rows=12 loops=1) -> Index Scan using grace_mytradeindia_exp on grace_mytradeindia c (cost=0.00..176.27 rows=4051 width=26) (actual time=0.03..37.53 rows=5372 loops=1) -> Hash (cost=1947.46..1947.46 rows=218 width=41) (actual time=2.15..2.15 rows=0 loops=1) -> Nested Loop (cost=0.00..1947.46 rows=218 width=41) (actual time=0.15..2.06 rows=31 loops=1) -> Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..677.81 rows=218 width=12) (actual time=0.09..0.48 rows=50 loops=1) -> Index Scan using users_pkey on users b (cost=0.00..5.80 rows=1 width=29) (actual time=0.02..0.03 rows=1 loops=50) Total runtime: 50.16 msec EXPLAIN tradein_clients=> -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
