bety
bety copied to clipboard
covariates table has 17 instances where stat is not null but no statname is given
It looks like these are cases where the level, n, and stat columns have been re-purposed to represent dates, with year = level, month = n, and day = stat. If we are going to do this, we should at least have the constraint
CHECK (stat IS NULL OR statname != '' OR
(variable_id IN (<some list of ids of variables corresponding to dates>)
AND level = ROUND(level) /* only integers for year */
AND level BETWEEN <some mininum year> AND EXTRACT(YEAR FROM NOW()) /* maybe ... */
AND n BETWEEN 1 AND 12
AND stat = ROUND(stat) /* only integers for day */
AND stat BETWEEN 1 AND 31))
and this usage should also be documented with a comment in the database. But this pretty ugly--there must be a better way.
Here's the query that makes this all clear:
SELECT
co. ID AS "cov. id",
format (
'%s %s, %s ... (cit. id = %s)',
author,
YEAR,
SUBSTRING (title, 1, 10),
C . ID
) AS citation,
trait_id,
LEVEL,
co.n,
co.stat,
NAME AS "variable name",
units,
MIN,
MAX
FROM
covariates co
LEFT JOIN traits T ON co.trait_id = T . ID
LEFT JOIN citations C ON C . ID = T .citation_id
LEFT JOIN variables v ON v. ID = co.variable_id
WHERE
co.stat IS NOT NULL
AND co.statname = ''
ORDER BY
citation,
trait_id;
Here is the result:
cov. id | citation | trait_id | level | n | stat | variable name | units | min | max
---------+------------------------------------------------+----------+-----------+----+---------+------------------+-------+-----+-----
8710 | Drake 2008, Fine-root ... (cit. id = 227) | 22099 | 2006.0000 | 5 | 1.0000 | start_date | | |
8711 | Drake 2008, Fine-root ... (cit. id = 227) | 22099 | 2006.0000 | 7 | 31.0000 | end-date | | |
8709 | Drake 2008, Fine-root ... (cit. id = 227) | 22100 | 2006.0000 | 7 | 31.0000 | end-date | | |
8708 | Drake 2008, Fine-root ... (cit. id = 227) | 22100 | 2006.0000 | 5 | 1.0000 | start_date | | |
8377 | George 2003, Fine-root ... (cit. id = 251) | 22241 | 2000.0000 | 6 | 1.0000 | start_date | | |
8378 | George 2003, Fine-root ... (cit. id = 251) | 22241 | 2000.0000 | | 31.0000 | end-date | | |
8381 | George 2003, Fine-root ... (cit. id = 251) | 22242 | 2000.0000 | 7 | 31.0000 | end-date | | |
8383 | George 2003, Fine-root ... (cit. id = 251) | 22242 | 2000.0000 | 6 | 1.0000 | start_date | | |
8385 | George 2003, Fine-root ... (cit. id = 251) | 22243 | 2000.0000 | 7 | 31.0000 | end-date | | |
8384 | George 2003, Fine-root ... (cit. id = 251) | 22243 | 2000.0000 | 6 | 1.0000 | start_date | | |
8386 | George 2003, Fine-root ... (cit. id = 251) | 22244 | 2000.0000 | 6 | 1.0000 | start_date | | |
8387 | George 2003, Fine-root ... (cit. id = 251) | 22244 | 2000.0000 | 7 | 31.0000 | end-date | | |
8325 | Herrick 1999, Effects of ... (cit. id = 237) | 22167 | 1997.0000 | 6 | 30.0000 | measurement_date | | |
8326 | Herrick 1999, Effects of ... (cit. id = 237) | 22167 | 1997.0000 | 8 | 30.0000 | measurement_date | | |
8721 | Pritchard 2008, Fine root ... (cit. id = 235) | 22155 | 1998.0000 | 10 | 22.0000 | start_date | | |
8722 | Pritchard 2008, Fine root ... (cit. id = 235) | 22155 | 2004.0000 | 10 | 22.0000 | end-date | | |
8720 | Pritchard 2008, Fine root ... (cit. id = 235) | 22156 | 1999.0000 | 9 | 30.0000 | end-date | | |
(17 rows)