pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to select text field as interger
4+ messages / 4 participants
[nested] [flat]
* How to select text field as interger
@ 2010-06-29 20:51 Jerrel Kemper <jerrel@s-wise.nl>
2010-06-29 21:06 ` Re: How to select text field as interger Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 4+ messages in thread
From: Jerrel Kemper @ 2010-06-29 20:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to select text field as interger
2010-06-29 20:51 How to select text field as interger Jerrel Kemper <jerrel@s-wise.nl>
@ 2010-06-29 21:06 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-06-29 22:07 ` Re: How to select text field as interger Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tim Landscheidt @ 2010-06-29 21:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to select text field as interger
2010-06-29 20:51 How to select text field as interger Jerrel Kemper <jerrel@s-wise.nl>
2010-06-29 21:06 ` Re: How to select text field as interger Tim Landscheidt <tim@tim-landscheidt.de>
@ 2010-06-29 22:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-30 08:12 ` Re: How to select text field as interger silly sad <sad@bankir.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-06-29 22:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to select text field as interger
2010-06-29 20:51 How to select text field as interger Jerrel Kemper <jerrel@s-wise.nl>
2010-06-29 21:06 ` Re: How to select text field as interger Tim Landscheidt <tim@tim-landscheidt.de>
2010-06-29 22:07 ` Re: How to select text field as interger Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-06-30 08:12 ` silly sad <sad@bankir.ru>
0 siblings, 0 replies; 4+ messages in thread
From: silly sad @ 2010-06-30 08:12 UTC (permalink / raw)
To: pgsql-sql
>> | 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-06-30 08:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-29 20:51 How to select text field as interger Jerrel Kemper <jerrel@s-wise.nl>
2010-06-29 21:06 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-06-29 22:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-30 08:12 ` silly sad <sad@bankir.ru>
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