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
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 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('
||
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
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
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
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
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!
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
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
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
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
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
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
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
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
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
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
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 =
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
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 ' ||
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
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
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
24 matches
Mail list logo
