From: David Kerr <dmk@mr-paradox.net>
To: pgsql-admin@postgresql.org
Subject: Re: table names seem identical
Date: Wed, 07 Mar 2012 22:08:09 -0800
Message-ID: <4F584CC9.1040508@mr-paradox.net> (raw)
In-Reply-To: <20120307153948.GA24033@cns.vt.edu>
References: <20120307153948.GA24033@cns.vt.edu>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dmk@mr-paradox.net
Subject: Re: table names seem identical
In-Reply-To: <4F584CC9.1040508@mr-paradox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox