Thread: How to select text field as interger

Dear members,


My table consist off the following fields


CREATE TABLE test
(  id bigserial NOT NULL,  name character varying(100),   CONSTRAINT logs_pkey PRIMARY KEY (log_id)
)


with value :

ID    Name
1      001
2        01
3          1
4        11

Select name from test where name = '1' ;

results in :

Name  1


If the 'Name' field was define as a serial the result set ought to be

Name  001    01      1


Question?

How can I have the same result set as above

Name  001    01      1


with the name field defined as character varying.



Thanks

Jerrel Kemper <jerrel@s-wise.nl> wrote:

> My table consist off the following fields

> CREATE TABLE test
> (
>   id bigserial NOT NULL,
>   name character varying(100),
>    CONSTRAINT logs_pkey PRIMARY KEY (log_id)
> )

> with value :

> ID    Name
> 1      001
> 2        01
> 3          1
> 4        11

> Select name from test where name = '1' ;

> results in :

> Name
>   1

> If the 'Name' field was define as a serial the result set ought to be

> Name
>   001
>     01
>       1

> Question?

> How can I have the same result set as above

> Name
>   001
>     01
>       1

> with the name field defined as character varying.

Ah! Your excentric formatting and the reference to "serial"
had me wondering if the indentation had any meaning. You can
select the rows where the "Name" field is numerically equal
to 1 by casting it to an integer and then comparing it to 1,
i. e.:

| SELECT Name FROM Test WHERE Name::INT = 1;

Tim

Tim Landscheidt <tim@tim-landscheidt.de> writes:
> Jerrel Kemper <jerrel@s-wise.nl> wrote:
>> How can I have the same result set as above

>> Name
>> 001
>> 01
>> 1

>> with the name field defined as character varying.

> Ah! Your excentric formatting and the reference to "serial"
> had me wondering if the indentation had any meaning. You can
> select the rows where the "Name" field is numerically equal
> to 1 by casting it to an integer and then comparing it to 1,
> i. e.:

> | SELECT Name FROM Test WHERE Name::INT = 1;

But note that this is going to throw an error if there are any table
rows where the name field *doesn't* contain a valid integer.
        regards, tom lane

>> | SELECT Name FROM Test WHERE Name::INT = 1;
>
> But note that this is going to throw an error if there are any table
> rows where the name field *doesn't* contain a valid integer.

and it recalls the problem of error suppressing once again