casting LEFT() from string to integer

Question

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

• user1822: Please edit your question and add the exact error message

Answer 1

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.

• Spatial Digger: cast AS or :: should not make the difference
• Spatial Digger: There was a data error, fixing the data makes this work
• Vérace: Hmm... yes, puzzling - but the error ERROR: invalid input syntax for integer: "13/1" SQL state: 22P02 suggests perhaps that there's a date in the format '13/1/XXXX' in there somewhere? Not really sure why. I do think that my answer is more "PostgreSQL-esqe" (c.f. Pythonesque).