From shahaf@redfin.com Wed Jun 3 19:57:23 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A78A32E02FC; Tue, 4 Mar 2008 16:34:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 82833-02; Tue, 4 Mar 2008 16:34:03 -0400 (AST) X-Greylist: delayed 00:29:51.574284 by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outbound7-dub-R.bigfish.com (outbound-dub.frontbridge.com [213.199.154.16]) by postgresql.org (Postfix) with ESMTP id 06FBB2E02D0; Tue, 4 Mar 2008 16:34:05 -0400 (AST) Received: from outbound7-dub.bigfish.com (localhost.localdomain [127.0.0.1]) by outbound7-dub-R.bigfish.com (Postfix) with ESMTP id 798525D24CB; Tue, 4 Mar 2008 20:03:57 +0000 (UTC) Received: from mail16-dub-R.bigfish.com (mail16-dub [10.5.7.13]) by outbound7-dub.bigfish.com (Postfix) with ESMTP id 544C4898053; Tue, 4 Mar 2008 20:03:57 +0000 (UTC) Received: from mail16-dub (localhost.localdomain [127.0.0.1]) by mail16-dub-R.bigfish.com (Postfix) with ESMTP id 1F29064832A; Tue, 4 Mar 2008 20:03:57 +0000 (UTC) X-BigFish: VP X-MS-Exchange-Organization-Antispam-Report: OrigIP: 207.170.241.12; Service: EHS Received: by mail16-dub (MessageSwitch) id 1204661033103733_9580; Tue, 4 Mar 2008 20:03:53 +0000 (UCT) Received: from mail-1.rf.lan (207-170-241-12.static.twtelecom.net [207.170.241.12]) by mail16-dub.bigfish.com (Postfix) with ESMTP id 85C069D80E6; Tue, 4 Mar 2008 20:03:49 +0000 (UTC) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C87E32.AC0FC1BC" Subject: Documenting a DB schema Date: Tue, 4 Mar 2008 12:02:27 -0800 Message-ID: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Documenting a DB schema Thread-Index: Ach+MqvKpCylwa1/RpiTYtOd0cSs4A== From: "Shahaf Abileah" To: , , , , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/17 X-Sequence-Number: 28500 This is a multi-part message in MIME format. ------_=_NextPart_001_01C87E32.AC0FC1BC Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I'm looking for a systematic way to document the schema for the database behind our website (www.redfin.com ), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recommendations? =20 It would be great if the documentation could be kept as close to the code as possible - that way we stand a chance of keeping it up to date. So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability: =20 create table table_with_comments(a int comment 'this is column a...'); =20 (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) =20 However, Postgres doesn't support the "comment" keyword. Is there an alternative? =20 Thanks, =20 --S =20 Shahaf Abileah | Lead Software Developer=20 shahaf@redfin.com | tel: 206.859.2869 | fax: 877.733.3469 Redfin Corporation 710 2nd Ave Suite 600 Seattle, WA 98104 =20 ------_=_NextPart_001_01C87E32.AC0FC1BC Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I’m looking for a systematic way to document = the schema for the database behind our website (www.redfin.com), so that the = developers using this database have a better idea what all the tables and columns = mean and what data to expect. Any = recommendations?

It would be great if the documentation could be kept = as close to the code as possible – that way we stand a chance of = keeping it up to date. So, in the same way that Java docs go right there on = top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability:

= create table table_with_comments(a int comment 'this is column = a...');

(see http:/= /dev.mysql.com/doc/refman/5.0/en/create-table.html)=

However, Postgres doesn’t support the “comment” keyword. Is there an = alternative?

Thanks,

--S

Shahaf Abileah | Lead Software Developer

shahaf@redfin.com = | tel: 206.859.2869 | fax: 877.733.3469

Redfin Corporation
710 2nd = Ave
Suite = 600
Seattle, WA 98104

------_=_NextPart_001_01C87E32.AC0FC1BC-- From me@oisinglynn.com Wed Jun 3 19:57:23 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2823F2E0340; Tue, 4 Mar 2008 16:58:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 08666-06; Tue, 4 Mar 2008 16:58:01 -0400 (AST) X-Greylist: delayed 00:12:09.619047 by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from homsrv01.homisco.local (mail.homisco.com [64.213.65.19]) by postgresql.org (Postfix) with ESMTP id 8BDAF2E034D; Tue, 4 Mar 2008 16:58:01 -0400 (AST) Received: from [192.168.10.102] ([192.168.10.102]) by homsrv01.homisco.local with Microsoft SMTPSVC(5.0.2195.6713); Tue, 4 Mar 2008 15:45:51 -0500 Message-ID: <47CDB4FF.4000408@oisinglynn.com> Date: Tue, 04 Mar 2008 15:45:51 -0500 From: Oisin Glynn User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: Shahaf Abileah CC: pgsql-admin@postgresql.org, pgsql-general@postgresql.org Subject: Re: [GENERAL] Documenting a DB schema References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 04 Mar 2008 20:45:51.0519 (UTC) FILETIME=[BBDD9AF0:01C87E38] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/19 X-Sequence-Number: 28502 Shahaf Abileah wrote: > > I’m looking for a systematic way to document the schema for the > database behind our website (www.redfin.com ), > so that the developers using this database have a better idea what all > the tables and columns mean and what data to expect. Any recommendations? > > It would be great if the documentation could be kept as close to the > code as possible – that way we stand a chance of keeping it up to > date. So, in the same way that Java docs go right there on top of the > class or method definitions, it would be great if I could attach my > comments to the table definitions. It looks like MySQL has that kind > of capability: > > create table table_with_comments(a int comment 'this is column a...'); > > (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) > > However, Postgres doesn’t support the “comment” keyword. Is there an > alternative? > > Thanks, > > --S > > *Shahaf Abileah *|* Lead Software Developer * > > shahaf@redfin.com | tel: 206.859.2869 | fax: 877.733.3469 > > Redfin Corporation > 710 2nd Ave > Suite 600 > Seattle, WA 98104 > Its probably best to try one list and see if you get a response rather than sending the same message to 3 lists. Comments are supported CREATE TABLE follow_me_destination ( mailbox_number character varying(10), -- Follow me users mailbox number. destination_number character varying(32), -- Follow me phone number. dest_id serial NOT NULL ) WITHOUT OIDS; ALTER TABLE follow_me_destination OWNER TO postgres; COMMENT ON TABLE follow_me_destination IS 'Stores follow me numbers for system users.'; COMMENT ON COLUMN follow_me_destination.mailbox_number IS 'Follow me users mailbox number.'; COMMENT ON COLUMN follow_me_destination.destination_number IS 'Follow me phone number.'; From emilu@encs.concordia.ca Wed Jun 3 19:57:23 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2F3AE2E01EB for ; Tue, 4 Mar 2008 16:58:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 90080-01-6 for ; Tue, 4 Mar 2008 16:58:43 -0400 (AST) X-Greylist: delayed 00:11:24.2081 by SQLgrey-1.7.5 Received: from perseverance.services.encs.concordia.ca (perseverance-96.encs.concordia.ca [132.205.96.94]) by postgresql.org (Postfix) with ESMTP id C093B2E2DBA for ; Tue, 4 Mar 2008 16:58:46 -0400 (AST) Received: from cacao.encs.concordia.ca (emilu@cacao.encs.concordia.ca [132.205.47.225]) by perseverance.services.encs.concordia.ca (envelope-from emilu@encs.concordia.ca) (8.13.7/8.13.7) with ESMTP id m24KlEua016670; Tue, 4 Mar 2008 15:47:14 -0500 Message-ID: <47CDB551.5010400@encs.concordia.ca> Date: Tue, 04 Mar 2008 15:47:13 -0500 From: Emi Lu Reply-To: emilu@encs.concordia.ca User-Agent: Thunderbird 2.0.0.6 (X11/20070728) MIME-Version: 1.0 To: Shahaf Abileah CC: pgsql-sql@postgresql.org Subject: Re: Documenting a DB schema References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.58 on perseverance.encs.concordia.ca at 2008/03/04 15:47:14 EST X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/11 X-Sequence-Number: 30339 Hi, > I'm looking for a systematic way to document the schema for the database > behind our website (www.redfin.com ), so that > the developers using this database have a better idea what all the > tables and columns mean and what data to expect. Any recommendations? I am using Case Studio to document DB structures. I think it is pretty good tool. http://www.casestudio.com/enu/default.aspx To browse DB objects only, try DbVisualizer: http://www.minq.se/products/dbvis/ - Ly From tdidomenico@avature.net Wed Jun 3 19:57:23 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BCA202E010E for ; Tue, 4 Mar 2008 17:15:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 16067-01 for ; Tue, 4 Mar 2008 17:15:35 -0400 (AST) X-Greylist: delayed 00:20:03.771349 by SQLgrey-1.7.5 Received: from mail3.avature.net (mail3.avature.net [4.78.160.175]) by postgresql.org (Postfix) with SMTP id A7F4F2E0343 for ; Tue, 4 Mar 2008 17:15:25 -0400 (AST) Received: from localhost (unknown [172.16.2.174]) by mail3.avature.net (Postfix) with ESMTP id 7A47C408008; Tue, 4 Mar 2008 15:53:58 -0500 (EST) X-Virus-Scanned: ClamAV at avature.net Received: from mail3.avature.net ([172.16.2.175]) by localhost (amavis.xcade.net [172.16.2.174]) (amavisd-new, port 10024) with ESMTP id OLZT1uLHiQHn; Tue, 4 Mar 2008 15:54:56 -0500 (EST) Received: from hegel.xcade.net (unknown [192.168.133.1]) by mail3.avature.net (Postfix) with SMTP id 3EE6C408007; Tue, 4 Mar 2008 15:53:43 -0500 (EST) Received: from [192.168.133.216] (unknown [192.168.133.216]) by hegel.xcade.net (Postfix) with ESMTP id E87FC320050; Tue, 4 Mar 2008 17:53:09 -0300 (ART) Message-ID: <47CDB71D.3000106@avature.net> Date: Tue, 04 Mar 2008 18:54:53 -0200 From: =?UTF-8?B?VG9tw6FzIERpIERvbcOpbmljbw==?= User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110) MIME-Version: 1.0 To: Shahaf Abileah , pgsql-general@postgresql.org Subject: Re: Documenting a DB schema References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> X-Enigmail-Version: 0.95.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/104 X-Sequence-Number: 129426 Check http://www.postgresql.org/docs/8.3/interactive/sql-comment.html Cheers! Shahaf Abileah wrote: > I=E2=80=99m looking for a systematic way to document the schema for the= database > behind our website (www.redfin.com ), so that > the developers using this database have a better idea what all the > tables and columns mean and what data to expect. Any recommendations? >=20 > =20 >=20 > It would be great if the documentation could be kept as close to the > code as possible =E2=80=93 that way we stand a chance of keeping it up = to date.=20 > So, in the same way that Java docs go right there on top of the class o= r > method definitions, it would be great if I could attach my comments to > the table definitions. It looks like MySQL has that kind of capability: >=20 > =20 >=20 > create table table_with_comments(a int comment 'this is > column a...'); >=20 > =20 >=20 > (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) >=20 > =20 >=20 > However, Postgres doesn=E2=80=99t support the =E2=80=9Ccomment=E2=80=9D= keyword. Is there an > alternative? >=20 > =20 >=20 > Thanks, >=20 > =20 >=20 > --S >=20 > =20 >=20 > *Shahaf Abileah *|* Lead Software Developer * >=20 > shahaf@redfin.com | tel: 206.859.2869 | fax: 877.733.3469 >=20 > Redfin Corporation > 710 2nd Ave > Suite 600 > Seattle, WA 98104 >=20 > =20 >=20 From michael.monnerie@it-management.at Wed Jun 3 19:57:23 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CF4142E02A9 for ; Tue, 4 Mar 2008 16:57:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 08795-02 for ; Tue, 4 Mar 2008 16:56:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198]) by postgresql.org (Postfix) with ESMTP id E5D6A2E0088 for ; Tue, 4 Mar 2008 16:56:55 -0400 (AST) Received: from mailsrv.i.zmi.at (85-125-129-125.neutorgasse.xdsl-line.inode.at [85.125.129.125]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "mailsrv.i.zmi.at", Issuer "mailsrv.i.zmi.at" (not verified)) by mailsrv1.zmi.at (Postfix) with ESMTP id 5DB6FC49 for ; Tue, 4 Mar 2008 21:56:52 +0100 (CET) Received: from saturn.i.zmi.at (saturn.i.zmi.at [10.0.0.2]) by mailsrv.i.zmi.at (Postfix) with ESMTP id BB5C2BF42 for ; Tue, 4 Mar 2008 21:56:22 +0100 (CET) From: Michael Monnerie Organization: it-management http://it-management.at To: pgsql-admin@postgresql.org Subject: Re: Documenting a DB schema Date: Tue, 4 Mar 2008 21:56:51 +0100 User-Agent: KMail/1.9.6 (enterprise 20070904.708012) References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart1870929.WUbVpWGqou"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200803042156.51832@zmi.at> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/18 X-Sequence-Number: 28501 --nextPart1870929.WUbVpWGqou Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Dienstag, 4. M=E4rz 2008 Shahaf Abileah wrote: > create table table_with_comments(a int comment 'this is > column a...'); > (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) > However, Postgres doesn't support the "comment" keyword. =A0Is there an > alternative? This sounds nice at first thought... but: you need to document tables=20 also, and relations between tables etc. And especially the complex=20 dependencies can't be documented that way ("if you insert this here,=20 look into table x and y and compare this with table z, blabla"). And I'd like to know how often such funny documentation can be found=20 when it's used, take your example: > create table table_with_comments(a int comment 'this is > column a...'); I've seen such documentation a lot - just bought a Nokia E65 mobile=20 phone, it's handbook has this kind of documentation printed in it...=20 worthless. But, BTW, does anybody have a good tool to show graphically the=20 relations between tables, and maybe even draw relations between tables=20 and create all necessary commands from this automatically? That would=20 be nice, along with documentation features... mfg zmi =2D-=20 // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4 --nextPart1870929.WUbVpWGqou Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4-svn0 (GNU/Linux) iD8DBQBHzbeTzhSR9xwSCbQRAi+nAJ47omm+KBHQc4tn79ocNdG3aMirRgCgxLbS 10ede0Iwvk0fF/Q/HLl1jgg= =LtAc -----END PGP SIGNATURE----- --nextPart1870929.WUbVpWGqou-- From ahodgson@simkin.ca Wed Jun 3 19:57:23 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3E3B82E034F for ; Tue, 4 Mar 2008 17:00:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10661-01 for ; Tue, 4 Mar 2008 17:00:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from skynet.simkin.ca (skynet.simkin.ca [72.51.27.117]) by postgresql.org (Postfix) with ESMTP id 240692E0328 for ; Tue, 4 Mar 2008 17:00:07 -0400 (AST) Received: from charon.medialogik.com (charon.medialogik.com [72.51.27.114]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by skynet.simkin.ca (Postfix) with ESMTP id 09FFF4A56 for ; Tue, 4 Mar 2008 13:00:04 -0800 (PST) From: Alan Hodgson Organization: Simkin Network Consulting To: pgsql-general@postgresql.org Subject: Re: Documenting a DB schema Date: Tue, 4 Mar 2008 13:00:50 -0800 User-Agent: KMail/1.9.6 (enterprise 0.20070907.709405) References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200803041300.50522@hal.medialogik.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/102 X-Sequence-Number: 129424 On Tuesday 04 March 2008, "Shahaf Abileah" wrote: > > However, Postgres doesn't support the "comment" keyword. Is there an > alternative? comment on table table_name is 'comment'; comment on column table.column_name is 'comment'; -- Alan From cww@denterprises.org Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 381AE2E2DB9 for ; Tue, 4 Mar 2008 17:07:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 13081-03 for ; Tue, 4 Mar 2008 17:07:16 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtpauth.rollernet.us (smtpauth.rollernet.us [208.11.75.5]) by postgresql.org (Postfix) with ESMTP id 132B72E14BF for ; Tue, 4 Mar 2008 17:07:16 -0400 (AST) Received: from smtpauth.rollernet.us (localhost.localdomain [127.0.0.1]) by smtpauth.rollernet.us (Postfix) with ESMTP id 0CFC1594062; Tue, 4 Mar 2008 13:07:13 -0800 (PST) Received: from iron.denterprises.org (ool-4350518d.dyn.optonline.net [67.80.81.141]) by smtpauth.rollernet.us (Postfix) with ESMTP; Tue, 4 Mar 2008 13:07:12 -0800 (PST) Received: from [127.0.0.1] (kell.iron.denterprises.org [192.168.171.80]) by iron.denterprises.org (Postfix) with ESMTP id C65A31676CB; Tue, 4 Mar 2008 16:07:11 -0500 (EST) Message-ID: <47CDB9D2.5070106@denterprises.org> Date: Tue, 04 Mar 2008 16:06:26 -0500 From: Colin Wetherbee User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: Shahaf Abileah CC: pgsql-general@postgresql.org Subject: Re: Documenting a DB schema References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Antivirus: avast! (VPS 080304-0, 03/04/2008), Outbound message X-Antivirus-Status: Clean X-Rollernet-Abuse: Processed by Roller Network Mail Services. Contact abuse@rollernet.us to report violations. Abuse policy: http://rollernet.us/abuse.php X-Rollernet-Submit: Submit ID 5e2f.47cdba00.b91c7.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/103 X-Sequence-Number: 129425 Shahaf Abileah wrote: > It would be great if the documentation could be kept as close to the > code as possible â€“ that way we stand a chance of keeping it up to date. > So, in the same way that Java docs go right there on top of the class or > method definitions, it would be great if I could attach my comments to > the table definitions. It looks like MySQL has that kind of capability: Please do not cross-post. One list is enough. PostgreSQL has that functionality, too. cww=# CREATE TABLE foo (a INTEGER, b INTEGER); CREATE TABLE cww=# COMMENT ON TABLE foo IS 'my comment'; COMMENT cww=# \d+ List of relations Schema | Name | Type | Owner | Description --------+------+-------+-------+------------- public | foo | table | cww | my comment (1 row) COMMENT is well-documented. http://www.postgresql.org/docs/8.3/static/sql-comment.html Colin From scrawford@pinpointresearch.com Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 015C12E0199; Tue, 4 Mar 2008 17:27:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 96794-09; Tue, 4 Mar 2008 17:27:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from polaris.pinpointresearch.com (66-7-238-130.static-ip.telepacific.net [66.7.238.130]) by postgresql.org (Postfix) with ESMTP id C6E452E0088; Tue, 4 Mar 2008 17:27:18 -0400 (AST) Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 5ABA7E00EC8A; Tue, 4 Mar 2008 13:27:16 -0800 (PST) Message-ID: <47CDBEB3.3010106@pinpointresearch.com> Date: Tue, 04 Mar 2008 13:27:15 -0800 From: Steve Crawford User-Agent: Thunderbird 2.0.0.12 (X11/20080227) MIME-Version: 1.0 To: Shahaf Abileah CC: pgsql-admin@postgresql.org, pgsql-docs@postgresql.org, pgsql-general@postgresql.org, psql-novice@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] Documenting a DB schema References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> Content-Type: multipart/alternative; boundary="------------020004090401080306070400" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/20 X-Sequence-Number: 28503 This is a multi-part message in MIME format. --------------020004090401080306070400 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Shahaf Abileah wrote: > > I'm looking for a systematic way to document the schema for the > database behind our website (www.redfin.com ), > so that the developers using this database have a better idea what all > the tables and columns mean and what data to expect. Any recommendations? > > > > It would be great if the documentation could be kept as close to the > code as possible -- that way we stand a chance of keeping it up to > date. So, in the same way that Java docs go right there on top of the > class or method definitions, it would be great if I could attach my > comments to the table definitions. It looks like MySQL has that kind > of capability: > > > > create table table_with_comments(a int comment 'this is > column a...'); > > > > (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) > > > > However, Postgres doesn't support the "comment" keyword. Is there an > alternative? > You mean like: COMMENT ON mytable IS 'This is my table. Mine, mine, mine'; You can also comment columns, databases, functions, schemas, domains, etc. Cheers, Steve --------------020004090401080306070400 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Shahaf Abileah wrote:

I’m looking for a systematic way to document the schema for the database behind our website (www.redfin.com), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recommendations?

It would be great if the documentation could be kept as close to the code as possible – that way we stand a chance of keeping it up to date. So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability:

create table table_with_comments(a int comment 'this is column a...');

(see http://dev.mysql.com/doc/refman/5.0/en/create-table.html)

However, Postgres doesn’t support the “comment” keyword. Is there an alternative?

You mean like:
COMMENT ON mytable IS 'This is my table. Mine, mine, mine';

You can also comment columns, databases, functions, schemas, domains, etc.

Cheers,
Steve

--------------020004090401080306070400-- From maryfran@demog.berkeley.edu Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3902D2E1891 for ; Tue, 4 Mar 2008 18:39:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44667-02 for ; Tue, 4 Mar 2008 18:39:02 -0400 (AST) X-Greylist: delayed 00:26:02.673811 by SQLgrey-1.7.5 Received: from nmx.DEMOG.Berkeley.EDU (DEMOG.Berkeley.EDU [128.32.93.50]) by postgresql.org (Postfix) with ESMTP id 58E012E181E for ; Tue, 4 Mar 2008 18:39:02 -0400 (AST) Received: from nmx.DEMOG.Berkeley.EDU (localhost.localdomain [127.0.0.1]) by nmx.DEMOG.Berkeley.EDU (Postfix) with ESMTP id B97F0167A6; Tue, 4 Mar 2008 14:12:57 -0800 (PST) Received: from generation.demog.berkeley.edu (generation.DEMOG.Berkeley.EDU [192.168.1.26]) (using SSLv3 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by nmx.DEMOG.Berkeley.EDU (Postfix) with ESMTP id 7E191167A0; Tue, 4 Mar 2008 14:12:57 -0800 (PST) Message-ID: <47CDC96E.7090209@demog.berkeley.edu> Date: Tue, 04 Mar 2008 14:13:02 -0800 From: Mary Anderson User-Agent: Thunderbird 2.0.0.4 (X11/20070604) MIME-Version: 1.0 To: Michael Monnerie CC: pgsql-admin@postgresql.org Subject: Re: Documenting a DB schema References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> <200803042156.51832@zmi.at> In-Reply-To: <200803042156.51832@zmi.at> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/23 X-Sequence-Number: 28506 MicroOlap (or MicrOlap) does a nice job of creating schema diagrams for postgres. I have had a little trouble with re-engineering databases that have had a lot of alterations, however. But their tech support is reasonable. Mary Michael Monnerie wrote: > On Dienstag, 4. März 2008 Shahaf Abileah wrote: >> create table table_with_comments(a int comment 'this is >> column a...'); >> (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) >> However, Postgres doesn't support the "comment" keyword. Is there an >> alternative? > > This sounds nice at first thought... but: you need to document tables > also, and relations between tables etc. And especially the complex > dependencies can't be documented that way ("if you insert this here, > look into table x and y and compare this with table z, blabla"). > > And I'd like to know how often such funny documentation can be found > when it's used, take your example: >> create table table_with_comments(a int comment 'this is >> column a...'); > I've seen such documentation a lot - just bought a Nokia E65 mobile > phone, it's handbook has this kind of documentation printed in it... > worthless. > > But, BTW, does anybody have a good tool to show graphically the > relations between tables, and maybe even draw relations between tables > and create all necessary commands from this automatically? That would > be nice, along with documentation features... > > mfg zmi From prof.flaviobrito@gmail.com Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1D09B2E00DD for ; Wed, 5 Mar 2008 09:10:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 02116-05 for ; Wed, 5 Mar 2008 09:10:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.175]) by postgresql.org (Postfix) with ESMTP id 7C50D2E00D6 for ; Wed, 5 Mar 2008 09:10:29 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 28so1617084wff.28 for ; Wed, 05 Mar 2008 05:10:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; bh=8TuZYtZcJzcGVwW3OUT5Gi6kd6o5aQzXkg3jXrYK/KM=; b=r8Y2rc35z2KjVHaFH7bwLQiDVzdn4hXZ2piEN8URq6zQS5Wku6t+VJ0X78GOHiaD5danS5xALEtJ0qXLhBDv+1BCcxPErjomouug7c2IADBo5GvkfCjw2LVTEemu4fuT3udyjN2XSKccX2Mig8YlUJ2ynRbGdhk7LYHimKniYCw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=MgV5Ok7RaxU4B8CAslEcQI21R0O3X3WixT+bWAPN8cD71zU7mf6QTck5bqL5ZMHCigx6ljxVC1j7UiJ/CTUko2YnkAF2dco3AjgyIBUoV9vfltVhPqVgzwifF7B6YS309t2DHHZ1I+b6cU/U0XMnCItaPxQVnreBqJkBrWJGkFY= Received: by 10.142.211.10 with SMTP id j10mr737367wfg.202.1204722627433; Wed, 05 Mar 2008 05:10:27 -0800 (PST) Received: by 10.142.148.16 with HTTP; Wed, 5 Mar 2008 05:10:27 -0800 (PST) Message-ID: <6a5e3a6f0803050510j11a19653h39564369e0427c6f@mail.gmail.com> Date: Wed, 5 Mar 2008 10:10:27 -0300 From: "=?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?=" Subject: Re: Documenting a DB schema Cc: pgsql-sql@postgresql.org In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_35927_11538548.1204722627449" References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.433 tagged_above=0 required=5 tests=AWL=-0.149, HTML_MESSAGE=0.001, MISSING_HEADERS=1.581 X-Spam-Level: * X-Archive-Number: 200803/15 X-Sequence-Number: 30343 ------=_Part_35927_11538548.1204722627449 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi You may try this. CREATE TYPE tabela_estrutura AS (esquema text, tabela text, campo text, tipo text, valor text, autoincremento boolean); ALTER TYPE tabela_estrutura OWNER TO postgres; CREATE OR REPLACE FUNCTION dados_tabela(character varying) RETURNS SETOF tabela_estrutura AS $BODY$ DECLARE r tabela_estrutura%ROWTYPE; rec RECORD; vTabela alias for $1; eSql TEXT; BEGIN eSql :=3D 'SELECT CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) , CAST(attrs.attname AS TEXT), CAST("Type" AS TEXT), CAST("Default" AS TEXT), attrs.attnotnull FROM (SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.relnamesp= ace WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel JOIN (SELECT a.attname, a.attrelid, pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type", (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid =3D a.attrelid AND d.adnum =3D a.attn= umAND a.atthasdef) as "Default", a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs ON (attrs.attrelid =3D rel.oid ) WHERE relname LIKE ''%' || vTabela || '%'' ORDER BY attrs.attnum'; FOR r IN EXECUTE eSql LOOP RETURN NEXT r; END LOOP; IF NOT FOUND THEN RAISE EXCEPTION 'Table not found', vTabela; END IF; RETURN; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION dados_tabela(character varying) OWNER TO postgres; 2008/3/4, Shahaf Abileah : > > I'm looking for a systematic way to document the schema for the database > behind our website (www.redfin.com), so that the developers using this > database have a better idea what all the tables and columns mean and what > data to expect. Any recommendations? > > > > It would be great if the documentation could be kept as close to the code > as possible =96 that way we stand a chance of keeping it up to date. So,= in > the same way that Java docs go right there on top of the class or method > definitions, it would be great if I could attach my comments to the table > definitions. It looks like MySQL has that kind of capability: > > > > create table table_with_comments(a int comment 'this is colum= n > a...'); > > > > (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) > > > > However, Postgres doesn't support the "comment" keyword. Is there an > alternative? > > > > Thanks, > > > > --S > > > > *Shahaf Abileah *|* Lead Software Developer * > > shahaf@redfin.com | tel: 206.859.2869 | fax: 877.733.3469 > > Redfin Corporation > 710 2nd Ave > Suite 600 > Seattle, WA 98104 > > > ------=_Part_35927_11538548.1204722627449 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi

You may try this.

CREATE TYPE tabela_estrutu= ra AS
   (esquema text,
    tabela text,
=     campo text,
    tipo text,
&n= bsp; valor text,
    autoincremento boolean);
AL= TER TYPE tabela_estrutura OWNER TO postgres;

CREATE OR REPLACE FUNCTION dados_tabela(character varying)
&= nbsp; RETURNS SETOF tabela_estrutura AS
$BODY$
DECLARE
= ;r tabela_estrutura%ROWTYPE;
rec RECORD;
vTabela alias fo= r $1;
eSql TEXT;

BEGIN
eSql :=3D 'SELECT
    &= nbsp; CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) ,
CAST= (attrs.attname AS TEXT), CAST("Type" AS TEXT),
CAST("Def= ault" AS TEXT), attrs.attnotnull
        FROM
    =            (SELECT c.oid,= n.nspname, c.relname
        &n= bsp;      FROM pg_catalog.pg_class c
&nbs= p;             = LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.relnamespace
&n= bsp;            = ; WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
        JOIN
    = ;           (SELECT a.att= name, a.attrelid,
         =       pg_catalog.format_type(a.atttypid, a.atttypm= od) as "Type",
        = ;            &n= bsp; (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
            &nb= sp;         WHERE d.adrelid =3D a.a= ttrelid AND d.adnum =3D a.attnum AND a.atthasdef)
   &nbs= p;           as "Def= ault", a.attnotnull, a.attnum
      &= nbsp;        FROM pg_catalog.pg_attribut= e a
           &n= bsp;   WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
        ON (attrs.attrelid =3D rel.oid )=
        WHERE relname LIKE ''= ;%' || vTabela || '%''
     &nb= sp; ORDER BY attrs.attnum';
FOR r IN EXECUTE eSql
LOO= P
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
        RAISE= EXCEPTION 'Table not found', vTabela;
END IF;
RE= TURN;
END
$BODY$
LANGUAGE 'plpgsql' VOL= ATILE;
ALTER FUNCTION dados_tabela(character varying) OWNER TO postgres;=

2008/3/4, Shahaf Abileah &= lt;shahaf@redfin.com>:

I'm looking for a systematic way to document the schema for the database behind our website (www.redfin.com), so that the devel= opers using this database have a better idea what all the tables and columns mean= and what data to expect. Any recommendations?

It would be great if the documentation could be kept as close to the code as possible =96 that way we stand a chance of keeping it up to date. So, in the same way that Java docs go right there on top = of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability:

&n= bsp; create table table_with_comments(a int comment 'this is column a...'= ;);

(see http://dev.mysql.com/doc/refman/5.0/en/create-table.html)

However, Postgres doesn't support the "comment" keyword. Is there an alternative?

Thanks,

--S

Shahaf A= bileah | Lead Sof= tware Developer

shahaf@redfin.com = | tel: 206.859.2869 | fax: 877.733.3469

Redfin Corporation
710 2nd Ave
Suite 600
Seattle, WA 98104

------=_Part_35927_11538548.1204722627449-- From david@fetter.org Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B76F72E036E; Wed, 5 Mar 2008 11:05:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 22370-03; Wed, 5 Mar 2008 11:05:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fetter.org (start.fetter.org [66.92.188.65]) by postgresql.org (Postfix) with ESMTP id DC6412E027E; Wed, 5 Mar 2008 11:05:29 -0400 (AST) Received: by fetter.org (Postfix, from userid 500) id 20F3BFBCDAA; Wed, 5 Mar 2008 07:05:27 -0800 (PST) Date: Wed, 5 Mar 2008 07:05:27 -0800 From: David Fetter To: Shahaf Abileah Cc: pgsql-admin@postgresql.org, pgsql-docs@postgresql.org, pgsql-general@postgresql.org, psql-novice@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [DOCS] Documenting a DB schema Message-ID: <20080305150527.GK19860@fetter.org> References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> User-Agent: Mutt/1.5.17 (2007-11-01) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/41 X-Sequence-Number: 28524 On Tue, Mar 04, 2008 at 12:02:27PM -0800, Shahaf Abileah wrote: > I'm looking for a systematic way to document the schema for the database > behind our website (www.redfin.com ), so that > the developers using this database have a better idea what all the > tables and columns mean and what data to expect. Any recommendations? You can and should be using COMMENT ON for the important database objects. http://www.postgresql.org/docs/current/static/sql-comment.html > It would be great if the documentation could be kept as close to the > code as possible - that way we stand a chance of keeping it up to > date. If your schema is changing substantively (i.e. anything other than adding/dropping table partitions) with any frequency, that's a sign of a broken design process which you need to fix. > So, in the same way that Java docs go right there on top of the class or > method definitions, it would be great if I could attach my comments to > the table definitions. It looks like MySQL has that kind of capability: See above re: COMMENT ON :) > create table table_with_comments(a int comment 'this is > column a...'); > > (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) > > However, Postgres doesn't support the "comment" keyword. Actually, it does :) > Is there an alternative? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate