Re: [SQL] seemingly slow for-loop in plpgsql
> Why in the world are you using a for-loop for this at all? It would be > tremendously faster as a single SQL command: > > update duplicates set hashcode = rtrim(hashcode, E'\n') where > length(hashcode) = 33; Thank you. I was "caught" in the plpgsql-lane. This is of course much simpler and much faster. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How do I get min and max from an array of floating point values
Hello
2008/9/2 pw <[EMAIL PROTECTED]>:
> Hello,
>
> Is it possible to get the min and max from an array of
> floating point values?
>
> The following doesn't return the min of the array values
> it simply returns the complete array...(??)
>
> SELECT min(string_to_array('1,2,3,4,5,6,7',',')::float[]);
>
> Thanks
>
> Peter
>
you should function
create function minimum(anyarray)
returns anyelement as $$
select min($1[i]) from generate_series(array_lower($1,1),
array_upper($1,1)) g(i);
$$ language sql immutable strict;
reagards
Pavel Stehule
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order of rows in update
On Tue, Sep 2, 2008 at 11:56 PM, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > I am using my version of DB mirror to do some kind of "Conditional row > grained + FK dependency oriented lazy replication". > (The logic behind it is the cost of comms, because the slaves are servers in > vessels in all 7 seas, where communication is done > via uucp connections over satellite dilaup, and the costs are really high, so > the aim was to minimize the costs. > Regarding high costs, It was so in 2003/2004 when we started > designing/deploying this system and the prices are still high > today.) If you are ever in LA, you should come to a LAPUG meeting. One of our members also replicates over long distances. He is replicating from California to China using a highly configured slony in combination with other software. His software analyzes the possible routes he has to find the routes with the best latency. He has configures a revolving Master-Slave replication. Perhaps you guys to share notes? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How do I get min and max from an array of floating point values
If you use intarray type it is convenient to call buil-in intarray
functions for your purpose.
http://www.postgresql.org/docs/8.3/static/intarray.html
SELECT t.a[1] AS "min", t.a[array_upper(t.a, 1)] AS "max"
FROM (SELECT sort(string_to_array('2,3,4,15,6,7',',')::int[]) AS a)t;
--
Best regards, Yuri.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Case-insensitive string prefix matching with parameterized query
Apologies if this posts twice... I've run into issues with the listserv lately. I am implementing an autosuggest-style text input for my site, where a user can start typing the name of a thing (call it a 'Foo'), and get a list of all things whose name starts with the string the user typed. For example, if the user types 'car', the database might return the names 'Car', 'Caramel', 'Carbon', etc. I want the search to be case- insensitive. Just to have some code, here's some bare-bones info: CREATE TABLE foo ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL UNIQUE, other_info VARCHAR ); Initially, I just did a naive match using ilike: SELECT name FROM foo WHERE name ilike 'car%'; This worked alright when the table was small, but now my foo table has millions of rows, and the query takes far too long. So I created a new index on name: CREATE INDEX lower_name_idx ON foo (lower(name)); Now I can use the query: SELECT name FROM foo WHERE lower(name) like 'car%'; which runs in 2ms, which is exactly what I want. My problem comes when I execute this query from my application. I'm using Hibernate to generate the query, the code for which looks like this: return session() .createCriteria( Foo.class ) .add( Restrictions.like( "name", queryString, MatchMode.START ).ignoreCase() ) .list(); This generates the proper query... almost. It creates a parameterized query like this: SELECT id, name, other_info FROM foo WHERE lower(name) like $1; where "$1" gets substituted with (for example) 'car%'. This query runs very slowly and doesn't use the index. I think what's happening is that when the parameterized query gets created, the planner doesn't know that it's going to be matching at the beginning of the string (indicated by the trailing percent character), and so it doesn't use the index. That information comes into play when the parameter gets substituted, but by then the plan has been set. I can get it to work if I directly create an HQL query like this: String hql = "from Foo where lower(name) like '" + queryString.toLowerCase() + "%'"; I could also generate a SQL query that does the same thing; the fact that I used HQL for this example is incidental. The point is that the query isn't parameterized, so it generates the plan I want. This works fine, but I don't like doing string manipulations like this. Plus I have to now take care to escape the string to avoid injection attacks. Is there any SQL or PostgreSQL string function I could use that would allow me to have a parameterized query that uses my index? Alternatively, is there another index definition that would work? Is there any Hibernate-functionality I've overlooked that would do what I want without having to do string manipulations? Is there something simple I've missed? Also, if string manipulation is the best way to do this, what all would I have to make sure I've escaped? Quotes, single quotes, and backslashes, but anything else I'm overlooking? Thanks for any suggestions, Chris -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order of rows in update
Στις Wednesday 03 September 2008 15:28:04 ο/η Richard Broersma έγραψε: > On Tue, Sep 2, 2008 at 11:56 PM, Achilleas Mantzios > <[EMAIL PROTECTED]> wrote: > > I am using my version of DB mirror to do some kind of "Conditional row > > grained + FK dependency oriented lazy replication". > > (The logic behind it is the cost of comms, because the slaves are servers > > in vessels in all 7 seas, where communication is done > > via uucp connections over satellite dilaup, and the costs are really high, > > so the aim was to minimize the costs. > > Regarding high costs, It was so in 2003/2004 when we started > > designing/deploying this system and the prices are still high > > today.) > > If you are ever in LA, you should come to a LAPUG meeting. One of our > members also replicates over long distances. He is replicating from > California to China using a highly configured slony in combination > with other software. His software analyzes the possible routes he has > to find the routes with the best latency. He has configures a > revolving Master-Slave replication. Thanx for your help. I live in Greece, but it would be nice some time to visit America/LA :) > > Perhaps you guys to share notes? > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
