From r1c4n@yahoo.com Thu Apr 20 03:32:08 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 320AB11F6D2A for ; Thu, 20 Apr 2006 00:32:08 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 3.559 X-Spam-Level: *** X-Spam-Status: No, score=3.559 required=5 tests=[BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_WHOIS=1.447, FROM_HAS_MIXED_NUMS=2.155, HTML_40_50=0.496, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id YJIaZqMvTORu for ; Thu, 20 Apr 2006 00:32:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web33408.mail.mud.yahoo.com (web33408.mail.mud.yahoo.com [68.142.206.140]) by postgresql.org (Postfix) with SMTP id 474CF11F6D22 for ; Thu, 20 Apr 2006 00:32:04 -0300 (ADT) Received: (qmail 50063 invoked by uid 60001); 20 Apr 2006 03:32:02 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=s2O8n6aJK32tOq7gTRQVKWGjMnSB0lNaSIXWPbQo+LJZdr45K7DCfR0YbgNH/CiyVauAGNmzhUknMSIsCHuHn3uOE44O4RRmPd4ToGQ0cCkJNbiICEGrNzCUSrS8OCwrM9MRDhQ8vFCp0gPi/RvelPUJEu7ZHFgPGG5JQ1jPXUs= ; Message-ID: <20060420033202.50061.qmail@web33408.mail.mud.yahoo.com> Received: from [222.124.227.199] by web33408.mail.mud.yahoo.com via HTTP; Wed, 19 Apr 2006 20:32:02 PDT Date: Wed, 19 Apr 2006 20:32:02 -0700 (PDT) From: Louise Catherine Subject: Field length ?? To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1025070810-1145503922=:48676" Content-Transfer-Encoding: 8bit X-Archive-Number: 200604/130 X-Sequence-Number: 24939 --0-1025070810-1145503922=:48676 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit I've this table : create table test(] satu char(10), dua char(5) ); How could I get the Field length not the Data length ? Thanks, Rina --------------------------------- Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice. --0-1025070810-1145503922=:48676 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit I've this table :

create table test(]
satu char(10),
dua char(5)
);

How could I get the Field length not the Data length ?


Thanks,
Rina

Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice. --0-1025070810-1145503922=:48676-- From r1c4n@yahoo.com Thu Apr 20 06:08:47 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 7347511F8CD4 for ; Thu, 20 Apr 2006 03:08:47 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 1.555 X-Spam-Level: * X-Spam-Status: No, score=1.555 required=5 tests=[AWL=0.351, BAYES_00=-2.599, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_WHOIS=1.447, FROM_HAS_MIXED_NUMS=2.155, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id FaV8waHUSEsI for ; Thu, 20 Apr 2006 03:08:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web33403.mail.mud.yahoo.com (web33403.mail.mud.yahoo.com [68.142.206.135]) by postgresql.org (Postfix) with SMTP id E070C11F8CC7 for ; Thu, 20 Apr 2006 03:08:38 -0300 (ADT) Received: (qmail 46276 invoked by uid 60001); 20 Apr 2006 06:08:38 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=St9QaZX3/fMSaFlsnVKEA5VDegEV0eTnI79VlNs3KDAmGlRH+bVMDoQIRPAY8IrhDl0QZtHys/pzxvleR1S8q0Ec/pTRrTRbGcp2Wa9Ik1p/JQjwz1ZSUBPCCZmXcTG7jsIXrKffeByQqSU6spV5mLx4f8eP5lguR+PczNuMJAg= ; Message-ID: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> Received: from [222.124.227.199] by web33403.mail.mud.yahoo.com via HTTP; Wed, 19 Apr 2006 23:08:38 PDT Date: Wed, 19 Apr 2006 23:08:38 -0700 (PDT) From: Louise Catherine Subject: Re: Field length ?? To: pgsql-sql@postgresql.org In-Reply-To: <20060420033202.50061.qmail@web33408.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-633594195-1145513318=:46248" Content-Transfer-Encoding: 8bit X-Archive-Number: 200604/133 X-Sequence-Number: 24942 --0-633594195-1145513318=:46248 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi , With this table : create table test( satu char(10), dua char(5) ); To get the field length, I use this query: select atttypmod from pg_attribute where attname='satu' and attrelid=(select oid from pg_class where relname='test'); ---> result 1 : 14 select atttypmod from pg_attribute where attname='dua' and attrelid=(select oid from pg_class where relname='test'); ---> result 2 : 9 Could anyone explain, why the field length must be add by 4 : result 1 : 10 + 4 =14 result 2 : 5 + 4 = 9 Could I use this query ? Is it valid? select atttypmod-4 from pg_attribute where attname='satu' and attrelid=(select oid from pg_class where relname='test'); Thank's, Rina --------------------------------- How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates. --0-633594195-1145513318=:46248 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi ,

With this table :
create table test(
satu char(10),
dua char(5)
);

To get the field length, I use this query:

select atttypmod from pg_attribute where
attname='satu' and
attrelid=(select oid from pg_class where relname='test');
---> result 1 : 14

select atttypmod from pg_attribute where
attname='dua' and
attrelid=(select oid from pg_class where relname='test');
---> result 2 : 9


Could anyone explain, why the field length must be add by 4 :
result 1 : 10 + 4 =14
result 2 : 5 + 4 = 9

Could I use this query ? Is it valid?
select atttypmod-4 from pg_attribute where
attname='satu' and
attrelid=(select oid from pg_class where relname='test');

Thank's,
Rina

How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates. --0-633594195-1145513318=:46248-- From schabi@logix-tt.com Thu Apr 20 09:15:59 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 92D7C11F6C49 for ; Thu, 20 Apr 2006 06:15:59 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.169 X-Spam-Level: X-Spam-Status: No, score=-2.169 required=5 tests=[AWL=0.295, BAYES_00=-2.599, FORGED_RCVD_HELO=0.135] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id 7gCjSnyLah+y for ; Thu, 20 Apr 2006 06:15:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (unknown [212.211.145.186]) by postgresql.org (Postfix) with ESMTP id 5B84A11F6C19 for ; Thu, 20 Apr 2006 06:15:56 -0300 (ADT) Received: from kingfisher.intern.logix-tt.com (T96ed.t.pppool.de [89.55.150.237]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id DF04B656CD; Thu, 20 Apr 2006 11:16:55 +0200 (CEST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 273BD181C1F52; Thu, 20 Apr 2006 11:16:12 +0200 (CEST) Message-ID: <4447515C.5070601@logix-tt.com> Date: Thu, 20 Apr 2006 11:16:12 +0200 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Louise Catherine Cc: pgsql-sql@postgresql.org Subject: Re: Field length ?? References: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> In-Reply-To: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/135 X-Sequence-Number: 24944 Hi, Louise, Louise Catherine wrote: > Could anyone explain, why the field length must be add by 4 : > result 1 : 10 + 4 =14 > result 2 : 5 + 4 = 9 I guess that it is because all variable length datatypes (and text types are such) internally contain a 4 bytes length field. http://www.postgresql.org/docs/8.1/interactive/xtypes.html might be a start if you want to read more on this topic. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org From tgl@sss.pgh.pa.us Thu Apr 20 14:27:28 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7EE2911F6095 for ; Thu, 20 Apr 2006 11:27:28 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.125 X-Spam-Level: X-Spam-Status: No, score=0.125 required=5 tests=[AWL=0.125] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id sktpOt74TdhH for ; Thu, 20 Apr 2006 11:27:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 68AB411F6296 for ; Thu, 20 Apr 2006 11:27:13 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3KER90b018167; Thu, 20 Apr 2006 10:27:09 -0400 (EDT) To: Markus Schaber cc: Louise Catherine , pgsql-sql@postgresql.org Subject: Re: Field length ?? In-reply-to: <4447515C.5070601@logix-tt.com> References: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> <4447515C.5070601@logix-tt.com> Comments: In-reply-to Markus Schaber message dated "Thu, 20 Apr 2006 11:16:12 +0200" Date: Thu, 20 Apr 2006 10:27:09 -0400 Message-ID: <18166.1145543229@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/138 X-Sequence-Number: 24947 Markus Schaber writes: > Louise Catherine wrote: >> Could anyone explain, why the field length must be add by 4 : > I guess that it is because all variable length datatypes (and text types > are such) internally contain a 4 bytes length field. It's an ancient decision that no longer has much of anything to do with reality, but changing it would break more code than it's worth to have a slightly cleaner definition of atttypmod ... regards, tom lane From fbax@sympatico.ca Thu Apr 20 15:30:38 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C28E111F6077 for ; Thu, 20 Apr 2006 12:30:38 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.096 X-Spam-Level: X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id bY0fJ2NNHmFe for ; Thu, 20 Apr 2006 12:30:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from tomts36-srv.bellnexxia.net (tomts36.bellnexxia.net [209.226.175.93]) by postgresql.org (Postfix) with ESMTP id D54B411F6057 for ; Thu, 20 Apr 2006 12:30:32 -0300 (ADT) Received: from acer.sympatico.ca ([65.95.176.171]) by tomts36-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20060420153031.FZBO13653.tomts36-srv.bellnexxia.net@acer.sympatico.ca> for ; Thu, 20 Apr 2006 11:30:31 -0400 Message-Id: <5.2.1.1.0.20060420111921.00a5b920@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Thu, 20 Apr 2006 11:21:27 -0400 To: pgsql-sql@postgresql.org From: Frank Bax Subject: Re: Field length ?? In-Reply-To: <4447515C.5070601@logix-tt.com> References: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200604/140 X-Sequence-Number: 24949 At 05:16 AM 4/20/06, Markus Schaber wrote: >Hi, Louise, > >Louise Catherine wrote: > > > Could anyone explain, why the field length must be add by 4 : > > result 1 : 10 + 4 =14 > > result 2 : 5 + 4 = 9 > >I guess that it is because all variable length datatypes (and text types >are such) internally contain a 4 bytes length field. Except that the original fields were neither variable length datatypes, not type text. create table test(] satu char(10), dua char(5) ); From schabi@logix-tt.com Thu Apr 20 15:36:33 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 153B511F6009 for ; Thu, 20 Apr 2006 12:36:33 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id GtbyFWPsIU5x for ; Thu, 20 Apr 2006 12:36:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (unknown [212.211.145.186]) by postgresql.org (Postfix) with ESMTP id 5EA3611F6069 for ; Thu, 20 Apr 2006 12:36:25 -0300 (ADT) Received: from kingfisher.intern.logix-tt.com (T96ed.t.pppool.de [89.55.150.237]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id E2ADE656CD; Thu, 20 Apr 2006 17:37:24 +0200 (CEST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 90DB8181C1F57; Thu, 20 Apr 2006 17:36:41 +0200 (CEST) Message-ID: <4447AA89.9060405@logix-tt.com> Date: Thu, 20 Apr 2006 17:36:41 +0200 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Frank Bax Cc: pgsql-sql@postgresql.org Subject: Re: Field length ?? References: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> <5.2.1.1.0.20060420111921.00a5b920@pop6.sympatico.ca> In-Reply-To: <5.2.1.1.0.20060420111921.00a5b920@pop6.sympatico.ca> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/141 X-Sequence-Number: 24950 Hi, Frank, Frank Bax wrote: > Except that the original fields were neither variable length datatypes, > not type text. > create table test(] > satu char(10), > dua char(5) > ); char is a textual type (in opposite to numeric types), and they are handled as variable length datatypes internally (even if given a limit). See http://www.postgresql.org/docs/8.1/static/datatype-character.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org From tgl@sss.pgh.pa.us Thu Apr 20 15:44:45 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 84B4911F607F for ; Thu, 20 Apr 2006 12:44:45 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.369 X-Spam-Level: X-Spam-Status: No, score=-2.369 required=5 tests=[AWL=0.232, BAYES_00=-2.599, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id XbFgrWCOMNLe for ; Thu, 20 Apr 2006 12:44:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 2AC0111F6031 for ; Thu, 20 Apr 2006 12:44:27 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3KFiPKX019021; Thu, 20 Apr 2006 11:44:25 -0400 (EDT) To: Markus Schaber cc: Frank Bax , pgsql-sql@postgresql.org Subject: Re: Field length ?? In-reply-to: <4447AA89.9060405@logix-tt.com> References: <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> <20060420060838.46274.qmail@web33403.mail.mud.yahoo.com> <5.2.1.1.0.20060420111921.00a5b920@pop6.sympatico.ca> <4447AA89.9060405@logix-tt.com> Comments: In-reply-to Markus Schaber message dated "Thu, 20 Apr 2006 17:36:41 +0200" Date: Thu, 20 Apr 2006 11:44:25 -0400 Message-ID: <19020.1145547865@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/142 X-Sequence-Number: 24951 Markus Schaber writes: > Frank Bax wrote: >> Except that the original fields were neither variable length datatypes, >> not type text. > char is a textual type (in opposite to numeric types), and they are > handled as variable length datatypes internally (even if given a limit). char(n) *is* a variable-length type, not merely "handled as such", because the limit N is measured in characters not bytes. To support variable-length encodings such as UTF8 we have to treat it as variable length. regards, tom lane