0

I want to truncate a table in PostgreSQL 13 and then insert rows to the same table within a single transaction, so that when I execute the SQL command it will TRUNCATE the table and if during INSERTS anything fails truncated data should be rollback.

Below is the command which I want in a single transaction.

TRUNCATE TABLE public.truninsdemo;

INSERT INTO public.truninsdemo (id, name)
VALUES (1, 'Scott'), (2, 'John');

UPDATE

I don't want to execute TRUNCATE and INSERT statements as 2 separate commands, separated by a semicolon. I want to achieve results using a single command may be CTE, but not sure how.

Vikas J
  • 795
  • 3
  • 14
  • 31
  • To truncate table and insert rows in same table within a single transaction, you simply have ... to truncate table and insert rows in same table within a single transaction. – Atmo Jan 14 '23 at 15:41
  • Hello @Atmo Can you please provide a working example for the same. So I can understand it better. Thanks! – Vikas J Jan 14 '23 at 15:45
  • BEGIN; TRUNCATE .... ; INSERT INTO ... ; COMMIT; That's it. The only thing missing in your example is BEGIN; to start the transaction and COMMIT; to end the transaction. – Frank Heikens Jan 14 '23 at 15:47
  • So I think my question was not clear, I don't want to execute TRUNCATE and INSERT statements as 2 separate commands, separated by a semicolon. I want to achieve results using a single command may be CTE, but not sure how. – Vikas J Jan 14 '23 at 16:32
  • 1) Why? 2) No per [Select](https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH): *Each subquery can be a SELECT, TABLE, VALUES, INSERT, UPDATE or DELETE statement*. 3) Do as already suggested and use `Begin Commit`. – Adrian Klaver Jan 14 '23 at 16:51
  • I have to execute this 2 commands in single query because tool (Snaplogic) which I use to execute Sql command it can execute only 1 command at a time. So internally Truncate will be 1 batch/transaction and later Insert in 2nd batch/transaction. So if anything goes wrong during Insert I won't be able to rollback truncate command and old data will be gone. – Vikas J Jan 14 '23 at 16:56

0 Answers0