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 will occur
if i run this query in other database will work fine
I tested this on pg 11.1 , pg 10.3, pg 9.6
PS:Sorry if this message is duplicated i canceled the previous message by mistake by clicking on link
Le jeu. 13 juin 2019 à 17:33, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
