pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconversion
10+ messages / 6 participants
[nested] [flat]
* conversion
@ 2001-02-26 02:32 Ken Kline <ken@oldbs.com>
2001-02-26 04:11 ` Re: conversion Ken Kline <ken@oldbs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ken Kline @ 2001-02-26 02:32 UTC (permalink / raw)
To: pgsql-sql
Hello,
another brain twister, at least for me...
i have a table of varchar and one of the values I want
to insert into another table, one of the columns is
defined as INTEGER in destination table, column...
and none of these statements seem to work
INSERT INTO pledge_classes (semester, year)
SELECT pseason, to_number('pyear','9999') from temp;
INSERT INTO pledge_classes (semester, year)
SELECT pseason, pyear::integer from temp;
INSERT INTO pledge_classes (semester, year)
SELECT pseason, pyear::numeric(4) from temp;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: conversion
2001-02-26 02:32 conversion Ken Kline <ken@oldbs.com>
@ 2001-02-26 04:11 ` Ken Kline <ken@oldbs.com>
2001-02-26 06:51 ` Re: conversion Christopher Sawtell <csawtell@xtra.co.nz>
2001-02-26 17:06 ` Re: conversion Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 10+ messages in thread
From: Ken Kline @ 2001-02-26 04:11 UTC (permalink / raw)
To: pgsql-sql
follow up....
actually the destination column is defined
as a numeric(4)
the following are the statements again with there error messages:
SELECT pseason, to_number(pyear,'9999') from temp;
ERROR: Bad numeric input format ' '
SELECT pyear::int from temp;
ERROR: Cannot cast type 'varchar' to 'int4'
Ken Kline wrote:
> Hello,
> another brain twister, at least for me...
> i have a table of varchar and one of the values I want
> to insert into another table, one of the columns is
> defined as INTEGER in destination table, column...
> and none of these statements seem to work
>
> INSERT INTO pledge_classes (semester, year)
> SELECT pseason, to_number('pyear','9999') from temp;
>
> INSERT INTO pledge_classes (semester, year)
> SELECT pseason, pyear::integer from temp;
>
> INSERT INTO pledge_classes (semester, year)
> SELECT pseason, pyear::numeric(4) from temp;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: conversion
2001-02-26 02:32 conversion Ken Kline <ken@oldbs.com>
2001-02-26 04:11 ` Re: conversion Ken Kline <ken@oldbs.com>
@ 2001-02-26 06:51 ` Christopher Sawtell <csawtell@xtra.co.nz>
2001-02-26 16:46 ` Re: conversion Ken Kline <ken@oldbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Christopher Sawtell @ 2001-02-26 06:51 UTC (permalink / raw)
To: Ken Kline <ken@oldbs.com>; pgsql-sql
On Mon, 26 Feb 2001 17:11, Ken Kline wrote:
> follow up....
> actually the destination column is defined
> as a numeric(4)
Could you to a \d on each of the tables and tell us the results.
[ ... ]
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: conversion
2001-02-26 02:32 conversion Ken Kline <ken@oldbs.com>
2001-02-26 04:11 ` Re: conversion Ken Kline <ken@oldbs.com>
2001-02-26 06:51 ` Re: conversion Christopher Sawtell <csawtell@xtra.co.nz>
@ 2001-02-26 16:46 ` Ken Kline <ken@oldbs.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ken Kline @ 2001-02-26 16:46 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; +Cc: pgsql-sql
here you go, thanks in advance, ken
Table "temp"
Attribute | Type | Modifier
-----------+-------------+----------
pseason | varchar(15) |
pyear | varchar(5) |
adx=# \d pledge+ _classe4s s
Table "pledge_classes"
Attribute | Type | Modifier
-----------+--------------+------------------------------------------
pc_id | integer | not null default nextval('pc_seq'::text)
semester | varchar(6) |
year | numeric(4,0) |
pc_letter | varchar(20) |
pc_name | varchar(50) |
Index: pc_pk
adx=# \q
bash-2.04$
Script done on Mon Feb 26 11:42:35 2001
Christopher Sawtell wrote:
> On Mon, 26 Feb 2001 17:11, Ken Kline wrote:
> > follow up....
> > actually the destination column is defined
> > as a numeric(4)
>
> Could you to a \d on each of the tables and tell us the results.
>
> [ ... ]
>
> --
> Sincerely etc.,
>
> NAME Christopher Sawtell
> CELL PHONE 021 257 4451
> ICQ UIN 45863470
> EMAIL csawtell @ xtra . co . nz
> CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
>
> -->> Please refrain from using HTML or WORD attachments in e-mails to me
> <<--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: conversion
2001-02-26 02:32 conversion Ken Kline <ken@oldbs.com>
2001-02-26 04:11 ` Re: conversion Ken Kline <ken@oldbs.com>
@ 2001-02-26 17:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2001-02-26 17:06 UTC (permalink / raw)
To: Ken Kline <ken@oldbs.com>; +Cc: pgsql-sql
It looks like you have some rows for pyear which do
not convert cleanly into a number like ' '. What
do you want it to do in such cases?
On Sun, 25 Feb 2001, Ken Kline wrote:
> follow up....
> actually the destination column is defined
> as a numeric(4)
>
> the following are the statements again with there error messages:
>
> SELECT pseason, to_number(pyear,'9999') from temp;
> ERROR: Bad numeric input format ' '
>
> SELECT pyear::int from temp;
> ERROR: Cannot cast type 'varchar' to 'int4'
>
>
>
>
> Ken Kline wrote:
>
> > Hello,
> > another brain twister, at least for me...
> > i have a table of varchar and one of the values I want
> > to insert into another table, one of the columns is
> > defined as INTEGER in destination table, column...
> > and none of these statements seem to work
> >
> > INSERT INTO pledge_classes (semester, year)
> > SELECT pseason, to_number('pyear','9999') from temp;
> >
> > INSERT INTO pledge_classes (semester, year)
> > SELECT pseason, pyear::integer from temp;
> >
> > INSERT INTO pledge_classes (semester, year)
> > SELECT pseason, pyear::numeric(4) from temp;
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Conversion
@ 2002-08-13 03:14 Lonh SENG <slonh@camgsm.com.kh>
2002-08-13 03:32 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Lonh SENG @ 2002-08-13 03:14 UTC (permalink / raw)
To: pgsql-sql
Hi all,
How can I convert from int4 to date time?
Regards,
Lonh
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion
2002-08-13 03:14 Conversion Lonh SENG <slonh@camgsm.com.kh>
@ 2002-08-13 03:32 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-13 07:03 ` Re: Conversion Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 1 reply; 10+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-13 03:32 UTC (permalink / raw)
To: Lonh SENG <slonh@camgsm.com.kh>; pgsql-sql
Is the int4 a UNIX epoch? ie. seconds since 1970?
If so, then this will generally work:
SELECT CAST(int4field AS abstime);
or
SELECT int4field::abstime;
Chris
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Lonh SENG
Sent: Tuesday, 13 August 2002 11:14 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Conversion
Hi all,
How can I convert from int4 to date time?
Regards,
Lonh
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion
2002-08-13 03:14 Conversion Lonh SENG <slonh@camgsm.com.kh>
2002-08-13 03:32 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-13 07:03 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2002-08-13 07:14 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Alexander M. Pravking @ 2002-08-13 07:03 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Lonh SENG <slonh@camgsm.com.kh>; pgsql-sql
On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote:
> Is the int4 a UNIX epoch? ie. seconds since 1970?
>
> If so, then this will generally work:
>
> SELECT CAST(int4field AS abstime);
>
> or
>
> SELECT int4field::abstime;
http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
The types abstime and reltime are lower precision types which are used
internally. You are discouraged from using any of these types in new
applications and are encouraged to move any old ones over when
appropriate. Any or all of these internal types might disappear in a
future release.
Don't they?
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion
2002-08-13 03:14 Conversion Lonh SENG <slonh@camgsm.com.kh>
2002-08-13 03:32 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-13 07:03 ` Re: Conversion Alexander M. Pravking <fduch@antar.bryansk.ru>
@ 2002-08-13 07:14 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-13 07:37 ` Re: Conversion Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 1 reply; 10+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-13 07:14 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: Lonh SENG <slonh@camgsm.com.kh>; pgsql-sql
> On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote:
> > Is the int4 a UNIX epoch? ie. seconds since 1970?
> >
> > If so, then this will generally work:
> >
> > SELECT CAST(int4field AS abstime);
> >
> > or
> >
> > SELECT int4field::abstime;
>
> http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
>
> The types abstime and reltime are lower precision types which are used
> internally. You are discouraged from using any of these types in new
> applications and are encouraged to move any old ones over when
> appropriate. Any or all of these internal types might disappear in a
> future release.
Yes, but in absence of:
SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234');
(Hint Hint Thomas!!!)
It's all he can do. I suggest using the syntax above to convert his integer
column to a timestamp column.
Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion
2002-08-13 03:14 Conversion Lonh SENG <slonh@camgsm.com.kh>
2002-08-13 03:32 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-13 07:03 ` Re: Conversion Alexander M. Pravking <fduch@antar.bryansk.ru>
2002-08-13 07:14 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-13 07:37 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 0 replies; 10+ messages in thread
From: Alexander M. Pravking @ 2002-08-13 07:37 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Lonh SENG <slonh@camgsm.com.kh>; pgsql-sql
On Tue, Aug 13, 2002 at 03:14:38PM +0800, Christopher Kings-Lynne wrote:
> > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
> >
> > The types abstime and reltime are lower precision types which are used
> > internally. You are discouraged from using any of these types in new
> > applications and are encouraged to move any old ones over when
> > appropriate. Any or all of these internal types might disappear in a
> > future release.
>
> Yes, but in absence of:
>
> SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234');
Sounds nice :)
> (Hint Hint Thomas!!!)
>
> It's all he can do. I suggest using the syntax above to convert his integer
> column to a timestamp column.
Sure. I use the same. But I don't like it because of that caution :(
The other way is
SELECT 'epoch'::timestamp + (int4field::text || 's')::interval,
but it's much much slower... And it seems not to handle timestamps
after 2038-01-19.
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-08-13 07:37 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-26 02:32 conversion Ken Kline <ken@oldbs.com>
2001-02-26 04:11 ` Ken Kline <ken@oldbs.com>
2001-02-26 06:51 ` Christopher Sawtell <csawtell@xtra.co.nz>
2001-02-26 16:46 ` Ken Kline <ken@oldbs.com>
2001-02-26 17:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-13 03:14 Conversion Lonh SENG <slonh@camgsm.com.kh>
2002-08-13 03:32 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-13 07:03 ` Re: Conversion Alexander M. Pravking <fduch@antar.bryansk.ru>
2002-08-13 07:14 ` Re: Conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-13 07:37 ` Re: Conversion Alexander M. Pravking <fduch@antar.bryansk.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