covariates table has 17 instances where stat is not null but no statname is given

[gsrohde]

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)