Re: [SQL] Clarification With Money data type
it's better to use some sort of numeric or integer and store amounts of cents, not dollars.
[SQL] Odd query behavior
Hello, I've come across an odd situation. I've had access to a database where a the following happens: " SELECT * FROM table WHERE name LIKE 'abc%' "returns 2 rows... but... " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'abc%') " returns 0 rows... I've also tried doing a join on the column to another table that has what appears to be the same data, and the join comes out with zero rows. Weirder yet, I took the hash of both of the values and it came out identical. Now, I know the first thing you'll ask is if you can get a copy of the data to re-produce it, and the problem is, I got a copy of the data in question, and loaded into another server and it works fine. Both queries return 2 rows. I do know that server it was running on was Finnish. The database uses a UTF8 encoding though, so I don't know what that would make any difference. Any ideas about things I should be looking at? Thanks Dan The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. Please see our legal details at http://www.cryptocard.com CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business number 80531 6478. CRYPTOCard Europe is limited liability company registered in England and Wales (with registered number 05728808 and VAT number 869 3979 41); its registered office is Aztec Centre, Aztec West, Almondsbury, Bristol, UK, BS32 4TD
Re: [SQL] Odd query behavior
Any views involved, or separate users/roles? On 03/12/2010 08:41 AM, Dan McFadyen wrote: > Hello, > > > > I've come across an odd situation. I've had access to a database where a > the following happens: > > > > " SELECT * FROM table WHERE name LIKE 'abc%' "returns 2 rows... > > > > but... > > > > " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name > LIKE 'abc%') " returns 0 rows... > > > > I've also tried doing a join on the column to another table that has > what appears to be the same data, and the join comes out with zero rows. > Weirder yet, I took the hash of both of the values and it came out > identical. > > > > Now, I know the first thing you'll ask is if you can get a copy of the > data to re-produce it, and the problem is, I got a copy of the data in > question, and loaded into another server and it works fine. Both queries > return 2 rows. > > > > I do know that server it was running on was Finnish. The database uses a > UTF8 encoding though, so I don't know what that would make any difference. > > > > Any ideas about things I should be looking at? > > > > Thanks > > > > Dan > > > > The information transmitted is intended only for the person or entity to > which it is addressed and may contain confidential and/or privileged > material. Statements and opinions expressed in this e-mail may not > represent those of the company. Any review, retransmission, > dissemination or other use of, or taking of any action in reliance upon, > this information by persons or entities other than the intended > recipient is prohibited. If you received this in error, please contact > the sender immediately and delete the material from any computer. Please > see our legal details at http://www.cryptocard.com CRYPTOCard Inc. is > registered in the province of Ontario, Canada with Business number 80531 > 6478. CRYPTOCard Europe is limited liability company registered in > England and Wales (with registered number 05728808 and VAT number 869 > 3979 41); its registered office is Aztec Centre, Aztec West, > Almondsbury, Bristol, UK, BS32 4TD -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Odd query behavior
"Dan McFadyen" writes: > I've come across an odd situation. I've had access to a database where a > the following happens: What are the plans for the two queries? If either one involves use of an index, does disabling the index change the results? > I do know that server it was running on was Finnish. The database uses a > UTF8 encoding though, so I don't know what that would make any > difference. The other line of thought that suggests is that you've got an encoding/locale mismatch that's messing up text comparisons. What's the server's LC_CTYPE setting, and what PG version is this anyway? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
