alter column to inet get error.

Started by Steve Clarkover 15 years ago5 messagesgeneral

Jump to latest

Steve Clark

sclark@netwolves.com

over 15 years ago

Hello List,

I want to change some columns in a database
that were originally created as char varying to
inet.

When I try I get an error. Is there anyway to work
around this?

See below for table definition.

alter TABLE kernel_gre ALTER COLUMN source_ip TYPE inet;
ERROR: column "source_ip" cannot be cast to type "inet"

current table contents:
interface | source_ip | dest_ip | physical_ip | status | physical_src_ip | tunnel_netmask | key | state | broadcast
-----------+-----------+-----------+-------------+--------+-----------------+-----------------+-----+-------+-----------
gre2 | 10.1.1.2 | 10.1.1.1 | 1.1.1.1 | t | 1.1.1.2 | 255.255.255.255 | | f | f
gre3 | 10.1.1.4 | 10.1.1.3 | 1.1.1.3 | t | 1.1.1.4 | 255.255.255.255 | | f | f
gre4 | 10.1.1.6 | 10.1.1.5 | 1.1.1.5 | t | 1.1.1.6 | 255.255.255.255 | | f | f
gre5 | 10.1.1.8 | 10.1.1.7 | 1.1.1.7 | t | 1.1.1.8 | 255.255.255.255 | | f | f
gre6 | 10.1.1.10 | 10.1.1.9 | 1.1.1.9 | t | 1.1.1.10 | 255.255.255.255 | | f | f
gre7 | 10.1.1.12 | 10.1.1.11 | 1.1.1.11 | t | 1.1.1.12 | 255.255.255.255 | | f | f
gre8 | 10.1.1.14 | 10.1.1.13 | 1.1.1.13 | t | 1.1.1.14 | 255.255.255.255 | | f | f
gre9 | 10.1.1.16 | 10.1.1.15 | 1.1.1.15 | t | 1.1.1.16 | 255.255.255.255 | | f | f
gre10 | 10.1.1.18 | 10.1.1.17 | 1.1.1.17 | t | 1.1.1.18 | 255.255.255.255 | | f | f
gre11 | 10.1.1.20 | 10.1.1.19 | 1.1.1.19 | t | 1.1.1.20 | 255.255.255.255 | | f | f
gre12 | 10.1.1.22 | 10.1.1.21 | 1.1.1.21 | t | 1.1.1.22 | 255.255.255.255 | | f | f
gre13 | 10.1.1.24 | 10.1.1.23 | 1.1.1.23 | t | 1.1.1.24 | 255.255.255.255 | | f | f
gre14 | 10.1.1.26 | 10.1.1.25 | 1.1.1.25 | t | 1.1.1.26 | 255.255.255.255 | | f | f
gre15 | 10.1.1.28 | 10.1.1.27 | 1.1.1.27 | t | 1.1.1.28 | 255.255.255.255 | | f | f
gre16 | 10.1.1.30 | 10.1.1.29 | 1.1.1.29 | t | 1.1.1.30 | 255.255.255.255 | | f | f
gre17 | 10.1.1.32 | 10.1.1.31 | 1.1.1.31 | t | 1.1.1.32 | 255.255.255.255 | | f | f
gre18 | 10.1.1.34 | 10.1.1.33 | 1.1.1.33 | t | 1.1.1.34 | 255.255.255.255 | | f | f
gre19 | 10.1.1.36 | 10.1.1.35 | 1.1.1.35 | t | 1.1.1.36 | 255.255.255.255 | | f | f
gre20 | 10.1.1.38 | 10.1.1.37 | 1.1.1.37 | t | 1.1.1.38 | 255.255.255.255 | | f | f
gre21 | 10.1.1.40 | 10.1.1.39 | 1.1.1.39 | t | 1.1.1.40 | 255.255.255.255 | | f | f
(20 rows)

Thanks in advance,
--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
www.netwolves.com

Scott Marlowe

scott.marlowe@gmail.com

over 15 years ago

In reply to: Steve Clark (#1)

Re: alter column to inet get error.

On Fri, Sep 3, 2010 at 7:21 AM, Steve Clark <sclark@netwolves.com> wrote:

Hello List,

I want to change some columns in a database
that were originally created as char varying to
inet.

When I try I get an error. Is there anyway to work
around this?

See below for table definition.

Table "public.kernel_gre"
Column | Type | Modifiers
-----------------+-----------------------+---------------
interface | character varying(15) | not null
source_ip | character varying(16) |
dest_ip | character varying(16) |
physical_ip | character varying(16) |
status | boolean | default false
physical_src_ip | character varying(16) |
tunnel_netmask | character varying(16) |
key | character varying(32) |
state | boolean | default false
broadcast | boolean | default false

alter TABLE kernel_gre ALTER COLUMN source_ip TYPE inet;
ERROR: column "source_ip" cannot be cast to type "inet"

Try

alter TABLE kernel_gre ALTER COLUMN source_ip TYPE inet using source_ip::inet
--
To understand recursion, one must first understand recursion.

A. Kretschmer

andreas.kretschmer@schollglas.com

over 15 years ago

In reply to: Steve Clark (#1)

Re: alter column to inet get error.

In response to Steve Clark :

Hello List,

I want to change some columns in a database
that were originally created as char varying to
inet.

When I try I get an error. Is there anyway to work
around this?

See below for table definition.

Table "public.kernel_gre"
Column | Type | Modifiers
-----------------+-----------------------+---------------
interface | character varying(15) | not null
source_ip | character varying(16) |
dest_ip | character varying(16) |
physical_ip | character varying(16) |
status | boolean | default false
physical_src_ip | character varying(16) |
tunnel_netmask | character varying(16) |
key | character varying(32) |
state | boolean | default false
broadcast | boolean | default false

alter TABLE kernel_gre ALTER COLUMN source_ip TYPE inet;
ERROR: column "source_ip" cannot be cast to type "inet"

Try this with explicet cast:

test=# create table ip (ip text);
CREATE TABLE
Zeit: 247,763 ms
test=*# copy ip from stdin;
Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.

127.0.0.1
\.

Zeit: 5199,184 ms
test=*# alter table ip alter column ip type inet using ip::inet;
ALTER TABLE
Zeit: 242,569 ms
test=*# \d ip
Tabelle ï¿½public.ipï¿½
Spalte | Typ | Attribute
--------+------+-----------
ip | inet |

test=*#

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

Steve Clark

sclark@netwolves.com

over 15 years ago

In reply to: A. Kretschmer (#3)

Re: alter column to inet get error.

On 09/03/2010 09:38 AM, A. Kretschmer wrote:

In response to Steve Clark :

Hello List,

I want to change some columns in a database
that were originally created as char varying to
inet.

When I try I get an error. Is there anyway to work
around this?

See below for table definition.

Table "public.kernel_gre"
Column | Type | Modifiers
-----------------+-----------------------+---------------
interface | character varying(15) | not null
source_ip | character varying(16) |
dest_ip | character varying(16) |
physical_ip | character varying(16) |
status | boolean | default false
physical_src_ip | character varying(16) |
tunnel_netmask | character varying(16) |
key | character varying(32) |
state | boolean | default false
broadcast | boolean | default false

alter TABLE kernel_gre ALTER COLUMN source_ip TYPE inet;
ERROR: column "source_ip" cannot be cast to type "inet"

Try this with explicet cast:

test=# create table ip (ip text);
CREATE TABLE
Zeit: 247,763 ms
test=*# copy ip from stdin;
Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.

127.0.0.1
\.

Zeit: 5199,184 ms
test=*# alter table ip alter column ip type inet using ip::inet;
ALTER TABLE
Zeit: 242,569 ms
test=*# \d ip
Tabelle »public.ip«
Spalte | Typ | Attribute
--------+------+-----------
ip | inet |

test=*#

Regards, Andreas

Thanks guys, that seems to do the trick. Postgresql ROCKS!!!

--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
www.netwolves.com

A. Kretschmer

andreas.kretschmer@schollglas.com

over 15 years ago

In reply to: Steve Clark (#4)

Re: alter column to inet get error.

In response to Steve Clark :

Try this with explicet cast:

Thanks guys, that seems to do the trick. Postgresql ROCKS!!!

Yeah, definitively!

You are welcome, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99