Skip site navigation (1) Skip section navigation (2)

Re: table names seem identical

From: David Kerr <dmk(at)mr-paradox(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: table names seem identical
Date: 2012-03-08 06:08:09
Message-ID: 4F584CC9.1040508@mr-paradox.net (view raw or flat)
Thread:
Lists: pgsql-admin
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)


In response to

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2012-03-08 17:06:02
Subject: Re: Postgres server crashing unexpectedly.
Previous:From: umashankar narayananDate: 2012-03-07 21:59:20
Subject: Postgres server crashing unexpectedly.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group