Postgres Pro
Downloads
Home   >   mailing lists

Thread: table names seem identical

table names seem identical

From
Ray Stell
Date:
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)

Re: table names seem identical

From
Walter Hurry
Date:
On Wed, 07 Mar 2012 10:39:48 -0500, 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)

misc=# create table test1(flag varchar(1));
CREATE TABLE
misc=# create table "test1 "(flag varchar(1));
CREATE TABLE

Re: table names seem identical

From
Ray Stell
Date:
On Wed, Mar 07, 2012 at 05:41:41PM +0000, Walter Hurry wrote:
> On Wed, 07 Mar 2012 10:39:48 -0500, Ray Stell wrote:
>
> misc=# create table test1(flag varchar(1));
> CREATE TABLE
> misc=# create table "test1 "(flag varchar(1));
> CREATE TABLE



yeah, that is the case, the \z output is truncated it seems:

 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 * 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)

Re: table names seem identical

From
David Kerr
Date:
On 03/07/2012 07:39 AM, Ray Stell wrote: 
how I can differentiate these:

oamp=# \z public.c3*                                  Access privilegesSchema |         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)