Thread: alter multiple tables

Hi,

Kodok Marton wrote:
> Hello,
>
> I have in every table columns like:
> username character varying(20) NOT NULL
>
> I want to extend the length of varchar in all tables.

next time you should probably consider using a domain type
(or stick to text)

> Since I have a lot of tables and mirrored backups, I am wondering if
> there is a way to alter automatically all tables where colname matches
> 'username'
>
> Is there a way to do this?

It should be possible to generate a list of tables either via
query or using pg_dump -L with grep and create SQL based on this
(with a script, unix shell) and execute it against the database.

(Test this of course)

HTH
Tino

doing via shell is one way (long way if you use Windows),
is it possible by updateding postgresql master tables?

----- Original Message -----
From: "Tino Wildenhain" <tino@wildenhain.de>
To: "Kodok Marton" <marton@mybusinessanywhere.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, March 16, 2009 6:08 PM
Subject: Re: [GENERAL] alter multiple tables


> Hi,
>
> Kodok Marton wrote:
>> Hello,
>>
>> I have in every table columns like:
>> username character varying(20) NOT NULL
>>
>> I want to extend the length of varchar in all tables.
>
> next time you should probably consider using a domain type
> (or stick to text)
>
>> Since I have a lot of tables and mirrored backups, I am wondering if
>> there is a way to alter automatically all tables where colname matches
>> 'username'
>>
>> Is there a way to do this?
>
> It should be possible to generate a list of tables either via
> query or using pg_dump -L with grep and create SQL based on this
> (with a script, unix shell) and execute it against the database.
>
> (Test this of course)
>
> HTH
> Tino
>

On Mar 16, 12:08 pm, t...@wildenhain.de (Tino Wildenhain) wrote:
> > Since I have a lot of tables and mirrored backups, I am wondering if
> > there is a way to alter automatically all tables where colname matches
> > 'username'
> > Is there a way to do this?

Enter psql with '-E' flag, and see the query that is generated when
using commands like '\dt' (which is for showing the tables in the
current database).

For example:
-bash-3.1$ psql -U some_user -d test_db -E
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

eds_db=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
  r.rolname as "Owner"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 public | test | table | some_user

Use the generated query, and modify it to suit your needs. There are
ways of extracting the columns from a given table name. With that, do
a for loop.

Daniel