Re: how to retrieve table definition - Mailing list pgsql-novice

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@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Raimon Fernandez
Sent: Tuesday, May 15, 2007 7:29 AM
To: pgsql-novice@postgresql.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