truncate function doesnt work in postgres

Question

I have created the following function to truncate bunch of tables starting with "irm_gtresult". There are no syntax errors in my function, but the function doesn't truncate the tables when I run it. What could be wrong here?

My Postgres db version is 8.4.

create or replace function br()
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
row text;
BEGIN
 FOR row IN
select table_name from information_schema.tables where table_name ILIKE 'irm_gtresult%'
LOOP
EXECUTE 'TRUNCATE TABLE ' || row;
END LOOP;
END;
$$;

Call:

select br();

You are querying all the tables that start with irm_gtresult, not irm. Is that intentional? — Mureinik
– Mureinik, Commented Jun 16, 2015 at 13:51
I pasted the code it is not a sceenshot, I judge upon size of the directory where the database resides. Yes, I want intentionally truncate those table that start with 'irm_gtresult'. In fact the standalone select table_name from information_schema.tables where table_name ILIKE 'irm_gtresult%' returns the list of tables exactly that I want to truncate. — d88
– d88, Commented Jun 16, 2015 at 14:04
You need to provide more information. Your version of Postgres? How exactly do you run it? Connected to the right database? Do you get any messages? If 'irm_gtresult%' is the pattern you want, please fix the text of your question. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 16, 2015 at 14:05
Use row. table_name in your TRUNCATE statement, not just row. — Frank Heikens
– Frank Heikens, Commented Jun 16, 2015 at 14:09
@FrankHeikens: row isn't actually a row in his code. That's just a very unfortunate variable name. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 16, 2015 at 14:11

Community · Accepted Answer · 2017-05-23 12:29:49Z

1

Your code is valid. I tested and it works for me in Postgres 9.4.
Using the outdated and unsupported version 8.4 (like you added) may be the problem. The version is just too old, consider upgrading to a current version.

However, I have a couple of suggestions:

Don't use key word row as variable name.
You don't need to loop, you can TRUNCATE all tables in a single command. Faster, shorter.
You may need to add CASCADE if there are dependencies. Be aware of the effect.

CREATE OR REPLACE FUNCTION br()
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
          || string_agg(format('%I.%I', schemaname, tablename), ',')
          || ' CASCADE'
      FROM   pg_tables t
      WHERE  tablename ILIKE 'irm_gtresult%'
      AND    schemaname = 'public'
      -- AND tableowner = 'postgres'  -- optionaly restrict to one user
      );
END
$func$  LANGUAGE plpgsql;

Call:

SELECT br();

I am using the view pg_tables from the system catalog. You can as well use information_schema.tables like you did. Note the subtle differences:

How to check if a table exists in a given schema

2 Comments

user330315 Over a year ago

Another potential reason: d88 is not using auto commit and simply not committing

d88 Over a year ago

Guys, thank you very much for your help! Adding 'CASCADE' into the statement solved the issue.And sorry for my annoying style of writing the post, next time I will bear in mind the rules;)

Oriol Mont i Creus · Accepted Answer · 2022-10-14 14:05:33Z

0

To truncate in postgres you just have to use the TRUNC() function.

Example:

SELECT TRUNC(price, 0) AS truncated_price
FROM product;

answered Oct 14, 2022 at 14:05

Oriol Mont i Creus

11 bronze badge

Collectives™ on Stack Overflow

truncate function doesnt work in postgres

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related