Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: How to select text field as interger
Date: Tue, 29 Jun 2010 21:06:26 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 53
Message-ID: <m31vbpsg4t.fsf@passepartout.tim-landscheidt.de>
References: <4C2A5CB4.3000601@s-wise.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:iTYWobSvbMDr9TV6xO2ytHch7vc=

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