Postgres Pro
Downloads
Home   >   mailing lists

Thread: Generating TRUNCATE orders

Generating TRUNCATE orders

From
Laurent ROCHE
Date:
Hi,
 
I wanted to write a SELECT that generates a TRUNCATE TABLE for all the tables in a given schema.
 
So I wrote:
SELECT 'TRUNCATE TABLE '
UNION
SELECT 'my_schema.' || c.relname ||', '
FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid
AND  c.relkind IN ('r' )
AND nc.nspname = 'my_schema'
ORDER BY relname
 
And this fails with the following message:
ERROR: column "relname" does not exist
SQL state:42703
 
If I run only the SELECT after the UNION that works as expected.
Of course, this is not a big deal as I copying and pasting this into a script file any way (and I will add the TRUNCATE TABLE manually).
But I don't understand why this does not work: the 2 SELECTs produce a single char column so from what I understand that should work ! ? !
If some body can explain I will be grateful.
 
 
PS: Of course, I realise the code produced by the SELECTs and UNION would not work straight away, because of the trailing comma !
 
Have fun,
L@u
The Computing Froggy

Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail

Re: Generating TRUNCATE orders

From
Richard Huxton
Date:
Laurent ROCHE wrote:
>
> So I wrote:
> SELECT 'TRUNCATE TABLE '
> UNION
...
> ORDER BY relname
>
> And this fails with the following message:
> ERROR: column "relname" does not exist

> But I don't understand why this does not work: the 2 SELECTs produce a single char column so from what I understand
thatshould work ! ? ! 
> If some body can explain I will be grateful.

The "ORDER BY" is attached to the "UNION" not the second subquery.
Catches everyone out from time to time.

--
   Richard Huxton
   Archonet Ltd

Re: Generating TRUNCATE orders

From
"Scott Marlowe"
Date:
On 10/3/07, Laurent ROCHE <laurent_roche@yahoo.com> wrote:

Would this work:

SELECT
'TRUNCATE TABLE ' ||
'my_schema.' ||
c.relname ||', '
FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid
AND  c.relkind IN ('r' )
AND nc.nspname = 'my_schema'
ORDER BY relname

Re: Generating TRUNCATE orders

From
Erik Jones
Date:
On Oct 3, 2007, at 12:19 PM, Scott Marlowe wrote:

> On 10/3/07, Laurent ROCHE <laurent_roche@yahoo.com> wrote:
>
> Would this work:
>
> SELECT
> 'TRUNCATE TABLE ' ||
> 'my_schema.' ||
> c.relname ||', '
> FROM pg_namespace nc, pg_class c
>   WHERE c.relnamespace = nc.oid
> AND  c.relkind IN ('r' )
> AND nc.nspname = 'my_schema'
> ORDER BY relname

Or, just:

SELECT 'TRUNCATE ' || schemaname || '.' || tablename ';'
FROM pg_tables
WHERE schemname='my_schema'
ORDER BY tablename;

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: Generating TRUNCATE orders

From
Florian Pflug
Date:
Erik Jones wrote:
> On Oct 3, 2007, at 12:19 PM, Scott Marlowe wrote:
>
> SELECT 'TRUNCATE ' || schemaname || '.' || tablename ';'
> FROM pg_tables
> WHERE schemname='my_schema'
> ORDER BY tablename;

To be safe, you'd probably want to write
SELECT 'TRUNCATE' || quote_ident(schemaname) || '.' || quote_ident(tablename) ||
';' ...

Otherwise, table or schema names containing funny characters, upper case, or
spaces will cause trouble..

greetings, Florian Pflug