Hi, I want to query the underlying information_schema for my postgres database(s). This doesnt work diretly as Dremio doesn’t actually list the information_schema. All good, makes sense but I created a “pgschema” database and then created a view “v_pg_tables” on the underlying “information_schema.tables” table. Once Dremio refreshed the source I got the listing for the view but on trying to run a query I get “Selected Table has no columns”?
Build : 3.2.8-201907180222520740-08f2b54
Edition : Community Edition
Restarted Dremio but it didnt help.
This is very odd indeed. FYI I am using the same account in pgadmin and dremio . I have now created another view as below … this works fine in pgadmin but only brings back the desciption column filled with the table_schema contents. I suspect that the other column names are reserved in Dremio for some reason? alias is next
FROM pg_statio_all_tables st
JOIN pg_description pgd ON pgd.objoid = st.relid
JOIN information_schema.columns c ON pgd.objsubid = c.ordinal_position::integer AND c.table_schema::name = st.schemaname AND c.table_name::name = st.relname;
Ok, so Aliasing the column names didn’t work either. Any help would be great. Looks like it has something to do with the information_schema though
Also created a materialised view in Posgtres. MV’s dont seem to show up either.
No response at all on this team Dremio? I thought that the issue would raise some quality eyebrows especially where the wrong data is being displayed in the wrong column in some instances.
Kindly provide us the profile for the first failure