In Postgres, how do you insert possible values for a particular column?-CodePudding

I have a table name ar for column operation in it I can allow only specific values ('C', 'R', 'RE', 'M', 'P'). I have added a check constraint for it.

Requirement: I need to insert 1 million records in the table but operation column has a constraint that only specific values are allowed. I am using generate_series() to generate values which generates random values and throws error. How can I avoid the error and insert 1 million record with only the required values ('C', 'R', 'RE', 'M', 'P') in column named operation.

CREATE TABLE ar (
  mappingId TEXT,
  actionRequestId integer,
  operation text,
  CONSTRAINT chk_operation CHECK (operation IN ('C', 'R', 'RE', 'M', 'P'))
);

INSERT INTO ar (mappingId, actionRequestId, operation)
SELECT substr(md5(random()::text), 1, 10),
       (random() * 70   10)::integer,
       substr(md5(random()::text), 1, 10)
FROM generate_series(1, 1000000);

ERROR: new row for relation "ar" violates check constraint "chk_operation"

CodePudding user response：

You can do a cross join with the allowed values:

INSERT INTO ar (mappingid, actionrequestid, operation)
SELECT substr(md5(random()::text), 1, 10),
       (random() * 70   10)::integer, 
       o.operation
FROM generate_series(1, 1000000 / 5)
   cross join ( 
     values ('C'), ('R'), ('RE'), ('M'), ('P')
   ) as o(operation);

CodePudding user response：

INSERT INTO ar (mappingId, actionRequestId, operation)
 SELECT substr(md5(random()::text), 1, 10),
   (random() * 70   10)::integer,
  'C'
FROM generate_series(1, 200000)
 UNION ALL
SELECT substr(md5(random()::text), 1, 10),
   (random() * 70   10)::integer,
  'R'
FROM generate_series(1, 200000)
 UNION ALL
SELECT substr(md5(random()::text), 1, 10),
   (random() * 70   10)::integer,
  'RE'
FROM generate_series(1, 200000)