Generate truncate table statements in PostgreSQL

To generate a script in postgreSQL, equivalent to the Oracle-style shown below:

select 'truncate table ' || table_name || ' cascade;' from dba_tables where owner='MYSCHEMA';

, put this in a file called gen_truncate.sql

/*
| Setting                  | Effect                                                    |
| ------------------------ | --------------------------------------------------------- |
| `\o /path/to/file`       | Redirects all query output to the file                    |
| `\pset format unaligned` | Produces plain text output (no table formatting)          |
| `\pset tuples_only on`   | Suppresses headers and row counts                         |
| `\pset footer off`       | Removes `x rows` footer                                   |
| `\pset border 0`         | Removes any border formatting (mostly for aligned format) |
*/

\o truncate_tables.sql
\pset format unaligned
\pset tuples_only on
\pset footer off
\pset border 0
SELECT 'TRUNCATE TABLE ' || schemaname || '.' || tablename || ' CASCADE;' AS stmt
FROM pg_tables
WHERE schemaname = 'myschema';
\o

Execute it:

psql mydb -f gen_truncate.sql

or login to the desired database directly as the owner of the table:

psql -h localhost -d mydb -U myuser -f gen_truncate.sql

Do I need special privileges to create temporary tables in a database in PostgreSQL?

To verify if your user has the privilege to create temporary segments in a postgreSQL database, use this check:

psql -h localhost -d mydb -U myuser

 SELECT has_database_privilege(current_user, current_database(), 'TEMP');

If the value returned shows:

has_database_privilege
------------------------
 f

To be able to create a temporary table in this database, my user needs privileges to do so:

psql
postgres=# grant temp on database mydb to myser;
GRANT

The same test will now yield true instead of false:

SELECT has_database_privilege(current_user, current_database(), 'TEMP');
 has_database_privilege
------------------------
 t
(1 row)

I can now create a temporary table:

CREATE TEMP TABLE table_counts (table_name text, row_count bigint);

Before the grant, the access privileges for my database was

mydb=> \l+ mydb

  |      Access privileges       |
  +------------------------------+
  | postgres=CTc/postgres       +|
  | myuser=c/postgres           +|
 

After the grant:

  |      Access privileges       |
  +------------------------------+
  | postgres=CTc/postgres       +|
  | myuser=Tc/postgres          +|

Notice how the privileges for "myuser" has a "T" ammended to it. This indicate the permission to create temporary objects.