pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPOSIX-style regular expressions
8+ messages / 6 participants
[nested] [flat]
* POSIX-style regular expressions
@ 2002-09-10 09:32 Goran Buzic <goran.buzic@hep.hr>
2002-09-10 13:35 ` Re: POSIX-style regular expressions Josh Jore <josh@greentechnologist.org>
2002-09-10 15:04 ` Re: POSIX-style regular expressions Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-11 12:21 ` Re: POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
0 siblings, 3 replies; 8+ messages in thread
From: Goran Buzic @ 2002-09-10 09:32 UTC (permalink / raw)
To: pgsql-sql
Hi
Can anybody help me with POSIX-style regular expression used to check rules
that new data must satisfy for an insert or update operetion.
Table was created as follows.
CREATE TABLE table_name1 (
id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
id2 INT NOT NULL,
CONSTRAINT primary_key
PRIMARY KEY (id1, id2),
CONSTRAINT id2_exists
FOREIGN KEY (id2) REFERENCES table_name2
ON DELETE CASCADE
ON INSERT CASCADE
);
Any id1 that looks like
1.2.
1.12.
12.1.
12.12.
should be inserted into table, but the insert query was rejected with an
error message
ERROR: ExecAppend: rejected due to CHECK constraint table_name1_id1
I tested preceding regular expression with Perl and JavaScript and it worked
fine. Can I use regular expressions with CHECK parametar, and if so, how can
I make it work.
The platform PostgreSQL is installed is Solaris 8 - Intel.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
@ 2002-09-10 13:35 ` Josh Jore <josh@greentechnologist.org>
2002-09-10 13:51 ` Re: POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
2002-09-10 15:13 ` Re: POSIX-style regular expressions Ross J. Reedstrom <reedstrm@rice.edu>
2 siblings, 2 replies; 8+ messages in thread
From: Josh Jore @ 2002-09-10 13:35 UTC (permalink / raw)
To: Goran Buzic <goran.buzic@hep.hr>; +Cc: pgsql-sql
On Tue, 10 Sep 2002, Goran Buzic wrote:
> id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> ERROR: ExecAppend: rejected due to CHECK constraint table_name1_id1
>
> I tested preceding regular expression with Perl and JavaScript and it worked
> fine. Can I use regular expressions with CHECK parametar, and if so, how can
> I make it work.
You should probably test it against PostgreSQL's regex engine. What you
may not know is that they all have different syntaxes, rules and quirks.
What works in one may or may not work in another.
So check out src/backend/regex and build retest (I think that's what it
was called). It's a command line regex tester (obviously against
PostgreSQL's implementation).
Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
2002-09-10 13:35 ` Re: POSIX-style regular expressions Josh Jore <josh@greentechnologist.org>
@ 2002-09-10 13:51 ` Goran Buzic <goran.buzic@hep.hr>
2002-09-16 15:55 ` Re: POSIX-style regular expressions Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Goran Buzic @ 2002-09-10 13:51 UTC (permalink / raw)
To: pgsql-sql
I tried to build retest but there was a lot of massages like
...... undefined referenc to ..
and building regex tester failed with messages
collect2: ld returned 1 exit status
make: *** [retest] Error 1
"Josh Jore" <josh@greentechnologist.org> wrote in message
news:Pine.BSO.4.44.0209100754190.3512-100000@kitten.greentechnologist.org...
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>
> > id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
>
> > ERROR: ExecAppend: rejected due to CHECK constraint table_name1_id1
> >
> > I tested preceding regular expression with Perl and JavaScript and it
worked
> > fine. Can I use regular expressions with CHECK parametar, and if so, how
can
> > I make it work.
>
> You should probably test it against PostgreSQL's regex engine. What you
> may not know is that they all have different syntaxes, rules and quirks.
> What works in one may or may not work in another.
>
> So check out src/backend/regex and build retest (I think that's what it
> was called). It's a command line regex tester (obviously against
> PostgreSQL's implementation).
>
> Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
2002-09-10 13:35 ` Re: POSIX-style regular expressions Josh Jore <josh@greentechnologist.org>
2002-09-10 13:51 ` Re: POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
@ 2002-09-16 15:55 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2002-09-16 15:55 UTC (permalink / raw)
To: Goran Buzic <goran.buzic@hep.hr>; +Cc: pgsql-sql
I have commented out the Makefile entry for retest because I can't get
it to compile anymore. Someone can come along and clean it up someday.
# retest will not compile because multibyte is now enabled by default
# and the multibyte calls require /mmgr, /adt, and other calls that
# are complex for linkage, bjm 2002-09-16
#retest: retest.o SUBSYS.o $(DEBUGOBJ)
# $(CC) $(CFLAGS) $(LDFLAGS) $^ $(LIBS) -o $@
---------------------------------------------------------------------------
Goran Buzic wrote:
> I tried to build retest but there was a lot of massages like
>
> ...... undefined referenc to ..
>
> and building regex tester failed with messages
>
> collect2: ld returned 1 exit status
> make: *** [retest] Error 1
>
>
> "Josh Jore" <josh@greentechnologist.org> wrote in message
> news:Pine.BSO.4.44.0209100754190.3512-100000@kitten.greentechnologist.org...
> > On Tue, 10 Sep 2002, Goran Buzic wrote:
> >
> > > id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> >
> > > ERROR: ExecAppend: rejected due to CHECK constraint table_name1_id1
> > >
> > > I tested preceding regular expression with Perl and JavaScript and it
> worked
> > > fine. Can I use regular expressions with CHECK parametar, and if so, how
> can
> > > I make it work.
> >
> > You should probably test it against PostgreSQL's regex engine. What you
> > may not know is that they all have different syntaxes, rules and quirks.
> > What works in one may or may not work in another.
> >
> > So check out src/backend/regex and build retest (I think that's what it
> > was called). It's a command line regex tester (obviously against
> > PostgreSQL's implementation).
> >
> > Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
2002-09-10 13:35 ` Re: POSIX-style regular expressions Josh Jore <josh@greentechnologist.org>
@ 2002-09-10 15:13 ` Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 0 replies; 8+ messages in thread
From: Ross J. Reedstrom @ 2002-09-10 15:13 UTC (permalink / raw)
To: Goran Buzic <goran.buzic@hep.hr>; pgsql-sql
On Tue, Sep 10, 2002 at 08:35:27AM -0500, Josh Jore wrote:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>
> > id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
>
> > ERROR: ExecAppend: rejected due to CHECK constraint table_name1_id1
> >
> > I tested preceding regular expression with Perl and JavaScript and it worked
> > fine. Can I use regular expressions with CHECK parametar, and if so, how can
> > I make it work.
>
> You should probably test it against PostgreSQL's regex engine. What you
> may not know is that they all have different syntaxes, rules and quirks.
> What works in one may or may not work in another.
>
> So check out src/backend/regex and build retest (I think that's what it
> was called). It's a command line regex tester (obviously against
> PostgreSQL's implementation).
Or, test directly in psql. I dropped your test data into a table, and
played with select:
test=# select * from testtable ;
id
--------
1.2.
1.12.
12.1.
12.12.
(4 rows)
test=# select * from testtable ;
id
--------
1.2.
1.12.
12.1.
12.12.
(4 rows)
test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2}$';
id
--------
12.12.
(1 row)
Hmm, that's because you said char(6), which is bank padded:
test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2} *';
id
--------
1.2.
1.12.
12.1.
12.12.
(4 rows)
Further testing with your actual table def (what version are you using?
I dont have ON INSERT CASCADE in my 7.2.1 test database) indicates you
need to double up the slashes on the '.', as so:
'^([0-9]{1,2}\\.){2}$'
One set of slashes gets stripped by the command processor.
Note that this _still_ requires a 6 char input, so 1.2. fails, but
01.02. works.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
@ 2002-09-10 15:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-10 15:15 ` Re: POSIX-style regular expressions Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 8+ messages in thread
From: Stephan Szabo @ 2002-09-10 15:04 UTC (permalink / raw)
To: Goran Buzic <goran.buzic@hep.hr>; +Cc: pgsql-sql
On Tue, 10 Sep 2002, Goran Buzic wrote:
> Hi
>
> Can anybody help me with POSIX-style regular expression used to check rules
> that new data must satisfy for an insert or update operetion.
>
> Table was created as follows.
>
> CREATE TABLE table_name1 (
> id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
Bad column type choice. char(6) is space padded, so 1.2. looks like
"1.2. ". For me, 12.12. works, but any shorter one fails. Unless
you need space padding elsewhere, you may just want to use varchar(6);
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
2002-09-10 15:04 ` Re: POSIX-style regular expressions Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-09-10 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2002-09-10 15:15 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Goran Buzic <goran.buzic@hep.hr>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>> id1 char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> Bad column type choice. char(6) is space padded, so 1.2. looks like
> "1.2. ". For me, 12.12. works, but any shorter one fails. Unless
> you need space padding elsewhere, you may just want to use varchar(6);
Also, the backslash has to be doubled to get through the string literal
parser, so what you really want is
id1 varchar(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\\.){2}$'),
else it will accept values you don't want...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: POSIX-style regular expressions
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
@ 2002-09-11 12:21 ` Goran Buzic <goran.buzic@hep.hr>
2 siblings, 0 replies; 8+ messages in thread
From: Goran Buzic @ 2002-09-11 12:21 UTC (permalink / raw)
To: pgsql-sql
Hi
I would like to thank you for helping me with regular expressions. Yours
suggestions to use VARCHAR and '\\.' pattern helped me a lot.
Best regards
Goran
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-09-16 15:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-10 09:32 POSIX-style regular expressions Goran Buzic <goran.buzic@hep.hr>
2002-09-10 13:35 ` Josh Jore <josh@greentechnologist.org>
2002-09-10 13:51 ` Goran Buzic <goran.buzic@hep.hr>
2002-09-16 15:55 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-10 15:13 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-10 15:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-10 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-11 12:21 ` Goran Buzic <goran.buzic@hep.hr>
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