Andrus wrote:
> > SELECT * FROM foo WHERE bar::CHAR(1) = 'A';
> > SELECT * FROM foo WHERE bar::CHAR(1) = 'B';
> > SELECT * FROM foo WHERE bar::CHAR(2) = 'BC';
> > SELECT * FROM foo WHERE bar::CHAR(3) = 'ABC';
> Can you confirm that in this case Postgres 8.1 can use index created
> by
> CREATE TABL
On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote:
> >> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY);
> >>
> >> Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1
>
> >Do this instead:
>
> >CREATE TABLE foo ( bar CHAR(10) NOT NULL );
> >CREATE UNIQUE INDEX foo_bar ON foo(bar char
> SELECT * FROM foo WHERE bar::CHAR(1) = 'A';
> SELECT * FROM foo WHERE bar::CHAR(1) = 'B';
> SELECT * FROM foo WHERE bar::CHAR(2) = 'BC';
> SELECT * FROM foo WHERE bar::CHAR(3) = 'ABC';
Dean,
thank you. That would be EXCELLENT solution!
Can you confirm that in this case Postgres 8.1 can use in
>> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY);
>>
>> Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1
>Do this instead:
>CREATE TABLE foo ( bar CHAR(10) NOT NULL );
>CREATE UNIQUE INDEX foo_bar ON foo(bar char_pattern_ops);
Martijn,
Thank you. I have CHAR columns and
On 2005-11-09 13:08, Martijn van Oosterhout wrote:
I want to run fast queries by knowing first characters of bar like :
1. Select records from foo where first character of bar is A
2. Select records from foo where first character of bar is B
3. Select records from foo where first two characters
On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote:
> > you can create two indexes:
> >
> > CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops);
> > and
> > CREATE INDEX myindex_normal ON foo(col1);
> >
> > the first one will be used when using LIKE and the other for normal
> > comparisons .
>
> Ja
On Wed, Nov 09, 2005 at 10:46:27PM +0200, Andrus wrote:
> thank you. I try to formulate my problem more presicely.
> I have table
>
> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY);
>
> Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1
Do this instead:
CREATE TABLE foo (
>> CREATE INDEX i1 ON foo(col1);
>>
>> cannot be used to optimize queries of type "get all rows where first n
>> charaters of col1 are known" in Postgres.
>
> Of course it will! Any btree based index will let you do that. Re-read the
> previous answers and make sure you pay attention to the bit a
> you can create two indexes:
>
> CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops);
> and
> CREATE INDEX myindex_normal ON foo(col1);
>
> the first one will be used when using LIKE and the other for normal
> comparisons .
Jaime,
CREATE INDEX myindex_normal ON foo(col1);
Creates btree st
On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote:
> Martijn,
>
> >> I can use queries:
> >>
> >> WHERE col1 BETWEEN 'f' and 'f'+CHR(255)
>
> >Well, you could do that in postgresql too, you just need to use the SQL
> >standard concatination operator.
>
> >WHERE col1 BETWEEN 'f' and 'f' || chr(255);
>
>
Andrus wrote:
So
CREATE INDEX i1 ON foo(col1);
cannot be used to optimize queries of type "get all rows where first n
charaters of col1 are known" in Postgres.
Of course it will! Any btree based index will let you do that. Re-read
the previous answers and make sure you pay attention to the
Martijn,
>> I can use queries:
>>
>> WHERE col1 BETWEEN 'f' and 'f'+CHR(255)
>Well, you could do that in postgresql too, you just need to use the SQL
>standard concatination operator.
>WHERE col1 BETWEEN 'f' and 'f' || chr(255);
thank you.
I think it is best to use regular indexes since regula
On Wed, Nov 09, 2005 at 12:37:25PM +0200, Andrus wrote:
> I'm using Postgres 8.1 in Windows. Database encoding is UTF-8
> lc_ctype is Estonian_Estonia.1257.
> lc_collate is Estonian currently. However I can set lc_collate to C if this
> solves this issue.
>
> Doc says that
> " to allow PostgreSQ
> Well, for starters, see if PostgreSQL is currently using any indexes via
> EXPLAIN. First rule of performance tuning: don't.
I'm designing a new application. Data is not available yet.
I'm using Postgres 8.1 in Windows. Database encoding is UTF-8
lc_ctype is Estonian_Estonia.1257.
lc_collate is
Well, for starters, see if PostgreSQL is currently using any indexes via
EXPLAIN. First rule of performance tuning: don't.
If it's not (which is probably the case), then your best bet is to
create functional indexes; ie:
CREATE INDEX mytable__col1_4 ON mytable( substring( col1 for 4 ) );
You can
I have 10 CHARACTER columns in table mytable. Mytable is indexed by some of
those columns.
CREATE TABLE mytable ( col1 CHARACTER(10),
col2 CHARACTER(10),col3 CHARACTER(10),col4 CHARACTER(10),col5
CHARACTER(10),col6 CHARACTER(10),col7 CHARACTER(10),col8 CHARACTER(10),col9
CHARACTER(10), col10 CH
16 matches
Mail list logo
