How to truncate data in a Postgres database
In this post am going to share with you how to truncate data in PostgreSQL and some of the keywords used when creating a function.
Let’s get started:
First you need to create a simple function, the function will receive the username variable with a type of character.
In the function created above, we are going to declare a cursor that is bound to a select query.
The query will select the tablename from pg_tables with the condition of checking our database schema name.
What is a cursor?
According to Postgresqltutorial, a PL/PGSQL cursor allows you to encapsulate a query and process each individual row at a time. You use cursors when you want to divide a large result set into parts and process each part individually.
After creating our cursor, we are going to create a for-loop to loop through the statements cursor we have created.
What is Truncate?
According to Postgres.org, TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.
What is quote_ident?
The PostgreSQL quote_ident function is used to make a given string with suitably double quoted, so as it can be used like an identifier in an SQL statement string if required.
According to kb.objectrocket.com, In PostgreSQL, a cascade means that a delete or update of records in a parent table will automatically delete or update matching records in a child table where a foreign key relationship is in place.
Below is the snippet of PGSQL function, for deleting all the data in the database tables.
After the function has been created, you need to run the query below for you to successfully delete all the database table data.
That’s how you truncate data from all your database tables in Postgres. Happy codding.
Reference
