From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: bug regclass::oid |
Date: | 2019-06-16 18:12:50 |
Message-ID: | 20190616181250.x6l3n7bq2j5z4onq@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com> 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(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-06-16 18:32:00 | Re: bug regclass::oid |
Previous Message | Stephen Frost | 2019-06-16 17:37:02 | Re: checkpoints taking much longer than expected |