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

How to check whether all data in PostgreSQL child table have been copied successfully

As a beginner in PostgreSQL, I am writing a shell script to verify whether all data have been copied to the child table. I will then perform other activities. If all data have not been copied, the remaining code execution should be halted. In my script, all data from the purchase_history_pre_partitioning table are copied to the newly created purchase_history table using the insert into command.

To verify whether all data have been copied, I have written the following DO statement to compare the number of rows in the two tables. However, there is a syntax error. Please advise me on how to troubleshoot this.

DO statement:

do $$
begin
  if (((select count(*) from purchase_history) !=
         (select count(*) from purchase_history_pre_partitioning)))
      then
          EXIT        
end
$$

Error:

ERROR: syntax error at or near "end" LINE 7: end
^

SQL state: 42601 Character: 143

Tags:

2

Answers


  1. 0

    Try this code. It will make sure that all of the data from the purchase_history_pre_partitioning table is correctly moved to the PostgreSQL purchase_history table:

    DO $$
BEGIN
  IF (SELECT COUNT(*) FROM purchase_history) != (SELECT COUNT(*) FROM purchase_history_pre_partitioning) THEN
    RAISE EXCEPTION 'Data copy verification failed!';
  END IF;
END $$;

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    You are meant to add a semicolon at the end of every SQL statement. Modify your script by adding a semicolon to EXIT, also end the if statement using END IF

    DO $$
BEGIN
  IF ((SELECT COUNT(*) FROM purchase_history) !=
      (SELECT COUNT(*) FROM purchase_history_pre_partitioning)) THEN
    EXIT;
  END IF;
END
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search