On 2019-06-16 18:03:02 +0200, John Mikel wrote: > 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 Note that Adrian had the name enclosed in double quotes: > Le jeu. 13 juin 2019 à 17:33, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> a > écrit : > >> Try: >> >> SELECT '"this is test"'::regnamespace::oid; not working ; >> >> Same for table name. As example: >> >> select '"space table"'::regclass; You don't do that you just try to use a.table_name as is. But 'space table'::regclass doesn't work. You have to quote the table name: hjp=> select table_schema, table_name::regclass, column_name from information_schema.columns where table_name like '% %'; ERROR: invalid name syntax Time: 5.794 ms hjp=> select table_schema, quote_ident(table_name)::regclass, column_name from information_schema.columns where table_name like '% %'; ╔══════════════╤═════════════╤═════════════╗ ║ table_schema │ quote_ident │ column_name ║ ╟──────────────┼─────────────┼─────────────╢ ║ public │ "foo bar" │ id ║ ╚══════════════╧═════════════╧═════════════╝ (1 row) hp -- _ | Peter J. Holzer | we build much bigger, better disasters now |_|_) | | because we have much more sophisticated | | | hjp@xxxxxx | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment:
signature.asc
Description: PGP signature
