Thread: Special characters in varchar/text fields
The question I tried to post yesterday never made it to the list at all, but in the spirit of blind optimism I'm trying again with a new one. I'm transferring an existing FileMaker Pro database into PostgreSQL for an art gallery, and a number of the artist's names and some image titles are French, and thus contain accents or other special characters. FileMaker directly supports accented characters as well as styled text (bold, italics etc.), but PostgreSQL to my knowledge does not, so for these things to show up properly on the web, the special characters need to be converted into the proper ASCII codes (and any text formatting needs to be done in HTML). While it probably wouldn't be too much of a nuisance to do a global replace for accented characters in a text editor after exporting the FileMaker databases as tab-delimited text, we still have the question of how to handle new input into the database once it's up, which will be through a PHP web form. The new information will probably be entered by a motley assortment of gallery staff, interns and volunteers, many of whom will not be all that computer literate, so expecting them to remember all the ASCII codes is a bit of a stretch (the gallery mostly runs on Macs rather than PCs, so they don't need to know the ASCII codes to type the accented characters in the first place, just simple keyboard commands). Is there any way to either (a) turn special characters into the appropriate ASCII codes when the form is processed, or (b) enable PostgreSQL to handle them directly? (b) would probably be the better of the two options, if possible... Thanks, Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
Lynna Landstreet writes: > FileMaker directly supports accented characters as well as styled text > (bold, italics etc.), but PostgreSQL to my knowledge does not, so for > these things to show up properly on the web, the special characters need > to be converted into the proper ASCII codes (and any text formatting > needs to be done in HTML). PostgreSQL supports all sorts of character sets perfectly well. Read the chapter on localization in the documentation to learn about how to set it up. (It should, in fact, work fine by default, but you didn't say whether you tried at all.) -- Peter Eisentraut peter_e@gmx.net
On Friday 06 Jun 2003 9:20 pm, Lynna Landstreet wrote: > The question I tried to post yesterday never made it to the list at all, > but in the spirit of blind optimism I'm trying again with a new one. Well you made it this time. [snip question about handling accents] > Is there any way to either (a) turn special characters into the appropriate > ASCII codes when the form is processed, or (b) enable PostgreSQL to handle > them directly? (b) would probably be the better of the two options, if > possible... OK - PostgreSQL actually has quite good handling for this sort of stuff - the terms you need to search the archives on are "locale" or "localization" (or localisation). There are several aspects to this sort of thing: 1. What characters can I store? 2. How do I sort text? 3. How do I display dates? 4. How do I display currency? etc. There's a whole chapter on this in the manuals (Localization) - read it carefully, especially the bit about setting encodings when creating the database. The two key questions you'll want to ask yourself are 1. What character set do I want (ISO 8859-1/Unicode etc) 2. Do I want to sort by a particular language? Best of luck - this and timezones are a real PITA - not so much PGs fault as the universe's. -- Richard Huxton