CODEWITHSUNDEEP
sqlpostgresql
Leasye
Leasye

Reputation: 279

postgres, how to fill blanks when a value does not exist?

I have this table : 

 id_data          
-----------
    1     | 
    2     | 
    4     |
    10    |  

create table c (id_data integer unique); 
insert into c values (1),(2),(4),(10)

And i would like to automatically fill blanks like that :

 id_data          
-----------
    1     | 
    2     |
    3     | 
    4     |
    5     |
    6     |
    7     |
    8     | 
    9     | 
    10    |

I tried this:

INSERT INTO c (id_data)
SELECT x.id_data
FROM generate_series(1,100000000) AS x(id_data) 
WHERE NOT EXISTS lead(id_data)>id_data+1  ;

But lead(id_data)<id_data+1 is not allowed in where clause :(

Upvotes: 0

Views: 911

Answers (2)

wildplasser
wildplasser

Reputation: 44230

WITH mima AS (
   SELECT MIN(id_data) AS mi
        , MAX(id_data) AS ma
        FROM c
        )
SELECT gs.val
FROM mima mm
, generate_series(mm.mi, mm.ma) gs(val) 
WHERE NOT EXISTS (SELECT * FROM c WHERE c.id_data = gs.val)
        ;

The same, but using a nested subquery instead of a CTE:

SELECT gs.val
FROM (
   SELECT MIN(id_data) AS mi
        , MAX(id_data) AS ma
        FROM c
        ) mm
 , generate_series(mm.mi, mm.ma ) gs(val)
WHERE NOT EXISTS (SELECT * FROM c WHERE c.id_data = gs.val)
        ;

And this one uses a recursive CTE to emulate the generate_series():

WITH RECURSIVE mmm AS (
        SELECT id_data AS id_data
        FROM c mi
        WHERE NOT EXISTS (SELECT id_data FROM c nx WHERE nx.id_data < mi.id_data)
        UNION ALL
        SELECT 1+m.id_data AS id_data
        FROM mmm m
        WHERE EXISTS (SELECT id_data FROM c WHERE c.id_data >= m.id_data)
        )
SELECT id_data
FROM mmm m
WHERE NOT EXISTS (SELECT * FROM c WHERE c.id_data = m.id_data)
        ;

Upvotes: 0

user330315
user330315

Reputation:

This should do it:

insert into c (id_data)
select data.id
from generate_series(1, 100000000) as data(id)
  left join c on c.id_data = data.id
where c.id_data is null;

You can also fill the table up to the current maxium of id_data:

insert into c (id_data)
select data.id
from generate_series(1, (select max(id_data) from c)) as data(id)
  left join c on c.id_data = i
where c.id_data is null;

Upvotes: 1

Related Questions