This is my sql query for create view in Postgresql. But I need to query it from SqlAlchemy
CREATE OR REPLACE VIEW public.my_view
AS
SELECT m.id AS mt_dump_model_csv_id,
m.geom,
m."time",
r.id AS rf_data_type_id,
r.code,
d.id AS dt_dump_model_data_id,
d.fk_data_type,
d.fk_dum_model_csv,
d.value
FROM mt_dump_model_csv m
JOIN dt_dump_model_data d ON m.id = d.fk_dum_model_csv
JOIN rf_data_type r ON d.fk_data_type = r.id
ORDER BY m."time";
I also tried the below format of query. But it didn't work.
metadata = MetaData()
view = Table('my_view', metadata)
definition = text("SELECT m.id AS mt_dump_model_csv_id,m.geom,m.time,r.id AS rf_data_type_id,r.code,d.id AS dt_dump_model_data_id,d.fk_data_type,d.fk_dum_model_csv,d.value FROM mt_dump_model_csv m JOIN dt_dump_model_data d ON m.id = d.fk_dum_model_csv JOIN rf_data_type r ON d.fk_data_type = r.id ORDER BY m.time;")
create_view = CreateView(view, definition, or_replace=True)
engine.execute(create_view)
I got the following error:
column r.id does not exist
LINE 2: ...model_csv JOIN rf_data_type r ON d.fk_data_type = r.id ORDER...
^
HINT: Perhaps you meant to reference the column "m.id" or the column "d.id".