Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id B9BAB119C292 for ; Thu, 8 Mar 2012 02:08:12 -0400 (AST) Received: from vexbert.mr-paradox.net ([2001:470:b:28:f::1]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S5WWG-0003RV-21 for pgsql-admin@postgresql.org; Thu, 08 Mar 2012 06:08:11 +0000 Received: from [192.168.200.111] (c-50-137-42-222.hsd1.or.comcast.net [50.137.42.222]) (Authenticated sender: dmk) by vexbert.mr-paradox.net (Postfix) with ESMTP id 140DC84631 for ; Thu, 8 Mar 2012 01:07:53 -0500 (EST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.96 at vexbert.mr-paradox.net Message-ID: <4F584CC9.1040508@mr-paradox.net> Date: Wed, 07 Mar 2012 22:08:09 -0800 From: David Kerr User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: table names seem identical References: <20120307153948.GA24033@cns.vt.edu> In-Reply-To: <20120307153948.GA24033@cns.vt.edu> Content-Type: multipart/alternative; boundary="------------010402070401020709030507" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/35 X-Sequence-Number: 39223 This is a multi-part message in MIME format. --------------010402070401020709030507 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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) --------------010402070401020709030507 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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)


--------------010402070401020709030507--