pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
convert char to varchar
4+ messages / 3 participants
[nested] [flat]

* convert char to varchar
@ 2007-10-18 05:23  Kostis Mentzelos <mentzelos@gmx.net>
  0 siblings, 2 replies; 4+ messages in thread

From: Kostis Mentzelos @ 2007-10-18 05:23 UTC (permalink / raw)
  To: pgsql-admin

Hi all,
is it possible to convert character fields to character varying using 
pg_dump/pg_restore?

this is my case:
I have a table with character fields:
CREATE TABLE c1 (name char(20), date char(20));
INSERT INTO c1 VALUES ('a',a');
INSERT INTO c1 VALUES ('b',b');

and another table with character varying fields:
CREATE TABLE v1 (name varchar(20), date varchar(20));

when I dump c1 and restore it to v1 my selections on v1 are failed 
because the data on v1 table contains trailing spaces.
SELECT * FROM v1 WHERE name ='a'; (failed)

if I alter types on v1 to character and then back to character varying  
the trailing spaces disappears and the above selection is ok.

Is there any other way to clear trailing spaces when I restore the table?

regards,
kostis.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: convert char to varchar
@ 2007-10-18 14:06  Rodrigo De León <rdeleonp@gmail.com>
  parent: Kostis Mentzelos <mentzelos@gmx.net>
  1 sibling, 0 replies; 4+ messages in thread

From: Rodrigo De León @ 2007-10-18 14:06 UTC (permalink / raw)
  To: Kostis Mentzelos <mentzelos@gmx.net>; +Cc: pgsql-admin

On 10/18/07, Kostis Mentzelos <mentzelos@gmx.net> wrote:
> Is there any other way to clear trailing spaces when I restore the table?

After you restore, you can do:

UPDATE V1
SET
  NAME = TRIM(TRAILING ' ' FROM NAME)
, DATE = TRIM(TRAILING ' ' FROM DATE);



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: convert char to varchar
@ 2007-10-18 15:35  Peter Koczan <pjkoczan@gmail.com>
  parent: Kostis Mentzelos <mentzelos@gmx.net>
  1 sibling, 1 reply; 4+ messages in thread

From: Peter Koczan @ 2007-10-18 15:35 UTC (permalink / raw)
  To: Kostis Mentzelos <mentzelos@gmx.net>; +Cc: pgsql-admin

> Is there any other way to clear trailing spaces when I restore the table?

If you're running 8.x, you can do this in place:

ALTER TABLE c1 ALTER COLUMN name varchar(20) USING rtrim(name), ALTER
COLUMN date varchar(20) USING rtrim(date);

This could take a long time if the table is large or has a lot of indexes on it.

Peter



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: convert char to varchar
@ 2007-10-18 15:37  Peter Koczan <pjkoczan@gmail.com>
  parent: Peter Koczan <pjkoczan@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Peter Koczan @ 2007-10-18 15:37 UTC (permalink / raw)
  To: Kostis Mentzelos <mentzelos@gmx.net>; +Cc: pgsql-admin

On 10/18/07, Peter Koczan <pjkoczan@gmail.com> wrote:
> > Is there any other way to clear trailing spaces when I restore the table?
>
> If you're running 8.x, you can do this in place:
>
> ALTER TABLE c1 ALTER COLUMN name varchar(20) USING rtrim(name), ALTER
> COLUMN date varchar(20) USING rtrim(date);
>
> This could take a long time if the table is large or has a lot of indexes on it.

Oops. The command should be:

ALTER TABLE c1 ALTER COLUMN name TYPE varchar(20) USING rtrim(name),
ALTER COLUMN date TYPE varchar(20) USING rtrim(date);

I forgot the TYPE keyword...it's important.

Peter



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2007-10-18 15:37 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-18 05:23 convert char to varchar Kostis Mentzelos <mentzelos@gmx.net>
2007-10-18 14:06 ` Rodrigo De León <rdeleonp@gmail.com>
2007-10-18 15:35 ` Peter Koczan <pjkoczan@gmail.com>
2007-10-18 15:37   ` Peter Koczan <pjkoczan@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox