From: | SZŰCS Gábor <surrano(at)mailbox(dot)hu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | adddepend and partial indexes |
Date: | 2003-06-17 08:29:34 |
Message-ID: | 01f701c334ab$da498890$0403a8c0@fejleszt4 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear Fellows,
Couldn't find a list on adddepend, nor this problem via Google nor search at
postgresql.org, so here it goes.
Adddepend seems to have problem with partial indexes. Below is an example.
I can see the bugous regular subst in the perl script and I'd probably be
able to correct it, but I don't know how to use ALTER TABLE to add a partial
UNIQUE constraint (or shouldn't I add it at all?)
Also, I can't see the meaning of the second regular subst (as shown below).
I'd appreciate any source of info or explanation on this.
G.
------------------------------- 7.3.2
adddepend -------------------------------
------------------------------- Func:
findUniqueConstraints -------------------
# ...
# Fetch vars
my $constraint_name = $row->{'index_name'};
my $table = $row->{'table_name'};
my $columns = $row->{'constraint_definition'};
# Extract the columns from the index definition
$columns =~ s|.*\(([^\)]+)\).*|$1|g;
$columns =~ s|([^\s]+)[^\s]+_ops|$1|g;
my $upsql = qq{
DROP INDEX $constraint_name RESTRICT;
ALTER TABLE $table ADD CONSTRAINT $constraint_name UNIQUE ($columns);
};
# ...
----------------------------------------------------------------------------
--
----------------------- result of SELECT to fetch rows from,
formatted -------
-[ RECORD
1 ]---------+-------------------------------------------------------------in
dex_name | pakolas_cikktetel_helyre
table_name | pakolas_cikktetel
constraint_definition | CREATE UNIQUE INDEX pakolas_cikktetel_helyre ON
pakolas_cikktetel USING btree
(pakolas, cikk, minoseg, helyre)
WHERE ((helyre IS NOT NULL) AND (helyrol IS NULL))
-[ RECORD
2 ]---------+-------------------------------------------------------------in
dex_name | pakolas_cikktetel_helyrol
table_name | pakolas_cikktetel
constraint_definition | CREATE UNIQUE INDEX pakolas_cikktetel_helyrol ON
pakolas_cikktetel USING btree
(pakolas, cikk, minoseg, helyrol)
WHERE ((helyrol IS NOT NULL) AND (helyre IS NULL))
----------------------------------------------------------------------------
--
----------------------- error message in
psql.log ----------------------------
2003-06-16 20:32:18 [6946] ERROR: parser: parse error at or near "IS" at
character 134
2003-06-16 20:32:18 [6946] LOG: statement:
DROP INDEX pakolas_cikktetel_helyre RESTRICT;
ALTER TABLE pakolas_cikktetel ADD CONSTRAINT pakolas_cikktetel_helyre UNIQUE
(helyrol IS NULL);
2003-06-16 20:32:18 [6946] ERROR: parser: parse error at or near "IS" at
character 135
2003-06-16 20:32:18 [6946] LOG: statement:
DROP INDEX pakolas_cikktetel_helyrol RESTRICT;
ALTER TABLE pakolas_cikktetel ADD CONSTRAINT pakolas_cikktetel_helyrol
UNIQUE (helyre IS NULL);
From | Date | Subject | |
---|---|---|---|
Next Message | Mattias Kregert | 2003-06-17 08:35:19 | Request for advice: Table design |
Previous Message | Ivar | 2003-06-17 07:49:55 | How to insert unicode strings ? |