Thread: parse error in create index

Hi,

could someone, please, explain me the following parse error?

adressen=> \d geburtstage
Table    = geburtstage
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| lfd_nr                           | int4                            
|     4 |
| geburtstag                       | date                            
|     4 |
+----------------------------------+----------------------------------+-------+
adressen=> create index Monat_Tag on geburtstage (date_part('month',
Geburtstag));
ERROR:  parser: parse error at or near "'"
adressen=>

Thanks in advance,

-- 
Hubert Palme
palme@uni-wuppertal.de

Functional indexes cannot currently take constant values to the function,
so it's complaining about the constant 'month'.  The current workaround is
probably to create a function that does the date_part('month', <arg>) for
you and then use that function in the index creation.

On Sat, 3 Feb 2001, Hubert Palme wrote:

> Hi,
> 
> could someone, please, explain me the following parse error?
> 
> adressen=> \d geburtstage
> Table    = geburtstage
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                |
> Length|
> +----------------------------------+----------------------------------+-------+
> | lfd_nr                           | int4                            
> |     4 |
> | geburtstag                       | date                            
> |     4 |
> +----------------------------------+----------------------------------+-------+
> adressen=> create index Monat_Tag on geburtstage (date_part('month',
> Geburtstag));
> ERROR:  parser: parse error at or near "'"
> adressen=>
> 
> Thanks in advance,
> 
> -- 
> Hubert Palme
> palme@uni-wuppertal.de
> 

You can use two quote characters to get a single quote in the quoted
string, so ''month''

On Thu, 8 Feb 2001, Hubert Palme wrote:

> Stephan Szabo wrote:
> > 
> > Functional indexes cannot currently take constant values to the function,
> > so it's complaining about the constant 'month'.  The current workaround is
> > probably to create a function that does the date_part('month', <arg>) for
> > you and then use that function in the index creation.
> 
> Hmm... Perhaps, it's better I post to the novice group, because I'm new
> to SQL. 
> 
> Anyway -- That's my trial:
> 
> adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
> adressen-> 'SELECT date_part('month', $1)::integer;'
> adressen-> LANGUAGE 'sql';
> ERROR:  parser: parse error at or near "month"
> 
> The point are the nested strings, I guess. How can I render a "'" in an
> SQL string?
> 
> Thanks for your help!

Stephan Szabo wrote:
> 
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'.  The current workaround is
> probably to create a function that does the date_part('month', <arg>) for
> you and then use that function in the index creation.

Hmm... Perhaps, it's better I post to the novice group, because I'm new
to SQL. 

Anyway -- That's my trial:

adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
adressen-> 'SELECT date_part('month', $1)::integer;'
adressen-> LANGUAGE 'sql';
ERROR:  parser: parse error at or near "month"

The point are the nested strings, I guess. How can I render a "'" in an
SQL string?

Thanks for your help!

-- 
Hubert Palme
palme@uni-wuppertal.de

Stephan Szabo wrote:
> 
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'.  The current workaround is
> probably to create a function that does the date_part('month', <arg>) for
> you and then use that function in the index creation.

OK, I got it now -- good old pascal/FORTRAN fashion. But now I get

adressen=> CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag));
ERROR:  DefineIndex: (null) class not found
adressen=>

What is a class in this sense, and where can I read about it in the
documentation?
(geburtstag is a row of type DATE in the table geburtstage)

-- 
Hubert Palme
palme@uni-wuppertal.de

Stephan Szabo wrote:
> 
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'.  The current workaround is
> probably to create a function that does the date_part('month', <arg>) for
> you and then use that function in the index creation.

Hmm... Perhaps, it's better I post to the novice group, because I'm new
to SQL. 

Anyway -- That's my trial:

adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
adressen-> 'SELECT date_part('month', $1)::integer;'
adressen-> LANGUAGE 'sql';
ERROR:  parser: parse error at or near "month"

The point are the nested strings, I guess. How can I render a "'" in an
SQL string?

Thanks for your help!

-- 
Hubert Palme
palme@uni-wuppertal.de

Hubert Palme <palme@uni-wuppertal.de> writes:
> adressen=> CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag));
> ERROR:  DefineIndex: (null) class not found
> adressen=>

Apparently you're using 6.5 or older ... I'd recommend updating!

IIRC, in <= 6.5 you *must* specify an operator class for a functional
index.  So,

CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag) float8_ops);

(assuming that geb_monat returns a float8, else adjust to suit).

Don't have a 6.5 server running anymore to check this on, however.
        regards, tom lane