pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtable names seem identical
4+ messages / 3 participants
[nested] [flat]
* table names seem identical
@ 2012-03-07 15:39 Ray Stell <stellr@cns.vt.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Ray Stell @ 2012-03-07 15:39 UTC (permalink / raw)
To: pgsql-admin
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: table names seem identical
@ 2012-03-07 17:41 Walter Hurry <walterhurry@lavabit.com>
parent: Ray Stell <stellr@cns.vt.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Walter Hurry @ 2012-03-07 17:41 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: table names seem identical
@ 2012-03-07 18:26 Ray Stell <stellr@cns.vt.edu>
parent: Walter Hurry <walterhurry@lavabit.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ray Stell @ 2012-03-07 18:26 UTC (permalink / raw)
To: Walter Hurry <walterhurry@lavabit.com>; +Cc: pgsql-admin
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: table names seem identical
@ 2012-03-08 06:08 David Kerr <dmk@mr-paradox.net>
parent: Ray Stell <stellr@cns.vt.edu>
1 sibling, 0 replies; 4+ messages in thread
From: David Kerr @ 2012-03-08 06:08 UTC (permalink / raw)
To: 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-03-08 06:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-07 15:39 table names seem identical Ray Stell <stellr@cns.vt.edu>
2012-03-07 17:41 ` Walter Hurry <walterhurry@lavabit.com>
2012-03-07 18:26 ` Ray Stell <stellr@cns.vt.edu>
2012-03-08 06:08 ` David Kerr <dmk@mr-paradox.net>
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