Re: [SQL] insert problem with special characters
Hi John, (added to JDBC list) 1. What is your database encoding? Does it support the unicode OK? 2. Are you sure it's getting /stored/ as a question mark rather than just displayed as such? Remember, if it is stored correctly, but you look at it from a terminal that doesn't support the character, you might be seeing an artifact of your terminal, not the underlying representation. I would suggest you try having the cent symbol pulled in via the normal route, and once it's in a java.lang.String, have java pop open a JOptionPane.showMessageDialog and show you what it thinks the String is. If it's not showing you the right character here, then the problem is way upstream of Postgres. If it displays OK here, have Java pull the value back out of the database and show it in another messageDialog; my guess is you'll see one of three things: A. Neither dialog shows the unicode OK. This means you have some encoding/decoding issues upstream of Postgres. B. The first dialog is OK, the second is not. Probably, your encoding for the database is not set right. C. Both dialogs are OK. The problem is merely an artifact of how you're seeing the unicode stuff, not a Java + Postgres problem. Best, Randall On Tuesday, May 13, 2003, at 01:18 PM, jwang wrote: Hi All, I have a web application in JSP/Servlet/PostgreSQL(7.3). I have a text input field where users can enter special cent symbol to represent cent unit just like dollar symbol. So I have to use java.net.URLDecoder.encode(text_input,"UTF-8") to fetch the request input on the server. If I just insert the encoding text into the database, it will store the text in URL-encoding format. But I want to store text in ASCII-readable format (and cent symbol is stored just like dollar symbol), rather then in URL-encoding format. If I am using java.net.URLDecoder.decode(text_input,"UTF-8") to decode the text before I do inserting, the cent symbol is stored as a question mark symbol in database. I tried to find a postgres encoding function without success. Does anyone know what the problem is and how to solve it? Thanks a lot. -John GSP Marketing Technologies, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] bad query performance
On Mon, 12 May 2003, Didrik Pinte wrote:
> Here is the query :
>
> SELECT profiles_et.username, profiles_et.name, profiles_et.firstname,
> profiles_et.email, company_et.name AS company, count(logs_et.dt) AS cnt,
> pro_invoice_addr_at.id
>
>
> FROM
> (
> (
> (
> secu.company_et JOIN secu.pro_invoice_addr_at
> ON (pro_invoice_addr_at.company = company_et.id)
> )
>
> JOIN secu.profiles_et ON (pro_invoice_addr_at.profile =
> profiles_et.id)
> )
> JOIN secu.logs_et ON (logs_et.invaddr = pro_invoice_addr_at.id)
> )
>
>
> GROUP BY profiles_et.username, profiles_et.name, profiles_et.firstname,
> profiles_et.email, company_et.name, pro_invoice_addr_at.id
>
> ORDER BY count(logs_et.dt) DESC;
>
>
>
>
> The secu_company, secu_prov_invoice_addr_at and secu_profiles contains only
> informations about the users.
>
> The query is executing in 8,6 minutes Indexes are defined on all the
> field used in the different joins.
Have you recently analyzed the tables involved?
I'd guess that the two outer sorts and the sort on logs_et are probably
the killers, but can you send explain analyze output? Looking at the
estimated row numbers and width, it seems to me that with 16Mb of sort_mem
it's going to have to sort on disk (although you don't have enough memory
to up it far enough probably if the estimates are right)
>
> Here is the query plan :
>
>
> Sort (cost=895649.54..896073.23 rows=169474 width=145)
>Sort Key: count(logs_et.dt)
>-> Aggregate (cost=831240.24..865135.10 rows=169474 width=145)
> -> Group (cost=831240.24..860898.24 rows=1694743 width=145)
>-> Sort (cost=831240.24..835477.10 rows=1694743 width=145)
> Sort Key: profiles_et.username, profiles_et.name,
> profiles_et.firstname, profiles_et.email, company_et.name,
> pro_invoice_addr_at.id
> -> Merge Join (cost=274406.73..304066.75
> rows=1694743 width=145)
>Merge Cond: ("outer".id = "inner".invaddr)
>-> Sort (cost=143.81..144.92 rows=446 width=126)
> Sort Key: pro_invoice_addr_at.id
> -> Merge Join (cost=90.27..124.18
> rows=446 width=126)
>Merge Cond: ("outer".id =
> "inner".profile)
>-> Index Scan using profiles_pk on
> profiles_et (cost=0.00..24.98 rows=449 width=66)
>-> Sort (cost=90.27..91.39
> rows=446 width=60)
> Sort Key:
> pro_invoice_addr_at.profile
> -> Merge
> Join (cost=37.82..70.65 rows=446 width=60)
>Merge Cond:
> ("outer".company = "inner".id)
>-> Index Scan using
> invaddr_at_company_idx on pro_invoice_addr_at (cost=0.00..24.68 rows=446
> width=33)
>-> Sort
> (cost=37.82..38.55 rows=291 width=27)
> Sort Key:
> company_et.id
> -> Seq Scan on
> company_et (cost=0.00..25.91 rows=291 width=27)
>-> Sort (cost=274262.92..278499.78
> rows=1694743 width=19)
> Sort Key: logs_et.invaddr
> -> Seq Scan on
> logs_et (cost=0.00..55404.43 rows=1694743 width=19)
>
>
> The computer is a Pentium III 850 Mhz with 256 mb RAM (Sort memory for
> postres is 16 mb, and 64 mb of shared memory).
---(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
