Postgresql – casting LEFT() from string to integer

postgresql

I have a date range column e.g. 1960-1970 in a table, I'm splitting these and copying them to new columns, I need to cast the string to an integer, the following code returns a generic syntax error, any ideas where I'm going wrong? 

INSERT INTO rhp_tvp.time (
  visit_id_tmp, 
  tvp_id_tmp, 
  time_start, 
  time_end)

SELECT 
  v.visit_id, 
  v.tvp_id, 
  cast(LEFT(date_range, 4)AS int8), 
  cast(RIGHT(date_range, 4) AS int8)
FROM tvp.visit v 
WHERE  date_format = 'DMY' AND date_range NOT LIKE 'Pre%' OR date_range IS NOT NULL;

ERROR: invalid input syntax for integer: "13/1"
SQL state: 22P02

Best Answer

I think you want something like this:

SELECT 
  v.visit_id, 
  v.tvp_id, 
  LEFT(date_range, 4)::INT, 
  RIGHT(date_range, 4)::INT
FROM tvp.visit v 
WHERE  date_format = 'DMY' AND date_range NOT LIKE 'Pre%';

To answer your question I did the following:

Created a table:

CREATE TABLE my_range(the_range VARCHAR(9)); -- just the field of interest!

Some sample values:

INSERT INTO my_range VALUES ('1969-1986');
INSERT INTO my_range VALUES ('1932-1987');
INSERT INTO my_range VALUES ('1956-1999');

Query:

SELECT LEFT(the_range, 4)::INT, RIGHT(the_range, 4)::INT FROM my_range;

Result:

Left Right
1969 1986
1932 1987
1956 1999

See the dbfiddle here.

You can check that they are now integers by running:

SELECT LEFT(the_range, 4)::INT + 20, RIGHT(the_range, 4)::INT - 20 FROM my_range;

And you can see that addition and subtraction are indeed performed on the new values in the fiddle.

Related Question