Revision 14362
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 589 | 589 |
SELECT util.recreate_view(''"TNRS".taxon_best_match'');
|
| 590 | 590 |
$ make schemas/remake |
| 591 | 591 |
|
| 592 |
to populate a new column: |
|
| 593 |
ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER taxon_match__fill; --speeds up update |
|
| 594 |
UPDATE "TNRS".taxon_match SET "col" = value; |
|
| 592 |
to add a new derived column: |
|
| 593 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''col''), $$
|
|
| 594 |
expr |
|
| 595 |
$$)); |
|
| 596 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match'');
|
|
| 595 | 597 |
-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
|
| 596 |
CLUSTER "TNRS".taxon_match; -- ensure rows are exported in sorted order |
|
| 597 |
ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER taxon_match__fill; |
|
| 598 |
VACUUM ANALYZE "TNRS".taxon_match --remove previous rows; runtime: 1.5 min ("92633 ms")
|
|
| 599 | 598 |
|
| 600 | 599 |
to add a constraint: runtime: 3 min ("173620 ms")
|
| 601 | 600 |
'; |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 20250 | 20250 |
SELECT util.recreate_view(''"TNRS".taxon_best_match'');
|
| 20251 | 20251 |
$ make schemas/remake |
| 20252 | 20252 |
|
| 20253 |
to populate a new column: |
|
| 20254 |
ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER taxon_match__fill; --speeds up update |
|
| 20255 |
UPDATE "TNRS".taxon_match SET "col" = value; |
|
| 20253 |
to add a new derived column: |
|
| 20254 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''col''), $$
|
|
| 20255 |
expr |
|
| 20256 |
$$)); |
|
| 20257 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match'');
|
|
| 20256 | 20258 |
-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
|
| 20257 |
CLUSTER "TNRS".taxon_match; -- ensure rows are exported in sorted order |
|
| 20258 |
ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER taxon_match__fill; |
|
| 20259 |
VACUUM ANALYZE "TNRS".taxon_match --remove previous rows; runtime: 1.5 min ("92633 ms")
|
|
| 20260 | 20259 |
|
| 20261 | 20260 |
to add a constraint: runtime: 3 min ("173620 ms")
|
| 20262 | 20261 |
'; |
Also available in: Unified diff
inputs/.TNRS/schema.sql: to populate a new column: updated to use util.derived_col_update()