Revision 3217
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 3148 | 3148 |
|
| 3149 | 3149 |
|
| 3150 | 3150 |
-- |
| 3151 |
-- Name: locationevent_unique_datasource_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3152 |
-- |
|
| 3153 |
|
|
| 3154 |
ALTER TABLE locationevent |
|
| 3155 |
ADD CONSTRAINT locationevent_unique_datasource_accessioncode UNIQUE (datasource_id, sourceaccessioncode); |
|
| 3156 |
|
|
| 3157 |
|
|
| 3158 |
-- |
|
| 3159 | 3151 |
-- Name: locationevent_unique_parent_authorcode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3160 | 3152 |
-- |
| 3161 | 3153 |
|
| ... | ... | |
| 4232 | 4224 |
|
| 4233 | 4225 |
|
| 4234 | 4226 |
-- |
| 4235 |
-- Name: locationevent_unique_location; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4227 |
-- Name: locationevent_unique_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4236 | 4228 |
-- |
| 4237 | 4229 |
|
| 4238 | 4230 |
|
| 4239 | 4231 |
|
| 4240 | 4232 |
|
| 4241 | 4233 |
-- |
| 4242 |
-- Name: locationevent_unique_location_date; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4243 |
-- |
|
| 4244 |
|
|
| 4245 |
|
|
| 4246 |
|
|
| 4247 |
|
|
| 4248 |
-- |
|
| 4249 | 4234 |
-- Name: locationevent_unique_project_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4250 | 4235 |
-- |
| 4251 | 4236 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 3682 | 3682 |
|
| 3683 | 3683 |
|
| 3684 | 3684 |
-- |
| 3685 |
-- Name: locationevent_unique_datasource_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3686 |
-- |
|
| 3687 |
|
|
| 3688 |
ALTER TABLE ONLY locationevent |
|
| 3689 |
ADD CONSTRAINT locationevent_unique_datasource_accessioncode UNIQUE (datasource_id, sourceaccessioncode); |
|
| 3690 |
|
|
| 3691 |
|
|
| 3692 |
-- |
|
| 3693 | 3685 |
-- Name: locationevent_unique_parent_authorcode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3694 | 3686 |
-- |
| 3695 | 3687 |
|
| ... | ... | |
| 4763 | 4755 |
|
| 4764 | 4756 |
|
| 4765 | 4757 |
-- |
| 4766 |
-- Name: locationevent_unique_location; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4758 |
-- Name: locationevent_unique_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4767 | 4759 |
-- |
| 4768 | 4760 |
|
| 4769 |
CREATE UNIQUE INDEX locationevent_unique_location ON locationevent USING btree (location_id) WHERE (parent_id IS NULL);
|
|
| 4761 |
CREATE UNIQUE INDEX locationevent_unique_accessioncode ON locationevent USING btree (location_id, (COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::timestamp with time zone))) WHERE (parent_id IS NULL);
|
|
| 4770 | 4762 |
|
| 4771 | 4763 |
|
| 4772 | 4764 |
-- |
| 4773 |
-- Name: locationevent_unique_location_date; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4774 |
-- |
|
| 4775 |
|
|
| 4776 |
CREATE UNIQUE INDEX locationevent_unique_location_date ON locationevent USING btree (location_id, obsstartdate) WHERE (parent_id IS NULL); |
|
| 4777 |
|
|
| 4778 |
|
|
| 4779 |
-- |
|
| 4780 | 4765 |
-- Name: locationevent_unique_project_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4781 | 4766 |
-- |
| 4782 | 4767 |
|
Also available in: Unified diff
schemas/vegbien.sql: locationevent: Fixed bug where locationevent_unique_location index was overconstraining locationevent when a sourceaccessioncode or obsstartdate was specified, by combining the locationevent_unique_location, locationevent_unique_accessioncode, and locationevent_unique_location_date indexes into one COALESCE index on the combined fields of those indexes