Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table fake_receivers(id varchar(11));

insert into fake_receivers(id) values 
('VR2W-110528'),
('VR2W-113640'),
('VR2W-113640'),
('VR2W-110528'),
('VR2W-110154'),
('VR2W-127942'),
('VR2W-113640'),
('VR2W-110528'),
('VR2W-110154'),
('VR2W-110528');
10 rows affected

select cast(split_part(id, '-', 2) as integer) from fake_receivers; -- this works fine, seemingly selects integers


split_part


110528

113640

113640

110528

110154

127942

113640

110528

110154

110528


create table fake_stations(receiver_sn integer, station varchar);

insert into fake_stations values 
('110528', 'Baff01-01'),
('113640', 'Baff02-02'),
('110154', 'Baff03-01'),
('127942', 'Baff05-01');

select * from fake_stations; -- first column are integers
4 rows affected



receiver_sn
station


110528
Baff01-01

113640
Baff02-02

110154
Baff03-01

127942
Baff05-01


create materialized view test as (
	select cast(split_part(id, '-', 2) as integer), station
		from fake_receivers 
		inner join fake_stations
		on split_part = fake_stations.receiver_sn -- not recognizing split_part cast as integer! 
);
ERROR:  column "split_part" does not exist
LINE 5:   on split_part = fake_stations.receiver_sn -- not recognizi...
             ^


select * from test;
ERROR:  relation "test" does not exist
LINE 1: select * from test;
                      ^

receiver_sn	station
110528	Baff01-01
113640	Baff02-02
110154	Baff03-01
127942	Baff05-01