Test Your PostgreSQL TRUNCATE Command Knowledge with This Quiz

Progress

True / False

The TRUNCATE command in PostgreSQL can be used to remove all rows from a table without logging individual row deletions.

True False

The TRUNCATE command removes all rows from a table quickly by not logging individual row deletions, making it faster than DELETE for large tables.

True / False

The TRUNCATE command in PostgreSQL can be rolled back if it is part of a transaction.

True False

The TRUNCATE command is transaction-safe in PostgreSQL and can be rolled back if it is part of an open transaction.

True / False

The TRUNCATE command can only be used on a single table at a time in PostgreSQL.

True False

PostgreSQL allows TRUNCATE to be used on multiple tables at once by specifying a comma-separated list of table names.

True / False

The TRUNCATE command will not fire any ON DELETE triggers defined on the table.

True False

TRUNCATE bypasses ON DELETE triggers because it does not delete rows individually.

True / False

In PostgreSQL, using TRUNCATE on a table with foreign key references from other tables will succeed without any additional steps.

True False

You must use the CASCADE option with TRUNCATE if the table being truncated has foreign key references to ensure that the referencing tables are also truncated or the constraints are temporarily disabled.

True / False

The TRUNCATE command resets any sequences associated with the table's columns back to their starting values.

True False

By default, TRUNCATE does not reset sequences. To reset sequences, you must use the RESTART IDENTITY option.

True / False

TRUNCATE in PostgreSQL can be restricted by using a WHERE clause to limit the rows that are removed.

True False

The TRUNCATE command does not support a WHERE clause. It removes all rows from the specified tables.

True / False

TRUNCATE is a part of Data Definition Language (DDL) in PostgreSQL.

True False

TRUNCATE is considered a DDL command because it changes the structure of the table by removing all rows efficiently.

True / False

The TRUNCATE command requires more locks compared to the DELETE command in PostgreSQL.

True False

TRUNCATE requires fewer locks and takes a shorter time compared to DELETE, especially on large tables, because it does not log individual row deletions.

Q10

True / False

: In PostgreSQL, using TRUNCATE on a partitioned table will not affect its partitions.

True False

When TRUNCATE is used on a partitioned table, it will affect all its partitions by removing rows from each partition.

Q11

Single Choice

What does the TRUNCATE command do in PostgreSQL?

Deletes a single row from a table Removes all rows from a table quickly without logging individual row deletions Removes a table from the databaseAdds a new column to a table

The TRUNCATE command in PostgreSQL is used to remove all rows from a table efficiently, without logging each row deletion.

Q12

Single Choice

Which of the following is true about the TRUNCATE command in PostgreSQL?

It cannot be rolled back It can be rolled back if used inside a transaction It is slower than the DELETE commandIt always requires a WHERE clause

In PostgreSQL, the TRUNCATE command can be rolled back if it is used within a transaction.

Q13

Single Choice

What is the main difference between DELETE and TRUNCATE in PostgreSQL?

DELETE removes all rows without logging, while TRUNCATE logs each row deletion DELETE removes all rows without logging, while TRUNCATE logs each row deletion DELETE and TRUNCATE perform the same function with no differencesTRUNCATE can be used only on empty tables

TRUNCATE is faster than DELETE because it does not log each individual row deletion.

Q14

Single Choice

Consider the following SQL statement in PostgreSQL:

SQL Code

TRUNCATE TABLE employees;
What will happen to the rows in the employees table?

All rows will be deleted and cannot be recovered All rows will be deleted, but they can be recovered with the ROLLBACK command Only the rows with a certain condition will be deletedThe table will be dropped from the database

The TRUNCATE command will remove all rows from the employees table, and the operation cannot be undone unless it is within a transaction.

Q15

Single Choice

Which of the following commands would you use to remove all rows from multiple tables in PostgreSQL?

SQL Code

TRUNCATE TABLE employees, departments;

The command is incorrect; it should be one table at a time This command is correct and will truncate both tables The command needs a WHERE clause to specify which rows to removeThe command needs a WHERE clause to specify which rows to remove

In PostgreSQL, you can truncate multiple tables at once by separating the table names with commas.

Q16

Single Choice

What happens to the sequences associated with a table when you issue a TRUNCATE command in PostgreSQL with the RESTART IDENTITY option?

The sequences are unaffected The sequences are dropped The sequences are reset to their initial valuesThe sequences continue from the current value

When using the RESTART IDENTITY option with TRUNCATE, all sequences associated with the truncated tables are reset to their starting values.

Q17

Single Choice

Consider the following PostgreSQL SQL command:

SQL Code

TRUNCATE TABLE employees CASCADE;
What does the CASCADE option do in this context?

Removes all rows from the employees table and all related tables Deletes the employees table Truncates the employees table only if it has no foreign keysIt causes an error if the employees table has foreign keys

The CASCADE option with TRUNCATE will remove all rows from the employees table and any tables that have foreign keys referencing it.

Q18

Single Choice

If you have a foreign key relationship between two tables, and you execute TRUNCATE on the parent table in PostgreSQL, what must be true for the operation to succeed?

The child table must be truncated first The foreign key must be set to ON DELETE CASCADE The CASCADE option must be used with the TRUNCATE commandThe foreign key constraint must be disabled

In PostgreSQL, to truncate a parent table that has foreign key constraints, the CASCADE option must be used to ensure that the child tables are also truncated.

Q19

Single Choice

What is the impact of using TRUNCATE ... RESTART IDENTITY CASCADE in PostgreSQL?

It truncates the table and leaves the sequences untouched It truncates the table, resets sequences, and truncates related tables It only resets the sequences and does not truncate the tableIt throws an error if there are related tables

The TRUNCATE ... RESTART IDENTITY CASCADE command truncates the specified table, resets the associated sequences, and also truncates any related tables

Q20

Single Choice

Which of the following commands would you use to truncate all tables in a PostgreSQL schema called sales while ensuring all sequences are reset and any dependent tables are also truncated?

SQL Code

DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'sales') LOOP
 EXECUTE 'TRUNCATE TABLE sales.' || r.tablename || ' RESTART IDENTITY CASCADE';
END LOOP;
END $$;

The command will fail because TRUNCATE cannot be used in a loop The command will truncate and reset sequences for all tables in the sales schema The command will truncate the tables but not reset the sequencesThe command will drop the sales schema

The given PL/pgSQL block correctly loops through all tables in the sales schema and truncates each one while resetting sequences and cascading to dependent tables.

Q21

Multiple Choice

Which SQL command truncates a table in PostgreSQL, removing all rows but keeping the table structure intact?

SQL Code

TRUNCATE TABLE employees;

TRUNCATE TABLE employees; DELETE FROM employees; DROP TABLE employees;TRUNCATE employees;

This command removes all rows from the 'employees' table without deleting the table itself. The table's structure and its constraints remain intact.

Q22

Multiple Choice

Which SQL command truncates multiple tables in a single command in PostgreSQL?

SQL Code

TRUNCATE TABLE employees, departments;

TRUNCATE TABLE employees, departments; DELETE FROM employees; DELETE FROM departments; TRUNCATE employees, departments;TRUNCATE TABLE employees; TRUNCATE TABLE departments;

This command removes all rows from both 'employees' and 'departments' tables in a single command.

Q23

Multiple Choice

Which SQL command truncates a table and cascades the truncation to related tables in PostgreSQL?

SQL Code

TRUNCATE TABLE orders CASCADE;

TRUNCATE TABLE orders CASCADE; DELETE FROM orders CASCADE; TRUNCATE orders CASCADE;TRUNCATE TABLE orders;

This command truncates the 'orders' table and automatically truncates any related tables with foreign key constraints that reference 'orders'.

Q24

Multiple Choice

Which SQL command is used to truncate a table but restrict the operation if it has foreign key references in PostgreSQL?

SQL Code

TRUNCATE TABLE sales RESTRICT;

TRUNCATE TABLE sales RESTRICT; TRUNCATE TABLE sales CASCADE; TRUNCATE sales RESTRICT;DELETE FROM sales WHERE EXISTS (SELECT 1 FROM orders WHERE sales.order_id = orders.order_id);

This command attempts to truncate the 'sales' table but will fail if there are any foreign key constraints that depend on it, due to the RESTRICT option.

Q25

Multiple Choice

Which SQL command is used to truncate a table while restarting its associated sequence in PostgreSQL?

SQL Code

TRUNCATE TABLE inventory RESTART IDENTITY;

TRUNCATE TABLE inventory RESTART IDENTITY; TRUNCATE TABLE inventory; TRUNCATE TABLE inventory CASCADE;TRUNCATE TABLE inventory RESTART SEQUENCE;

This command truncates the 'inventory' table and resets any sequences associated with it, such as AUTO_INCREMENT columns, to their initial values.

Q26

Multiple Choice

Which SQL command truncates a table and logs the operation for recovery purposes in PostgreSQL?

SQL Code

TRUNCATE TABLE logs WITH IDENTITY;

TRUNCATE TABLE logs WITH IDENTITY; TRUNCATE TABLE logs; TRUNCATE TABLE logs WITH RESTART IDENTITY;TRUNCATE TABLE logs CASCADE;

This command truncates the 'logs' table and ensures that the truncation operation is logged, making it possible to recover the table data in case of failure.

Q27

Multiple Choice

Which SQL command is used to truncate a table without affecting the associated sequences in PostgreSQL?

SQL Code

TRUNCATE TABLE transactions CONTINUE IDENTITY;

TRUNCATE TABLE transactions CONTINUE IDENTITY; TRUNCATE TABLE transactions RESTART IDENTITY; TRUNCATE TABLE transactions;TRUNCATE TABLE transactions CASCADE;

This command truncates the 'transactions' table but does not reset any associated sequences, allowing them to continue from their current values.

Q28

Multiple Choice

Which SQL command truncates a table and triggers associated foreign key actions in PostgreSQL?

SQL Code

TRUNCATE TABLE customers CASCADE;

TRUNCATE TABLE customers CASCADE; TRUNCATE TABLE customers; TRUNCATE TABLE customers RESTRICT;TRUNCATE TABLE customers WITH IDENTITY;

This command truncates the 'customers' table and triggers any foreign key actions (such as deletion or cascading) associated with the truncation.

Q29

Multiple Choice

Which SQL command is used to truncate a partitioned table along with all of its partitions in PostgreSQL?

SQL Code

TRUNCATE TABLE partitioned_sales;

TRUNCATE TABLE partitioned_sales; TRUNCATE TABLE partitioned_sales PARTITIONS; TRUNCATE partitioned_sales;TRUNCATE TABLE partitioned_sales CASCADE;

This command truncates the 'partitioned_sales' table, including all of its partitions, removing all rows from the table and its partitions.

Q30

Multiple Choice

Which SQL command is used to truncate a table in PostgreSQL and log the operation for auditing?

SQL Code

TRUNCATE TABLE audit_logs WITH IDENTITY;

TRUNCATE TABLE audit_logs WITH IDENTITY; TRUNCATE TABLE audit_logs; TRUNCATE TABLE audit_logs RESTART IDENTITY;TRUNCATE TABLE audit_logs CASCADE;

This command truncates the 'audit_logs' table and ensures that the truncation operation is logged for auditing purposes.

PostgreSQL Database Quiz Questions