PostgreSQL casting data

Question

I have a table called temperature containing 5 columns : centre date tmpmax tmpmin rrquot, date tmpmax tmpmin rrquotare stored as character varying and I need to convert them to double precision in order to manipulate them to calculate some statistics. I used the following SQL command :

ALTER TABLE public.temperature
    ALTER COLUMN "TMPMAX" TYPE double precision
    USING cast("TMPMAX" AS double precision)

But it didn't work for the whole table because there are some cells that contains '' (nothing).

Concerning the date column, is there a way to cast it into date type (data are stored in this format : YYYYMMDD ex. 20030122) with YYYY-MM-DD format ex. 1968-01-13.

Please help in resolving those two problems.

I did not get why would you convert column you aim to make double precision to date and how it will help fighting empty values, so I suggested the way to fight empty values — Vao Tsun
– Vao Tsun, Commented May 4, 2017 at 13:35
I agree with Vao Tsun. If that is actually a date you should convert it to a DATE not a double — user330315
– user330315, Commented May 4, 2017 at 14:07

Vao Tsun · Accepted Answer · 2017-05-04 13:33:37Z

1

you can use condition in USING, like here:

t=# create table s106 (i text);
CREATE TABLE
t=# insert into s106 values ('1'),('');
INSERT 0 2
t=# alter table s106 alter COLUMN i type double precision
t-#     USING cast(i AS double precision);
ERROR:  invalid input syntax for type double precision: ""

conditional:

t=# alter table s106 alter COLUMN i type double precision
    USING cast(case when length(i) < 1 then null else i end AS double precision);
ALTER TABLE

answered May 4, 2017 at 13:33

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

or cast(nullif(trim(i), '') as double precision)

Vao Tsun Over a year ago

yes - totally agree for my example. I was not sure If op wants to make them NULL, so I suggested full case to be clear and flexible

Oto Shavadze · Accepted Answer · 2017-05-04 13:53:51Z

1

About date type, if I correctly understand, you have dates stored in character varying type field, like this: 20030122, and you need cast values to date type, (values become like this: 2003-01-22)

If so, you can try:

ALTER TABLE  temperature
ALTER COLUMN "date" TYPE date
USING  cast("date" as date)

answered May 4, 2017 at 13:53

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Collectives™ on Stack Overflow

PostgreSQL casting data

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related