hi again here is my query *select A.table_name as "table_name",A.domain_name as "domain",* * format_type(c.atttypid, c.atttypmod) AS data_type ,A.column_name as "column_name",* * A.is_nullable as "nullable",A.column_default as "default"* * from information_schema.columns A inner join pg_attribute c on a.table_name::regclass::oid=c.attrelid* * where a.table_schema in (select current_schema()) and a.column_name =c.attname ;*
if i run this query in any database contain at least one table with space in their name , an error occurred if i run this query in other database work fine I tested this on pg 11.1 , pg 10.3, pg 9.6 Le jeu. 13 juin 2019 à 17:33, Adrian Klaver <adrian.kla...@aklaver.com> a écrit : > On 6/13/19 8:14 AM, John Mikel wrote: > > * hi , > > i am here to tell you that this test in query is not working when the > > table name in the database or schema name contain space > > a.table_name::regclass=b.attrelid > > > > a is information_schema.columns > > b is pg_attribute > > > > trying this in two different databases the first database contain table > > with space in his name (Problem when running query) > > the second no ( work fine) > > > > the same problme if you get Oid from schema name. > > SELECT 'public'::regnamespace::oid; work ; > > create schema " this is test" ; > > SELECT 'this is test'::regnamespace::oid; not working ; > > Try: > > SELECT '"this is test"'::regnamespace::oid; not working ; > > Same for table name. As example: > > select '"space table"'::regclass; > regclass > --------------- > "space table" > (1 row) > > > > > i have question how use join between information_schema.columns and > > pg_attribute ? thanks > > > > regards* > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
