Re: Creating index for convert text to integer

On Wed, Aug 26, 2009 at 07:13:41AM -0700, xaviergxf wrote:
> How can i create a index to index all the fields that has the type
> INTEGER, in the following table:
>
> create type properties_types as enum('INTEGER', 'STRING', 'FLOAT');
>
> create table properties_types(
> value text NOT NULL,
> value_type properties_types NOT NULL
> );

You can't create an enum that has the same name as a table can you?

> how do i create index for the integer types?
>
> create index properties_types_index on properties_types ((value ::integer)) where value_type='INTEGER'

Yup, that should work.

> Can i use this select with the index?
> select valor from properties_types where value::integer<3

You need the where clause in there:

SELECT value
FROM properties_types
WHERE value_type = 'INTEGER'
AND value::integer < 3;

This is generally considered pretty bad form though; there are lots
of discussions about "EAV" style designs that this seems similar to.
Slightly better would be creating your original table as:

CREATE TABLE properties_types (
value_type properties_type,
value_int INTEGER
CHECK ((value_type = 'INTEGER') = (value_int IS NOT NULL)),
value_text TEXT
CHECK ((value_type = 'STRING') = (value_text IS NOT NULL)),
value_float FLOAT8
CHECK ((value_type = 'FLOAT') = (value_float IS NOT NULL))
);

You can then just build a normal index on the appropriate columns and
run your queries the naive way. Something like:

SELECT *
FROM properties_types
WHERE value_int < 3;

Arranging things this way shouldn't take much (if any) more space and it
should run faster as it doesn't need to go converting between datatypes
the whole time.

This is still pretty bad form though and you'll get much more
leverage/help from PG if you arrange the tables so they reflect the
structure of the data you're really putting in.

--
Sam http://samason.me.uk/