truncate script tables in schema does not work

Question

I am looking for a solution. I am trying to truncate all the tables in my postgres database:

I am using a simple SQL script

SELECT 'TRUNCATE ' || table_name || ';'
FROM information_schema.tables WHERE table_schema='sda' AND table_type='BASE TABLE';

unfortunately it does not work because many relations do not exist.

please help. ( i am using postgresql 9.2)

You can try with `SELECT tablename FROM pg_catalog.pg_tables where shemaname = 'sda'` but that is more or less same as you did. Can you provide more info - like sample output, or what are the tables that are in information_schema and not in exists in the DB? — Michał Zaborowski, Dec 16 '17 at 12:40
would you check [this question](https://stackoverflow.com/questions/2829158/truncating-all-tables-in-a-postgres-database) ! — Gholamali Irani, Dec 16 '17 at 15:34

score 0 · Answer 1 · answered Dec 16 '17 at 19:46

prepare:

t=# create schema sda;
CREATE SCHEMA
t=# create table sda."BASE TABLE"();
CREATE TABLE

try:

t=# SELECT format('TRUNCATE %I;',table_name)
    FROM information_schema.tables WHERE table_schema='sda' AND table_type = 'BASE TABLE';
         format
------------------------
 TRUNCATE "BASE TABLE";
(1 row)
t=# TRUNCATE "BASE TABLE";
TRUNCATE TABLE

so I assume you just did not treat table a identifier, like:

t=# TRUNCATE BASE TABLE;
ERROR:  syntax error at or near "TABLE"
LINE 1: TRUNCATE BASE TABLE;

also - upper case with space in name often leads to human errors, better use standard no case names...

I fixed it. the relations where not found becuase I was not logged in as the user responible for the given tables — Kirill Armas, Dec 24 '17 at 12:00

truncate script tables in schema does not work

1 Answers1