pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnderscores in column names
4+ messages / 3 participants
[nested] [flat]
* Underscores in column names
@ 2003-03-07 15:58 Mark Mitchell <mark@lapcrew.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mark Mitchell @ 2003-03-07 15:58 UTC (permalink / raw)
To: pgsql-sql
I have a underscores in most all of the column names in this database.
I've ran into a problem where Postgres doesn't like them.
SELECT * FROM "NATAB" WHERE
"NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'
Produces the error : "ESCAPE string must be empty or one character"
The column "NA_COLON" is a column that holds the numeric position at
which the first name ends and the last name begins. If the column name
does not contain an underscore it works fine. Any suggestions?
- Mark
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Underscores in column names
@ 2003-03-07 16:28 Richard Huxton <dev@archonet.com>
parent: Mark Mitchell <mark@lapcrew.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2003-03-07 16:28 UTC (permalink / raw)
To: Mark Mitchell <mark@lapcrew.com>; pgsql-sql
On Friday 07 Mar 2003 3:58 pm, Mark Mitchell wrote:
> I have a underscores in most all of the column names in this database.
> I've ran into a problem where Postgres doesn't like them.
>
> SELECT * FROM "NATAB" WHERE
> "NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
> SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'
>
> Produces the error : "ESCAPE string must be empty or one character"
>
> The column "NA_COLON" is a column that holds the numeric position at
> which the first name ends and the last name begins. If the column name
> does not contain an underscore it works fine. Any suggestions?
Are you sure you don't mean substr() rather than substring()? I think the form
you're using does a POSIX regexp match and uses the third param as an escape
character.
--
Richard Huxton
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Underscores in column names
@ 2003-03-07 17:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Mitchell <mark@lapcrew.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-03-07 17:08 UTC (permalink / raw)
To: Mark Mitchell <mark@lapcrew.com>; +Cc: pgsql-sql
Mark Mitchell <mark@lapcrew.com> writes:
> SELECT * FROM "NATAB" WHERE
> "NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
> SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'
> Produces the error : "ESCAPE string must be empty or one character"
This is a datatype issue, not a naming issue. If the second and third
arguments to substring() aren't integers (and no, I don't mean numeric,
nor float, I mean int4), the parser is likely to think you are asking
for the three-text-parameter variant of substring(). Which is
SQL99-style regexps with an alternate escape string.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Underscores in column names
@ 2003-03-07 17:17 Mark Mitchell <mark@lapcrew.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Mark Mitchell @ 2003-03-07 17:17 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
You are 100% correct Rich. I changed the query to use substr() instead
of substring() and it works fine. Thanks for your quick answer.
- Mark
On Fri, 2003-03-07 at 11:28, Richard Huxton wrote:
> On Friday 07 Mar 2003 3:58 pm, Mark Mitchell wrote:
> > I have a underscores in most all of the column names in this database.
> > I've ran into a problem where Postgres doesn't like them.
> >
> > SELECT * FROM "NATAB" WHERE
> > "NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
> > SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'
> >
> > Produces the error : "ESCAPE string must be empty or one character"
> >
> > The column "NA_COLON" is a column that holds the numeric position at
> > which the first name ends and the last name begins. If the column name
> > does not contain an underscore it works fine. Any suggestions?
>
> Are you sure you don't mean substr() rather than substring()? I think the form
> you're using does a POSIX regexp match and uses the third param as an escape
> character.
>
> --
> Richard Huxton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-07 17:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 15:58 Underscores in column names Mark Mitchell <mark@lapcrew.com>
2003-03-07 16:28 ` Richard Huxton <dev@archonet.com>
2003-03-07 17:17 ` Mark Mitchell <mark@lapcrew.com>
2003-03-07 17:08 ` 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