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

In Postgres, how do you insert possible values for a particular column? – Postgresql

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"
Tags:

2

Answers


  1. 0 
    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)
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search