Hi!
That looks sane, though you didn't need the WITH.
I changed select to code below.
If same table name appears in multiple schemas, it generates duplicate alter
column clauses which cause error.
How to fix it to generate proper sql ?
I added n.nspname='myschame' as shown in code below but
On 10/9/14, 12:41 AM, Andrus wrote:
Hi!
>There really is no easy way to make a single ALTER for each table unless you
use a programming language.
I’snt SQL a programming language ?
>However, adding a GROUP BY c.relname,a.attname
>would certainly simplify editing. Then you can combine all the
"Andrus" writes:
> Hi!
>
> Thank you.
>
>>This revised query should give you what you need:
>>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
>>|| quote_ident(c.relname)
>>|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar('
>> || i.character_maximum_l
Hi!
>There really is no easy way to make a single ALTER for each table unless you
>use a programming language.
I’snt SQL a programming language ?
>However, adding a GROUP BY c.relname, a.attname
>would certainly simplify editing. Then you can combine all the
>ALTER COLUMN's f
There really is no easy way to make a single ALTER for each table unless
you use a programming language. However, adding a
GROUP BY c.relname,
a.attname
would certainly simplify editing. Then you can combine all the
ALTER COLUMN's for each table.
On Wed, Oct 8, 2014 at 6:21 PM,
Hi!
Thank you.
>This revised query should give you what you need:
>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
>|| quote_ident(c.relname)
>|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar('
> || i.character_maximum_length || ');'
> FROM pg_class
This revised query should give you what you need:
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar(' || i.character_maximum_length || ');'
FROM pg_class c
JOIN pg_namespace
Hi!
Using Toms recommendation I added not attisdropped and now got the query
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar;'
FROM pg_class c
JOIN pg_namespace n ON n.
Hi!
also, it generates statement which tries to change all columns to one character
length columns.
Andrus.
From: Andrus
Sent: Monday, October 06, 2014 8:11 PM
To: Melvin Davidson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Converting char to varchar automatically
Hi!
>SEL
On 10/6/14, 6:16 PM, Tom Lane wrote:
Jim Nasby writes:
Just a heads-up: each of those ALTER's will rewrite the table, so unless your
database is tiny this will be a slow process. There's ways to work around that,
but they're significantly more complicated.
I think he's trying to get rid of
On Tue, Oct 7, 2014 at 11:02 AM, Tom Lane wrote:
> Roughly speaking it's the pairs that have a binary (WITHOUT FUNCTION)
> coercion according to pg_cast, although we have special logic for a few
> cases such as varchar(M) -> varchar(N).
That ones?
select t1.typname, t2.typname
from pg_cast, pg_t
Sergey Konoplev writes:
> On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane wrote:
>> Sergey Konoplev writes:
>>> BTW, where can I find a list of type1->type2 pairs that doesn't
>>> require full table lock for conversion?
>> There aren't any. Sometimes you can skip a table rewrite, but that
>> doesn't
On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane wrote:
> Sergey Konoplev writes:
>> BTW, where can I find a list of type1->type2 pairs that doesn't
>> require full table lock for conversion?
>
> There aren't any. Sometimes you can skip a table rewrite, but that
> doesn't mean that a lesser lock is pos
Sergey Konoplev writes:
> BTW, where can I find a list of type1->type2 pairs that doesn't
> require full table lock for conversion?
There aren't any. Sometimes you can skip a table rewrite, but that
doesn't mean that a lesser lock is possible.
regards, tom lane
--
Sen
BTW, where can I find a list of type1->type2 pairs that doesn't
require full table lock for conversion?
ps. Sorry for top posting.
On Mon, Oct 6, 2014 at 4:20 PM, Tom Lane wrote:
> Melvin Davidson writes:
>> Also, don't forget to test for relkind = 'r'. My bad from before.
>
> In principle you
Melvin Davidson writes:
> Also, don't forget to test for relkind = 'r'. My bad from before.
In principle you need to ignore attisdropped columns as well.
Thinking about Jim's point about speed: it'd be wise to collapse any
updates for multiple columns in the same table into one ALTER command,
so
Jim Nasby writes:
> Just a heads-up: each of those ALTER's will rewrite the table, so unless your
> database is tiny this will be a slow process. There's ways to work around
> that, but they're significantly more complicated.
I think he's trying to get rid of all the blank-padding he's got righ
Also, don't forget to test for relkind = 'r'. My bad from before.
Revised query is below.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar;'
FROM pg_class c
JOIN pg_nam
On 10/6/14, 12:41 PM, hari.fu...@gmail.com wrote:
Melvin Davidson writes:
This query might work for you, but double check all result statements first.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TY
Melvin Davidson writes:
> This query might work for you, but double check all result statements first.
>
> SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
> quote_ident(c.relname)
>|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
> FROM pg_class c
> JOIN pg_
Hi!
>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
>quote_ident(c.relname)
> || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
> FROM pg_class c
> JOIN pg_namespace n ON n.oid = c.relnamespace
> JOIN pg_attribute a ON a.attrelid = c.oid
> JOIN pg_type t ON t
This query might work for you, but double check all result statements first.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
On 10/6/2014 5:29 AM, Andrus wrote:
Database contains about 300 tables.
Most of them contain columns of char(n) type.
How to convert all those columns to varchar automatically ?
Is it possible to run some update commands in system tables for this ?
Or is it possible to create pgsql script which c
Database contains about 300 tables.
Most of them contain columns of char(n) type.
How to convert all those columns to varchar automatically ?
Is it possible to run some update commands in system tables for this ?
Or is it possible to create pgsql script which creates dynamically alter table
alte
24 matches
Mail list logo
