Re: table names seem identical

On 03/07/2012 07:39 AM, Ray Stell wrote:
> how I can differentiate these:
>
> oamp=# \z public.c3*
> Access privileges
> Schema | Name | Type | Access privileges | Column access privileges
> --------+-----------------------+-------+-------------------+--------------------------
> public | c3p0_connection_test | table | |
> public | c3p0_connection_test | table | |
> (2 rows)
>
> oamp=# select * from pg_tables where tablename = 'c3p0_connection_test';
> schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
> ------------+----------------------+------------+------------+------------+----------+-------------
> public | c3p0_connection_test | admin | | f | f | f
> (1 row)
>
> oamp=# select version();
> version
> ------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 32-bit
> (1 row)
>

do you really want to differentiate or are you just pointing out that
it's difficult to tell via \z?

because if you really need to know then you can do

> psql -E temp

temp=# \z test*
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl,
E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column access privileges"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(test.*)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**************************

Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------+-------+-------------------+--------------------------
public | test | table | |
public | test | table | |
(2 rows)

Now you have the query, so alter it to:
SELECT n.nspname as "Schema",
* 'x'||c.relname||'x' as "Name",
* CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl,
E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column access privileges"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(test.*)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;

and you'll get:
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
public | xtestx | table | |
public | xtest x | table | |
(2 rows)