Re: [GENERAL] Converting char to varchar automatically
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 problem persists.
Andrus.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' ' ||
string_agg(
' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' ||
i.character_maximum_length ||')',
',' ) || ';' as statement
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.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped
and n.nspname='myschema'
group by n.nspname, c.relname
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
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
ALTER COLUMN's for each table.
I wrote
with stem as (
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) as prefix ,
string_agg(
' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' ||
i.character_maximum_length ||')',
',' ) as body
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.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped
group by 1
)
select prefix || ' '|| body || ';' as statement
from stem
Is this prefect ?
That looks sane, though you didn't need the WITH.
In the future, you'll probably find it easier to go with information schema
directly since then you don't have to worry about things like attisdropped.
Also, you mentioned that type varchar restricts length to 1. That's not true.
varchar with no specifier has unlimited[1] length:
decibel@decina.attlocal=# create table t(t varchar);
CREATE TABLE
decibel@decina.attlocal=# \d t
Table public.t
Column | Type| Modifiers
+---+---
t | character varying |
decibel@decina.attlocal=# insert into t values( '123' );
INSERT 0 1
decibel@decina.attlocal=#
[1]: In reality you're limited to ~1GB of data
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
Andrus kobrule...@hot.ee 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_length || ');'
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.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped;
How to create single alter table command for every table ?
Can we use string concat aggregate function or window functions or plpgsql or
something other ?
string_agg should do it:
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) ||
'.' || quote_ident(c.relname) || ' ' ||
string_agg('ALTER COLUMN ' || quote_ident(a.attname) ||
' TYPE varchar(' || i.character_maximum_length || ')', ', ') || ';'
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.oid = a.atttypid
JOIN information_schema.columns i ON
i.table_name = c.relname AND i.column_name = a.attname
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped
GROUP BY n.nspname, c.relname;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
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! 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.oid = a.atttypid WHERE t.typname = 'char' AND n.nspname 'pg_catalog'; It does not return any data. Andrus.
Re: [GENERAL] Converting char to varchar automatically
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.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typname = 'bpchar' AND c.relkind = 'r' AND n.nspname 'pg_catalog' and not attisdropped; Will this create commands which replace all user-defined char things in database to varchar ? TYPE varchar creates single character column so most alter table command will fail. How to change this so that original char column width is kept ? I looked into tables used in this query but havent found column which holds char column defined width. How get it or is it better to re-write this query using informational_schema ? How to change this query so that it creates single alter table command for every table (with multiple alter column clauses) to increase conversion speed ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
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 n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped;
On Wed, Oct 8, 2014 at 3:34 PM, Andrus kobrule...@hot.ee wrote:
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.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped;
Will this create commands which replace all user-defined char things in
database to varchar ?
TYPE varchar creates single character column so most alter table command
will fail.
How to change this so that original char column width is kept ?
I looked into tables used in this query but havent found column which holds
char column defined width.
How get it or is it better to re-write this query using
informational_schema
?
How to change this query so that it creates single alter table command for
every table
(with multiple alter column clauses) to increase conversion speed ?
Andrus.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] Converting char to varchar automatically
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 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.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped;
How to create single alter table command for every table ?
Can we use string concat aggregate function or window functions or plpgsql or
something other ?
Andrus.
Re: [GENERAL] Converting char to varchar automatically
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, Andrus kobrule...@hot.ee wrote:
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 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.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped;
How to create single alter table command for every table ?
Can we use string concat aggregate function or window functions or plpgsql
or something other ?
Andrus.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] Converting char to varchar automatically
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 for each table.
I wrote
with stem as (
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) as prefix ,
string_agg(
' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' ||
i.character_maximum_length ||')',
',' ) as body
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.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
AND c.relkind = 'r'
AND n.nspname 'pg_catalog' and not attisdropped
group by 1
)
select prefix || ' '|| body || ';' as statement
from stem
Is this prefect ?
Andrus.
Re: [GENERAL] Converting char to varchar automatically
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 t...@sss.pgh.pa.us wrote: Melvin Davidson melvin6...@gmail.com 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 that you only rewrite the table once, not once per column. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
Sergey Konoplev gray...@gmail.com 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane t...@sss.pgh.pa.us wrote: Sergey Konoplev gray...@gmail.com 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. Oh, sorry, it was a typo, I meant that doesn't require a full table rewrite. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
Sergey Konoplev gray...@gmail.com writes: On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane t...@sss.pgh.pa.us wrote: Sergey Konoplev gray...@gmail.com 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. Oh, sorry, it was a typo, I meant that doesn't require a full table rewrite. 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). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
On Tue, Oct 7, 2014 at 11:02 AM, Tom Lane t...@sss.pgh.pa.us 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_type as t1, pg_type as t2 where t1.oid = castsource and t2.oid = casttarget and castmethod = 'b' order by 1, 2; -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
On 10/6/14, 6:16 PM, Tom Lane wrote: Jim Nasby jim.na...@bluetreble.com 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 right now, so table rewrites are unavoidable. Right, but there's other ways this could be done without requiring an outage. Like creating the new column with temporary name, put trigger on table, etc, etc. Having dealt with an environment where downtime was thousands of dollars per minute I've gotten very creative at not taking outages. :) -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Converting char to varchar automatically
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 alter column commands and PERFORMs them ? Any tables have primary keys with char(n) columns and foreign keys on them. Foreign keys are deferrable and initially immediate. Will foreign keys allow to perform such alter table alter column commands ? Or is there better way. Andrus.
Re: [GENERAL] Converting char to varchar automatically
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 creates dynamically alter table alter column commands and PERFORMs them ? Any tables have primary keys with char(n) columns and foreign keys on them. Foreign keys are deferrable and initially immediate. Will foreign keys allow to perform such alter table alter column commands ? Or is there better way. Andrus. I'd use a little perl. Or if your editor has macros, you could use that. change: create table bob ( id char(50), .. ) to alter table bob alter id type varchar(50); You might be able to query them out if you wanted: select table_name, column_name, character_maximum_length from information_schema.columns where data_type = 'character' Then use that to generate the alter table commands. Hum... this might also work: select 'alter table ' || table_name || ' alter ' || column_name etc but that might try changing system tables which would be bad. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
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 JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typname = 'char' AND n.nspname 'pg_catalog'; On Mon, Oct 6, 2014 at 6:29 AM, Andrus kobrule...@hot.ee 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 creates dynamically alter table alter column commands and PERFORMs them ? Any tables have primary keys with char(n) columns and foreign keys on them. Foreign keys are deferrable and initially immediate. Will foreign keys allow to perform such alter table alter column commands ? Or is there better way. Andrus. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Converting char to varchar automatically
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.oid = a.atttypid WHERE t.typname = 'char' AND n.nspname 'pg_catalog'; It does not return any data. Andrus.
Re: [GENERAL] Converting char to varchar automatically
Melvin Davidson melvin6...@gmail.com 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_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typname = 'char' AND n.nspname 'pg_catalog'; Make that t.typname = 'bpchar'. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
On 10/6/14, 12:41 PM, hari.fu...@gmail.com wrote: Melvin Davidson melvin6...@gmail.com 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_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typname = 'char' AND n.nspname 'pg_catalog'; Make that t.typname = 'bpchar'. 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. -- Jim Nasby, Data Architect, Blue Treble Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
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_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typname = 'bpchar' AND c.relkind = 'r' AND n.nspname 'pg_catalog'; On Mon, Oct 6, 2014 at 6:18 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 10/6/14, 12:41 PM, hari.fu...@gmail.com wrote: Melvin Davidson melvin6...@gmail.com 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_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typname = 'char' AND n.nspname 'pg_catalog'; Make that t.typname = 'bpchar'. 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. -- Jim Nasby, Data Architect, Blue Treble Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Converting char to varchar automatically
Jim Nasby jim.na...@bluetreble.com 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 right now, so table rewrites are unavoidable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
Melvin Davidson melvin6...@gmail.com 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 that you only rewrite the table once, not once per column. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
