Обсуждение: 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