pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate new field
3+ messages / 3 participants
[nested] [flat]
* create new field
@ 2003-10-06 16:35 Chris Faulkner <chrisf@oramap.com>
2003-10-06 16:44 ` Re: create new field Josh Berkus <josh@agliodbs.com>
2003-10-06 16:56 ` Re: create new field Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 3+ messages in thread
From: Chris Faulkner @ 2003-10-06 16:35 UTC (permalink / raw)
To: pgsql-sql
Hello
I would like to change the type of a column. At the moment, it is varchar(4)
but I would like it to be int. All values in the field at the moment are
actually integer.
I tried a way I had seen in the archives - it foes along the lines of adding
a column, using update, drop the old column and rename the new one.
alter table tab add column new_col int4;
update tab set new_col = "OLD_COL";
ERROR: column "new_col" is of type integer but expression is of type
characte
r
You will need to rewrite or cast the expression
OK - so I tried casting.
template1=# update tab set new_col = "OLD_COL"::int4;
ERROR: Cannot cast type character to integer
I understand this - some tables might have characters in the varchar but how
to get around it in my case ? I know that my character field has only
integers in it ?
Thanks
Chris
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create new field
2003-10-06 16:35 create new field Chris Faulkner <chrisf@oramap.com>
@ 2003-10-06 16:44 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2003-10-06 16:44 UTC (permalink / raw)
To: Chris Faulkner <chrisf@oramap.com>; pgsql-sql
Chris,
> template1=# update tab set new_col = "OLD_COL"::int4;
> ERROR: Cannot cast type character to integer
>
> I understand this - some tables might have characters in the varchar but
> how to get around it in my case ? I know that my character field has only
> integers in it ?
Actually, you just need to use the to_number function as an intermediary:
UPDATE tab SET new_col = CAST(to_number("OLD_COL", '9999') AS INT);
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create new field
2003-10-06 16:35 create new field Chris Faulkner <chrisf@oramap.com>
@ 2003-10-06 16:56 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2003-10-06 16:56 UTC (permalink / raw)
To: Chris Faulkner <chrisf@oramap.com>; +Cc: pgsql-sql
On Mon, Oct 06, 2003 at 17:35:11 +0100,
Chris Faulkner <chrisf@oramap.com> wrote:
>
> OK - so I tried casting.
>
> template1=# update tab set new_col = "OLD_COL"::int4;
> ERROR: Cannot cast type character to integer
>
> I understand this - some tables might have characters in the varchar but how
> to get around it in my case ? I know that my character field has only
> integers in it ?
You want to use to_number to do the conversion.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-10-06 16:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-06 16:35 create new field Chris Faulkner <chrisf@oramap.com>
2003-10-06 16:44 ` Josh Berkus <josh@agliodbs.com>
2003-10-06 16:56 ` Bruno Wolff III <bruno@wolff.to>
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