Обсуждение: sql syntax to replace desc

Dear all,

I need to see my table structure complete with it's constraint (at least primary key, foreign key, not null, unique)
In oracle or mysql, I usually use desc table_name to achieve such result
But I find from pgsql-doc that desc isn't implemented.

Does anyone know some sql syntax to do that?

I'm not talking about \d option at psql console since I need that data
to be called from programming language such as PHP and Ruby

Thank you
Regards
Hendra

On Fri, Jan 30, 2009 at 6:20 PM, hendra kusuma <penguinroad@gmail.com> wrote:

> I'm not talking about \d option at psql console since I need that data
> to be called from programming language such as PHP and Ruby

Here is a way to see what \d is doing:

richard@dell-desktop:~$ psql -E -U richard

richard=> \d animals
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(animals)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '16525'
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16525' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = '16525' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
**************************

********* QUERY **********
SELECT r.conname, pg_catalog.pg_get_constraintdef(r.oid, true)
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16525' AND r.contype = 'c' ORDER BY 1
**************************

********* QUERY **********
SELECT c.oid::regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
'16525' ORDER BY inhseqno ASC
**************************

             Table "public.animals"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 animalname | character varying(20) | not null
 animaltype | character varying(20) | not null
 weight     | numeric(6,2)          |
Indexes:
    "animals_pkey" PRIMARY KEY, btree (animalname, animaltype)
    "animals_animalname_key" UNIQUE, btree (animalname)
Check constraints:
    "animals_animaltype_check" CHECK (animaltype::text = ANY
(ARRAY['BEAR'::character varying, 'WOLF'::character varying,
'COUGAR'::character varying, 'SHEEP'::character varying,
'DEER'::character varying]::text[]))

richard=>


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

information_schema.table_name contains the tables information..make sure yuo select type BASE TABLE
and make sure the schema is not from either catalog or information_schema e.g.

SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')

HTH
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

---

Date: Sat, 31 Jan 2009 09:20:27 +0700
Subject: [GENERAL] sql syntax to replace desc
From: penguinroad@gmail.com
To: pgsql-general@postgresql.org

Dear all,

I need to see my table structure complete with it's constraint (at least primary key, foreign key, not null, unique)
In oracle or mysql, I usually use desc table_name to achieve such result
But I find from pgsql-doc that desc isn't implemented.

Does anyone know some sql syntax to do that?

I'm not talking about \d option at psql console since I need that data
to be called from programming language such as PHP and Ruby

Thank you
Regards
Hendra

---

Windows Live™ Hotmail®…more than just e-mail. See how it works.