PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – create new record in db with sql

I have a bunch of device_id (roughly 300million), I want to create a full video resolution list [‘360p’, ‘480p’, ‘540p’, ‘720p’, ‘1080p’]for each device_id like

device_id
   1
   2

device_id    video_resolution
  1              360p
  1              480p
  1              540p 
  1              720p
  1              1080p
  2              360p
  2              480p
  2              540p 
  2              720p
  2              1080p

I wonder if it's possible to create these record in sql?  
I've tried to use spark, but I do not have that many resources and often run into OOM issue.
Tags:

2

Answers


  1. 0

    Try this: the cross join would give you all the combinations. I am not familiar with the internals of PostgreSQL but generally the CREATE TABLE statements are ‘logged’ less, thefore it may not run out of memory (you may run out of target disk space, of course).

    create table MyTbl (DeviceId integer);
insert into MyTbl values (1),(2),(3);
create table MyBiggerTable as 
  select mt.*, vr.res
  from myTbl mt cross join (values
    ('360p'),
    ('480p'),
    ('540p'),
    ('720p'),
    ('1080p')) as vr(res);

    You don’t need the first two statement, of course, and you need to adjust the table names.

    Login or Signup to reply.
  2. 0 
    select device_id,string_agg(video_resolution, ',')  from table_name group by 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search