"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2019-06-16 18:03:02 +0200, John Mikel wrote:
>> 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
> You have to quote the table name [ with quote_ident ]
Note that that's still unreliable, because it's not considering the
possibility of duplicate table names in different schemas. You could
do something like
where
(quote_ident(a.table_schema) || '.' || quote_ident(a.table_name))::regclass = c.attrelid
If that seems awfully brute-force, you're right, but I think it's
self-inflicted damage from trying to mix two different levels of
abstraction -- namely, the information_schema and the underlying
native PG catalogs. I'd suggest recasting this as a join between
pg_catalog and pg_attribute, which would make the join condition
just "where c.oid = a.attrelid".
regards, tom lane