Re: how to retrieve table definition

Raimon

Try this:

SELECT relhasindex FROM pg_class WHERE relname = 'your-table-name'

Returns a bool value...
This is a somewhat related example which may be educational.

http://openacs.org/xowiki/cond_create_index

Mike

-----Original Message-----
From: Raimon Fernandez [mailto:coder(at)montx(dot)com]
Sent: Tuesday, May 15, 2007 11:22 AM
To: Programmer
Subject: Re: [NOVICE] how to retrieve table definition

Hi,

yes, this works !

thanks,

I hope now I can find where the index and wich indexs are and if the
table is WITH OID or WITHOU ID, as I can get it from PostgreSQL Navicat,
so that info must reside in one place or maybe is a compound of various
selects ...

thanks again!

raimon

On 15/05/2007, at 16:53, Programmer wrote:

> This works fine:
>
> SELECT COLUMN_NAME,
> CHARACTER_MAXIMUM_LENGTH,
> DATA_TYPE
> FROM information_schema.columns
> WHERE TABLE_NAME =
>
> Hope it helps
>
> Mike
>
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Raimon
> Fernandez
> Sent: Tuesday, May 15, 2007 7:29 AM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: [NOVICE] how to retrieve table definition
>
> Hi,
>
> In sqlite with SELECT * FROM sqlite_master WHERE table='a' I can
> obtain the complete CREATE TABLE ... commands,
>
>
> Wich is the equivalent in PostgeSQL ? I've been trying
> information_schema.tables without success ...
>
>
> thanks!
>
>
> raimon fernandez
>
>
>
> for example:
>
> for table 'comptes' I want to retrieve this:
>
> create table public.comptes( "codi_empresa" char(2) not null ,
> "codi_compte" varchar not null , "comptes" varchar not null , "tipus"
> char(2) not null , "clau_compte" char(13) not null , "saldo_deure"
> numeric(12,2) not null , "saldo_haver" numeric(12,2) not null ,
> "nivell"
> int2 not null , "saldo" numeric(12,2) not null , "ultim_moviment" date

> not null , "codi_437" int2 not null
> )
> WITH OIDS;
> ALTER table "public"."comptes" OWNER TO "postgres"; ALTER table
> "public"."comptes" SET WITHOUT CLUSTER; alter table "public"."comptes"
> add primary key(clau_compte); create index "clau_compte1" on
> "public"."comptes" using btree
> (clau_compte) ;
> create index "comptes1" on "public"."comptes" using btree(comptes) ;
> create index "nivell" on "public"."comptes" using btree(nivell) ;
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>