5

I have a table that contains data for every day in 2002, but it has some missing dates. Namely, 354 records for 2002 (instead of 365). For my calculations, I need to have the missing data in the table with Null values

+-----+------------+------------+
| ID  |  rainfall  | date       |
+-----+------------+------------+
| 100 |  110.2     | 2002-05-06 |
| 101 |  56.6      | 2002-05-07 |
| 102 |  65.6      | 2002-05-09 |
| 103 |  75.9      | 2002-05-10 |
+-----+------------+------------+

You see that 2002-05-08 is missing. I want my final table to be like:

+-----+------------+------------+
| ID  |  rainfall  | date       |
+-----+------------+------------+
| 100 |  110.2     | 2002-05-06 |
| 101 |  56.6      | 2002-05-07 |
| 102 |            | 2002-05-08 |
| 103 |  65.6      | 2002-05-09 |
| 104 |  75.9      | 2002-05-10 |
+-----+------------+------------+

Is there a way to do that in PostgreSQL?

It doesn't matter if I have the result just as a query result (not necessarily an updated table).

Improve this question
4
  • Is the ID a serial? Do you need to move then all up when a date in inserted? Commented Oct 27, 2012 at 13:19
  • Yes, I need the ID to be changed like the example table I put here. At the end for an entire year, the last ID will be 365. Commented Oct 27, 2012 at 13:22
  • 2
    I did read your other posts (answered one) and I think you are doing it wrong. You should not rely on the ID for ordering or grouping. It is the "date" the important one. If you want to fill the gaps it is Ok and it can make for easier queries but not strictly necessary. Create an unique index on the "date" column to make sure they don't repeat. I'm answering on how to fill the gaps. Commented Oct 27, 2012 at 13:46
  • yes, you are right. I have to do it based on date not ID. But the problem is I couldn't do the grouping based on date. I'll try it. Thank you Commented Oct 27, 2012 at 15:01

4 Answers 4

Reset to default
9

date is a reserved word in standard SQL and the name of a data type in PostgreSQL. PostgreSQL allows it as identifier, but that doesn't make it a good idea. I use thedate as column name instead.

Don't rely on the absence of gaps in a surrogate ID. That's almost always a bad idea. Treat such an ID as unique number without meaning, even if it seems to carry certain other attributes most of the time.

In this particular case, as @Clodoaldo commented, thedate seems to be a perfect primary key and the column id is just cruft - which I removed:

CREATE TEMP TABLE tbl (thedate date PRIMARY KEY, rainfall numeric);
INSERT INTO tbl(thedate, rainfall) VALUES
  ('2002-05-06', 110.2)
, ('2002-05-07', 56.6)
, ('2002-05-09', 65.6)
, ('2002-05-10', 75.9);

Query

Full table by query:

SELECT x.thedate, t.rainfall  -- rainfall automatically NULL for missing rows
FROM (
   SELECT generate_series(min(thedate), max(thedate), '1d')::date AS thedate
   FROM   tbl
   ) x
LEFT   JOIN tbl t USING (thedate)
ORDER  BY x.thedate

Similar to what @a_horse_with_no_name posted, but simplified and ignoring the pruned id.

Fills in gaps between first and last date found in the table. If there can be leading / lagging gaps, extend accordingly. You can use date_trunc() like @Clodoaldo demonstrated - but his query suffers from syntax errors and can be simpler.

INSERT missing rows

The fastest and most readable way to do it is a NOT EXISTS anti-semi-join.

INSERT INTO tbl (thedate, rainfall)
SELECT x.thedate, NULL
FROM (
   SELECT generate_series(min(thedate), max(thedate), '1d')::date AS thedate
   FROM   tbl
   ) x
WHERE NOT EXISTS (SELECT 1 FROM tbl t WHERE t.thedate = x.thedate)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

8

Just do an outer join against a query that returns all dates in 2002:

with all_dates as (
  select date '2002-01-01' + i as date_col
  from generate_series(0, extract(doy from date '2002-12-31')::int - 1) as i
)
select row_number() over (order by ad.date_col) as id, 
       t.rainfall,
       ad.date_col as date
from all_dates ad
  left join your_table t on ad.date_col = t.date
order by ad.date_col;

This will not change your table, it will just produce the result as desired.

Note that the generated id column will not contain the same values as the ID column in your table as it is merely a counter in the result set.

You could also replace the row_number() function with extract(doy from ad.date_col)

Improve this answer

1 Comment

I ran this for my table and I encountered the following error: ERROR: invalid reference to FROM-clause entry for table "ad" LINE 13: left join rwanda1 t on ad.date_col = t.date HINT: There is an entry for table "ad", but it cannot be referenced from this part of the query.
4

To fill the gaps. This will not reorder the IDs:

insert into t (rainfall, "date") values
select null, "date"
from (
    select d::date as "date"
    from (
        t
        right join
        generate_series(
            (select date_trunc('year', min("date")) from t)::timestamp,
            (select max("date") from t),
            '1 day'
        ) s(d) on t."date" = s.d::date
    where t."date" is null
    ) q
) s
Improve this answer

4 Comments

What if December 31st is missing in the table? In that case, the generate_series() would not create enough values.
@a_horse_with_no_name Yes that is by design. I think he don't want or should not want future dates.
No, he wants 365 rows in the result.
@a_horse_with_no_name Read my comment on the question as to why I think that is wrong.
1

You have to fully re-create your table as indexes haves to change.

The better way to do it is to use your prefered dbi language, make a loop ignoring ID and putting values in a new table with new serialized IDs.

for day in (whole needed calendar)
    value = select rainfall from oldbrokentable where date = day
    insert into newcleanedtable date=day, rainfall=value, id=serialized

(That's not real code! Just conceptual to be adapted to your prefered scripting language)

Improve this answer

2 Comments

He could also renumber all the IDs to nonsense values (UPDATE table SET id=-id) then renumber them properly (UPDATE table SET id=q.val FROM (SELECT date, row_number() OVER (ORDER BY date) AS val FROM table) q WHERE q.date=table.date). That said, I agree with @Clodoaldo that this is wrong.
.. and extremely inefficient. If you'd want to write a new clean table, you'd use a single SQL command where you generate the new id with row_number() or from a sequence in older versions without window functions. But the whole idea is no good. It's hardly a good idea to depend on the absence of gaps in a surrogate key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.