Hi,
Just wondering what kind of execute statement (within a function) i should
use to force the planner to use the index for the following?:
SELECT pcode searchmatch, geometry FROM postcode
WHERE (replace(lower(pcode), ' '::text, ''::text)) LIKE
(replace((lower($1)::text),' '::text,''::text)||'%
Thanks Tom. I'll try the "EXECUTE" method as well but my dev environment is
9.2 and the planner doesn't seem to be including the index so following are
the fairly basic table/index/function details. Thanks, Andy:
TABLE (circa 300,000 rows):
===
Hi David,
Thanks, i can see the logic there. To place a constant in front, i tried
putting 'pc'||pcode in to the index and 'pc'||$1 in to the WHERE clause. It
had no effect - does the planner see this as a cheat and i need to actually
prefix the data in the tables?
Andy
--
View this message
Hi,
I have further found that it is only when passing the string in to the
function that the slow response occurs.
When i call SELECT * FROM _function(IN TEXT) it is very slow on my WHERE
clause using LIKE($1||'%') but (run in error), very fast when LIKE('some
text'||'%')
I have also created and
Hi,
I have created an index as follows:
(replace(lower(my_column), ' '::text, ''::text)
which i use in a WHERE clause against LIKE 'string%'
By using text_pattern_ops i get the index used provided i more than one
character is used in the string.
However, with the same SELECT query running wit
Hi Adrian/Mark
Thanks again for your help, i have now got the load working by setting the
encoding to WIN1252. I had been assuming i was setting it to UTF8
SET CLIENT_ENCODING TO 'WIN1252';
Andy
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp
your help so far...
Andy
From: Mark Watson-12 [via PostgreSQL]
[mailto:ml-node+s1045698n4992336...@n5.nabble.com]
Sent: 14 November 2011 20:29
To: LPlateAndy
Subject: Re: encoding and LC_COLLATE
De : [hidden email]
[mailto:[hidden email]] De la part de Adrian Klaver
>Envoyé :
t just because i'm verifying what's incoming, it doesn't mean
it's going to go into a database which doesn't support it?
Let me know if i'm missing something!
Cheers
Andy
From: Adrian Klaver-3 [via PostgreSQL]
[mailto:ml-node+s1045698n4991012...@n5.nab
Thanks Adrian,
Apologies, i'd assumed specifying "CSV" would work just for actual CSV files
with comma separation.
Thanks again
Andy
From: Adrian Klaver-3 [via PostgreSQL]
[mailto:ml-node+s1045698n4990887...@n5.nabble.com]
Sent: 14 November 2011 14:24
To: LPlateA
Hi,
My apologies for the previous post, i reread the documentation and realised
that a "CSV" load can actually use a file with delimiters other than a
comma.
As such, i have answered my own question.
Thanks
Andy
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/issue-wi
Hi,
Using COPY to bring data into a table. It uses "|" which i allow for with a
command such as:
COPY mydata FROM E'C:\\mydata\\mydata.txt' USING DELIMITERS '|'
This works fine except for a url field which randomly includes a pipeline
"|" character. Despite the url being in quotation (" ") marks
Hi,
In response to my own question i have now read the following:
http://stackoverflow.com/questions/6579621/lc-collate-and-lc-ctype-suport-for-utf-8-in-postgresql
It seems to show that apart from a complete reinstall i should just use a
Locale of C (as i'm on Windows and POSIX wont work).
This
Hi,
I set up my postgres 9.0 install 6 months ago and generally everything is
fine but a recent data load with an e acute character failed which an
unsupported message which surprised me as we're using UTF-8.
However, i can now see that the listing for the database set up show a
restriction unde
13 matches
Mail list logo
