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.
Any suggestions
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
SELECT c.table_schema,
c.table_name,
c.column_name,
pgd.description
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.
@Ben_Spencer
Kindly provide us the profile for the first failure
Thanks
@balaji.ramaswamy