Обсуждение: bug regclass::oid
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 ;
i have question how use join between information_schema.columns and
pg_attribute ? thanks
regards
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 ;
i have question how use join between information_schema.columns and
pg_attribute ? thanks
regards
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@aklaver.com
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.klaver@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.klaver@aklaver.com
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@aklaver.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_namelike '% %'; ╔══════════════╤═════════════╤═════════════╗ ║ 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@hjp.at | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Вложения
"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
I wrote: > ... 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". Sigh, that should be "between pg_class and pg_attribute" of course. I'm really only firing on one cylinder today :-( regards, tom lane
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@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.klaver@aklaver.com
On 6/17/19 1:58 AM, 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 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 > The previous message came through, see the replies. The basic issue is that a table name with a space in it will need to be quoted. So use quote_ident() per Peter and Tom's suggestions. -- Adrian Klaver adrian.klaver@aklaver.com