[SQL] Regarding boolean datatype
Hello all,
this is my first interaction with this list. I have a
problem. I was using MSSQL Server as db, now i m
changing to Postgres. I know that the bit type in
MSSQLserver is boolean in Postgres.
So, i have the following query and getting the
following error:Why???
strSql = " Update parts Set name = ? , description = ?
world_market = ?, part = ? , unit_of_measure = ?
Where depot= ?;
prepStmt = conn.prepareStatement(strSql);
prepStmt.setString( 1, Pname) ;
prepStmt.setString( 2, Pdesc) ;
prepStmt.setBoolean(3, BoolWM) ;
prepStmt.setString( 4, strPart);
prepStmt.setString( 5, strUoM);
prepStmt.setString( 6, strDepot);
This is how i m assigning value to BoolWM,
boolean BoolWM ;
if ( WM.equals("False")) {
BoolWM = false ;
} else {
BoolWM = true;
}
Error is:::
SQLError ERROR: Unable to identify an operator '~~'
for types 'boolean' and 'unknown'
You will have to retype this query using an explicit
cast
I know, somewhere i m making mistake, but where??
Moreover, if i m updating the database with the above
query, the db is giving the error and deleting the
data from the db. As per my understanding it should
automatically rollback. What is the problem???
What are the other coding rules for Postgres??
Please guide.
Thanks and regards
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
---(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] Backend message type 0x50 arrived while idle
Hi Phil It also happened to me last week. I found the problem is that you cannot share the same connection among processes unless you implement some kind of locking (semaphores, ...). More clearly, I had this problem. Query QA lasts 5 seconds to perform, and returns answer AA. Query QB lasts 10 seconds and returns AB. B is a child process of A. I launch the parent A. Performs QA. Waits for answer. I launch the child B. Performs QB. Waits for answer. Server returns AA, but it gets caught by B !! So, solutions are: - You open a new backend connection for each process. - You make each process lock before sending QA, and unlock when receiving AA. Hope it helps !! Thrasher Berman, Phil wrote: To all- I am using Postgres version 6.5.1 in a multithreaded program which is written in C. All inserts and updates into any tables are done within the child process, and there are retrieves from tables both in the parent and child processes. Additionally, the child process will insert into and update a table, and the parent process will retrieve from the same table. There are loops in both the parent and child processes, so every postgres call may be made multiple times, with a significant amount of time (seconds or minutes) between calls. Invariably, I get a message "backend message type 0x50 arrived while idle" before the process completes, although the point where I get this message can vary somewhat. Can anyone tell me what this message means, why I am getting it, and/or how to avoid getting it in the future? Thanks in advance, Phil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Query about table and catalog
> I've got a table with several fields. Among others there are the fields > 'soil1', 'soil2', and 'soil3' that are char type. A row can have an empty > value in one of these fields, or the three fields can have valid values: > > cod_grass |suelo1|suelo2 | suelo3 > ---+--++- > 2590 | Xerosoles petrocalcicos| | >181 | Xerosoles calcicos | | >265 | Xerosoles petrocalcicos | | >593 | Zona urbana | | > 1112 | Cambisoles calcicos | | > 2 | Litosoles | | > 3 | Xerosoles calcicos|| > 4 | Litosoles | Rendsinas aridicas | > 5 | Xerosoles petrocalcicos | | > 6 | Litosoles | | > 7 | Regosoles calcaricos | Xerosoles calcicos > ... > > In other table I've got a catalog of posible soil types, assigning an integer > value to each of possible soil types. > > tipo_suelo | cod_tipo_suelo > -+ > Arenosoles albicos | 1 > Cambisoles calcicos | 2 > Cambisoles eutricos | 3 > > Is it possible to prepare a query that show the contents of the table of > soils and aditional columns after each of the soils fields, showing the > corresponding numerical code for that soil, extracted from the catalog? > > I just know how to do this for one of the soils: > > SELECT cod_grass, suelo1,cod_tipo_suelo AS cod_suelo1 FROM > suelos,suelos_catalogo WHERE suelo1=tipo_suelo ORDER BY cod_grass; > > But I would like to do the same for the three at a time. > Try this (untested) or something similar: SELECT cod_grass, suelo1, st1.cod_tipo_suelo AS cod_suelo1 suelo2, st2.cod_tipo_suelo AS cod_suelo2 suelo3, st3.cod_tipo_suelo AS cod_suelo3 FROMsuelos, suelos_catalogo st1, suelos_catalogo st2, suelos_catalogo st3 WHERE suelo1=st1.tipo_suelo AND suelo2=st2.tipo_suelo AND suelo3=st3.tipo_suelo ORDER BY cod_grass; I'm curious why you did not design the tables vice versa. Table "suelos" just holding "cod_tipo_suelo", so queries like the above would run much faster, because only integers have to be compared instead of strings. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Regarding boolean datatype
On Thursday 05 Dec 2002 9:11 am, kanika singh wrote: > Hello all, > > this is my first interaction with this list. I have a > problem. I was using MSSQL Server as db, now i m > changing to Postgres. I know that the bit type in > MSSQLserver is boolean in Postgres. There's also a bit type if that's really what you want, but in this case I think boolean is right. PS - if you get error messages again, please let us know what version and client you are using (e.g. PostgreSQL v7.2.1 from jdbc) > strSql= " Update parts Set name = ? , description = ? > world_market = ?, part = ? , unit_of_measure = ? > Where depot= ?; > > prepStmt = conn.prepareStatement(strSql); > prepStmt.setString( 1, Pname) ; > prepStmt.setString( 2, Pdesc) ; > prepStmt.setBoolean(3, BoolWM) ; [snip] > SQLError ERROR: Unable to identify an operator '~~' > for types 'boolean' and 'unknown' > You will have to retype this query using an explicit > cast Are you sure this error goes with this query? I don't see any use of LIKE here (which is what ~~ does for you). > I know, somewhere i m making mistake, but where?? > Moreover, if i m updating the database with the above > query, the db is giving the error and deleting the > data from the db. As per my understanding it should > automatically rollback. What is the problem??? I find this unlikely. If Postgresql was in the habit of deleting data without being asked to there'd be mention of it. I suggest you turn query logging on (in your postgresql.conf file) while you try to reproduce this. That will show you what is really happening. > What are the other coding rules for Postgres?? Manuals are at http://www.postgresql.org/ Extra info at http://techdocs.postgresql.org/ (including some online books) Mailing lists you know about. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Regarding boolean datatype
Thanks for the reply. I will definitely look into it. and will come back to you. thanks and regards, Kanika --- Richard Huxton <[EMAIL PROTECTED]> wrote: > On Thursday 05 Dec 2002 9:11 am, kanika singh wrote: > > Hello all, > > > > this is my first interaction with this list. I > have a > > problem. I was using MSSQL Server as db, now i m > > changing to Postgres. I know that the bit type in > > MSSQLserver is boolean in Postgres. > > There's also a bit type if that's really what you > want, but in this case I > think boolean is right. > PS - if you get error messages again, please let us > know what version and > client you are using (e.g. PostgreSQL v7.2.1 from > jdbc) > > > strSql = " Update parts Set name = ? , description > = ? > > world_market = ?, part = ? , unit_of_measure = ? > > Where depot= ?; > > > > prepStmt = conn.prepareStatement(strSql); > > prepStmt.setString( 1, Pname) ; > > prepStmt.setString( 2, Pdesc) ; > > prepStmt.setBoolean(3, BoolWM) ; > [snip] > > SQLError ERROR: Unable to identify an operator > '~~' > > for types 'boolean' and 'unknown' > > You will have to retype this query using an > explicit > > cast > > Are you sure this error goes with this query? I > don't see any use of LIKE here > (which is what ~~ does for you). > > > I know, somewhere i m making mistake, but where?? > > Moreover, if i m updating the database with the > above > > query, the db is giving the error and deleting the > > data from the db. As per my understanding it > should > > automatically rollback. What is the problem??? > > I find this unlikely. If Postgresql was in the habit > of deleting data without > being asked to there'd be mention of it. I suggest > you turn query logging on > (in your postgresql.conf file) while you try to > reproduce this. That will > show you what is really happening. > > > What are the other coding rules for Postgres?? > > Manuals are at http://www.postgresql.org/ > Extra info at http://techdocs.postgresql.org/ > (including some online books) > Mailing lists you know about. > > -- > Richard Huxton > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] master-detail relationship and count
On Fri, Nov 29, 2002 at 02:39:50PM +, Gary Stainburn wrote: > I've worked out a way of doing it by vreating a view for the tally info as: > > create view link_tally as > select lklid, lktype, count(*) from links group by lklid, lktype; > > and then doing: > > select r.rtid, r.rtname, l.count from route r > left outer join link_tally l on r.rtid = l.lklid and l.lktype = 'R'; > > (this works apart from the coalesce bit which I haven't worked out where to > put yet, and for the moment isn't important as NULL is okay as a result). > > However, I still can't get it to work straight from the tables. The nearest > I'ev got is: > > select r.rtid, r.rtname, subsel.cnt from route r, > (select r2.rtid as rid, count(lnk.lkid) as cnt from route r2, links lnk > where lnk.lktype='R' and lnk.lklid = r2.rtid group by r2.rtid) as subsel > left outer join subsel on r.rtid = subsel.rid; Hmm, I think this should work: select r.rtid, r.rtname, subsel.cnt from route r left outer join (select r2.rtid as rid, count(lnk.lklid) as cnt from route r2, links lnk where lnk.lktype='R' and lnk.lklid = r2.rtid group by r2.rtid) as subsel on r.rtid = subsel.rid; At least, it won't error. I don't have any test data to see if it returns what you want. Ross ---(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
