pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
parse error in create index
7+ messages / 3 participants
[nested] [flat]

* parse error in create index
@ 2001-02-03 20:46  Hubert Palme <palme@uni-wuppertal.de>
  0 siblings, 1 reply; 7+ messages in thread

From: Hubert Palme @ 2001-02-03 20:46 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: parse error in create index
@ 2001-02-06 16:54  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Hubert Palme <palme@uni-wuppertal.de>
  0 siblings, 3 replies; 7+ messages in thread

From: Stephan Szabo @ 2001-02-06 16:54 UTC (permalink / raw)
  To: Hubert Palme <palme@uni-wuppertal.de>; +Cc: pgsql-sql


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
> 




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: parse error in create index
@ 2001-02-08 22:29  Hubert Palme <palme@uni-wuppertal.de>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Hubert Palme @ 2001-02-08 22:29 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: parse error in create index
@ 2001-02-08 22:39  Hubert Palme <palme@uni-wuppertal.de>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2 siblings, 1 reply; 7+ messages in thread

From: palme@uni-wuppertal.de @ 2001-02-08 22:39 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Hubert Palme <palme@uni-wuppertal.de>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: parse error in create index
@ 2001-02-08 22:56  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Hubert Palme <palme@uni-wuppertal.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Stephan Szabo @ 2001-02-08 22:56 UTC (permalink / raw)
  To: Hubert Palme <palme@uni-wuppertal.de>; +Cc: pgsql-sql


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!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: parse error in create index
@ 2001-02-08 23:21  Hubert Palme <palme@uni-wuppertal.de>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2 siblings, 1 reply; 7+ messages in thread

From: Hubert Palme @ 2001-02-08 23:21 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Hubert Palme <hubert@news.tht.net>

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: parse error in create index
@ 2001-02-12 15:54  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Hubert Palme <palme@uni-wuppertal.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2001-02-12 15:54 UTC (permalink / raw)
  To: Hubert Palme <palme@uni-wuppertal.de>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql@postgresql.org, Hubert Palme <hubert@news.tht.net>

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-02-12 15:54 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-03 20:46 parse error in create index Hubert Palme <palme@uni-wuppertal.de>
2001-02-06 16:54 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-02-08 22:29   ` Hubert Palme <palme@uni-wuppertal.de>
2001-02-08 22:39   ` Hubert Palme <palme@uni-wuppertal.de>
2001-02-08 22:56     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-02-08 23:21   ` Hubert Palme <palme@uni-wuppertal.de>
2001-02-12 15:54     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox