Thread: VARCHAR and TEXT
Hi everybody, I have a little question about string types in PostgreSQL: it seems I don't understood well how string data are stored in the database, from a recent post by Tom Lane I feel encouraged to think that VARCHAR and TEXT are treated the same way by PG, i.e. they occupy ony the space needed by their actual length, bt I'm not so sure of it. I ask this because I'm trying using PG as database server in a project developed in delphi under ms-win and it seems to me that varchar and text fields are not treated the same way by odbc interface and/or bde (borland database engine, the database interface layer used by delphi programs). When I connect to a table the fields of type VARCHAR are seen as strings while the fields of type TEXT are seen as blobs, and this is a little upsetting, because some useful functions are not implemented for blob fields. I have several tables with fields that could contain quite lengthy strings, but usually don't. So I don't want to define theese fields as VARCHAR(400) if this means a disk occupation of 400 bye or so for each row (I have some 500000 rows in a table and the average length of the field in question is about 30, but the max length is near 400). Can someone clarify this point? Thanks in advance ___________________PS
i am seeing NullPtrExs with the following stack trace: java.lang.NullPointerException at org.postgresql.Connection.ExecSQL(Connection.java:312) at org.postgresql.jdbc2.Connection.commit(Connection.java:173) at com.commnav.sbh.framework.persist.JDBCConnection.disconnect(JDBCConnection.j ava:416) at com.commnav.sbh.framework.persist.ConnectionPoolImpl.expire(ConnectionPoolIm pl.java:245) at com.commnav.sbh.framework.ObjectPool.clean(ObjectPool.java:178) at com.commnav.sbh.framework.ObjectPool.run(ObjectPool.java:330) at java.lang.Thread.run(Thread.java:484) Our object pool disconnects unused open connections every few minutes. Do the connections themselves timeout? is this a parameter somewhere? I am wondering if I am trying to close a connection that is already closed? please respond directly to me - i'm not in the jdbc group. thanks chris
I gave up from using ODBC... to much problems with blobs and other stuff... and really very low speed under Delphi. Try Zeos Database Components for Postgre(http://www.marms.com/zeos). I've been using for 6 mounths and its great. Blobs greater than 8Kb still are a big problem, but you will see this component can recognize text fields as Memo and OIDs as Binary Blobs. A really good point of these components is that you dont need BDE or ODBC (everything is done accessing a single dll). My apps are very small and installation is a piece of cake. My config : Zeos 3.0.8 under Delphi 5 Enterprise. Regards, José Vilson de Mello de Farias Dígitro Tecnologia Ltda - Brazil ----- Original Message ----- From: Paolo Sinigaglia <sini@informativesystem.it> To: <pgsql-general@postgresql.org> Sent: Quinta-feira, 29 de Março de 2001 11:53 Subject: [GENERAL] VARCHAR and TEXT : Hi everybody, : I have a little question about string types in PostgreSQL: it seems I don't : understood well how string data are stored in the database, from a recent : post by Tom Lane I feel encouraged to think that VARCHAR and TEXT are : treated the same way by PG, i.e. they occupy ony the space needed by their : actual length, bt I'm not so sure of it. : : I ask this because I'm trying using PG as database server in a project : developed in delphi under ms-win and it seems to me that varchar and text : fields are not treated the same way by odbc interface and/or bde (borland : database engine, the database interface layer used by delphi programs). When : I connect to a table the fields of type VARCHAR are seen as strings while : the fields of type TEXT are seen as blobs, and this is a little upsetting, : because some useful functions are not implemented for blob fields. : : I have several tables with fields that could contain quite lengthy strings, : but usually don't. So I don't want to define theese fields as VARCHAR(400) : if this means a disk occupation of 400 bye or so for each row (I have some : 500000 rows in a table and the average length of the field in question is : about 30, but the max length is near 400). : : Can someone clarify this point? : : Thanks in advance : : ___________________PS : : : ---------------------------(end of broadcast)--------------------------- : TIP 3: if posting/reading through Usenet, please send an appropriate : subscribe-nomail command to majordomo@postgresql.org so that your : message can get through to the mailing list cleanly :
Quoting chris markiewicz <cmarkiew@commnav.com>: > i am seeing NullPtrExs with the following stack trace: > > java.lang.NullPointerException > at org.postgresql.Connection.ExecSQL(Connection.java:312) > at org.postgresql.jdbc2.Connection.commit(Connection.java:173) > at > com.commnav.sbh.framework.persist.JDBCConnection.disconnect(JDBCConnection.j > ava:416) > at > com.commnav.sbh.framework.persist.ConnectionPoolImpl.expire(ConnectionPoolIm > pl.java:245) > at com.commnav.sbh.framework.ObjectPool.clean(ObjectPool.java:178) > at com.commnav.sbh.framework.ObjectPool.run(ObjectPool.java:330) > at java.lang.Thread.run(Thread.java:484) > > Our object pool disconnects unused open connections every few minutes. > Do the connections themselves timeout? is this a parameter somewhere? I > am wondering if I am trying to close a connection that is already closed? It's possible but it shouldn't occur. I'll check. As for the connections timing out, AFAIK no. > please respond directly to me - i'm not in the jdbc group. Due to the amount of emails I get I'll be dropping general soon (600+ in the last 24hrs is a tad too many now), so it's best to join it. Peter -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/