Re: [GENERAL] Searching for bare letters
On Sun, 2011-10-02 at 01:25 +0200, Reuven M. Lerner wrote: Hi, everyone. I'm working on a project on PostgreSQL 9.0 (soon to be upgraded to 9.1, given that we haven't yet launched). The project will involve numerous text fields containing English, Spanish, and Portuguese. Some of those text fields will be searchable by the user. That's easy enough to do; for our purposes, I was planning to use some combination of LIKE searches; the database is small enough that this doesn't take very much time, and we don't expect the number of searchable records (or columns within those records) to be all that large. The thing is, the people running the site want searches to work on what I'm calling (for lack of a better term) bare letters. That is, if the user searches for n, then the search should also match Spanish words containing ñ. I'm told by Spanish-speaking members of the team that this is how they would expect searches to work. However, when I just did a quick test using a UTF-8 encoded 9.0 database, I found that PostgreSQL didn't see the two characters as identical. (I must say, this is the behavior that I would have expected, had the Spanish-speaking team member not said anything on the subject.) So my question is whether I can somehow wrangle PostgreSQL into thinking that n and ñ are the same character for search purposes, or if I need to do something else -- use regexps, keep a naked, searchable version of each column alongside the native one, or something else entirely -- to get this to work. Could you parse the search string for the non-English characters and convert them to the appropriate English character? My skills are not that good or I would offer more details. Any ideas? Thanks, Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Decimal vs. Bigint memory usage
Gregor On Thu, 2011-09-29 at 14:15 +0200, Gregor Vollmer wrote: Dear List, we are currently updating our application to use multiple database backends, the main backend on our site will be Postgres, though. Some of our columns exceed the limit of the 'integer' type, now we are discussing the alternatives. Personally, I would use bigint since it should suffice in most cases, but using decimal is under discussion, too, because our modules would be able to precisely specify their required column sizes. We do not do any arithmetic on the columns, only saving and retrieval, is decimal as fast as bigint in that case? How does decimal store the number internally, is it a fixed size through-out all rows and how does it compare to bigint? Thanks in advance, Gregor My preference is to use bigint since you are already using integer. It depends on what the values mean and how you use them whether decimal could be used. Are these values used at all in any queries? -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Help needed in Search
On Wed, 2011-09-28 at 12:33 +0530, Siva Palanisamy wrote:
Hi All,
I am trying to retrieve the contact names based on the keyed search
string. It performs good for the English alphabets and behaves
strangely for special chars such as _,/,\,%
The % character is used by SQL as the wild card for searching. To search
for Mecklenburg county (in North Carolina) from a list of US counties
you might try meck% to find all the counties that start with meck. (%
meck for those that end in meck and %meck% for any that contain meck).
The use of % is in the SQL standard and is used by all the all the SQL
dialects I am familiar with.
My query in the function is similar to
SELECT contact_name FROM contacts WHERE LOWER(contact_name) LIKE
LOWER('_McDonald%') ORDER BY LOWER(contact_name) ASC LIMIT 1;
I would expect you to get anything that starts with _mcdonald
(_mcdonald, james) not say james mcdonald (%mcdonald% would work) . The
underline (_) is not the same as a space. To search with leading space
try '% mcdonald%'
It looks like, during searching, it retrieves all the contact names
instead of the desired. The similar bizarre happens for the above
mentioned special chars. I need to support these. How do I educate
postgres to consider these chars? Please guide me.
Thanks and Regards,
Siva.
__
::DISCLAIMER::
---
The contents of this e-mail and any attachment(s) are confidential and
intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily
reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure,
modification, distribution and / or publication of
this message without the prior written consent of the author of this
e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. Before opening any mail and
attachments please check them for viruses and defect.
---
--
Jay Lozier
jsloz...@gmail.com
Re: [GENERAL] Download States and Capitals Database
On Tue, 2011-09-27 at 12:18 +0530, Adarsh Sharma wrote: Dear all, I googled a lot and find data of all countries , cities , location etc from Geo Spatial websites but I am able to find the data that shows all states their respective capitals in world. Please let me know if anyone as prior information about this ? Thanks Try this link from Wikipedia http://en.wikipedia.org/wiki/World_capital_cities_by_country . I just searched for world capitals by country. -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Download States and Capitals Database
On Wed, 2011-09-28 at 09:39 +0530, Adarsh Sharma wrote: This Link gives the capitals of all countries but I need the states and their capitals in all these countries too.. But I think this is not possible because very few countries are divided into states and some into cantons, county etc. Thanks planas wrote: On Tue, 2011-09-27 at 12:18 +0530, Adarsh Sharma wrote: Dear all, I googled a lot and find data of all countries , cities , location etc from Geo Spatial websites but I am able to find the data that shows all states their respective capitals in world. Please let me know if anyone as prior information about this ? Thanks Try this link from Wikipedia http://en.wikipedia.org/wiki/World_capital_cities_by_country . I just searched for world capitals by country. -- Jay Lozier jsloz...@gmail.com I would try Wikipedia and search for lists of states, cantons, provinces, etc of each country. The lists I have needed have had the capitals listed for each state, etc. Tedious but if you have to google for list it might be easier. -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Quick-and-Dirty Data Entry with LibreOffice3?
Rich On Mon, 2011-09-26 at 11:38 -0700, Rich Shepard wrote: Rather than writing an application right now to enter data into a table I thought of trying LibreOffice as a front end. But, it doesn't seem to work as OO.o did. This leads to two questions: 1) Can someone show me how to use LO as a front end to a postgres table? 2) Is there another tool suitable for a linux box for some data entry work? Rich Which version of LO are you using and which Linux? I have some experience with using LO as a front-end when pgAdmin is not the best tool. I have noticed that with Ubuntu you need to use the 3.3.x series from the repository. There is pg connector for LO 3.3.x in the repository. Getting 3.4.x to connect is more of a pain in Ubuntu. I think the correct driver is libreoffice-sdbc-postgresql. The steps I use are 1 Open Base and select connect to an existing database. 2 Scroll down to postgresq and select (it will be there if the correct driver is present) 3 On the next screen you will need to enter the connection information (dbname = , host = ) 4 On the next screen enter your user information 5 On the next screen I usually enter the defaults 6 You should be in Base. -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Problem importing a csv file
On Sun, 2011-08-28 at 03:53 -0200, pasman pasmański wrote: What if you run this query using psql? -- pasman It did import the data and it gave no error messages -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Problem importing a csv file
On Sun, 2011-08-28 at 03:53 -0200, pasman pasmański wrote: What if you run this query using psql? -- pasman When I run the following: DROP Table ContactUpdates CREATE TABLE ContactUpdates ( VendorID integer NOT NULL, LastName character varying(45), FirstName character varying(45), CONSTRAINT ContactUpdates_pkey PRIMARY KEY (VendorID ) ) WITH ( OIDS=FALSE ); ALTER TABLE ContactUpdates OWNER TO postgres; The table is created with proper columns Then running COPY CountUpdates FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv' DELIMITER ',' CSV gives this error message ERROR: relation countupdates does not exist ** Error ** ERROR: relation countupdates does not exist SQL state: 42P01 Adding COPY CountUpdates FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv' DELIMITER ',' CSV ERROR: relation CountUpdates does not exist ** Error ** ERROR: relation CountUpdates does not exist SQL state: 42P01 But the file exists, it was previously created. What is the problem? -- Jay Lozier jsloz...@gmail.com
[GENERAL] Problem importing a csv file
Hi, I am attempting to import a csv file into a predefined, empty table using the following commands: COPY ContactUpdates FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv' WITH DELIMITERS ',' CSV When testing the query I get the following error message ERROR: syntax error at or near COPY LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )COPY Conta... ^ ** Error ** ERROR: syntax error at or near COPY SQL state: 42601 Character: 59 I have been able to determine what the error is. Researching the question does not give me any clue what is wrong or what the syntax error is. Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII -- Jay Lozier jsloz...@gmail.com
