Skip site navigation (1) Skip section navigation (2)

Re: how to retrieve table definition

From: "Programmer" <programmer(at)paradigm-corp(dot)com>
To: "Raimon Fernandez" <coder(at)montx(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: how to retrieve table definition
Date: 2007-05-15 16:59:56
Message-ID: 611B913B025B1B4494E40781B4F6A61C01E52BF6@SERVER.paradigm-corp.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>



pgsql-novice by date

Next:From: Gregory ThomsonDate: 2007-05-15 17:54:04
Subject: PostgreSQL audiobooks and/or podcasts
Previous:From: ProgrammerDate: 2007-05-15 14:53:23
Subject: Re: how to retrieve table definition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group