Thread: text field

Hi,

I need to put a field in a database that will hold a login for the users.
That login is rather long (it's their e-mail address) and of variable length (I can't
change that :-).
I thought of using the text type, but wondered what were the implications in terms of
speed and amount of data (does it use much more space than other character types?)

Thanks for your help.

Raph

Raphael,

> I need to put a field in a database that will hold a login for the
>  users.
> That login is rather long (it's their e-mail address) and of variable
>  length (I can't
> change that :-).
> I thought of using the text type, but wondered what were the
>  implications in terms of
> speed and amount of data (does it use much more space than other
>  character types?)

Not in PostgreSQL.  However, you may find that some interfaces (such as
 ODBC and JDBC) put limits on waht you can do with a Text column.  For
 example, MS Access/ODBC will interpret a TEXT field (or and VARCHAR
 over 250 chars) as a "memo" field and refuse to let you search or
 aggregate on the field.

Otherwise, TEXT and VARCHAR perform the same in PostgreSQL.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

At 01:35 PM 1/29/02 -0800, Josh Berkus wrote:
>Raphael,
>
>> I need to put a field in a database that will hold a login for the
>>  users.
>> That login is rather long (it's their e-mail address) and of variable
>>  length (I can't change that :-).
>> I thought of using the text type, but wondered what were the
>>  implications in terms of speed and amount of data (does it use much
>>  more space than othe character types?)
>
>Not in PostgreSQL.  However, you may find that some interfaces (such as
> ODBC and JDBC) put limits on waht you can do with a Text column.  For
> example, MS Access/ODBC will interpret a TEXT field (or and VARCHAR
> over 250 chars) as a "memo" field and refuse to let you search or
> aggregate on the field.
>
>Otherwise, TEXT and VARCHAR perform the same in PostgreSQL.

Almost the same?  I read recently that VARCHAR(n) has a minor overhead on
UPDATE to enforce the max length.

Frank,

> >Otherwise, TEXT and VARCHAR perform the same in PostgreSQL.
>
> Almost the same?  I read recently that VARCHAR(n) has a minor
>  overhead on
> UPDATE to enforce the max length.

Hmmm ... yes, probably.  I was comparing with SQL-Server, where large
 text fields are stored as attached files and thus have vastly slower
 performance than VARCHAR.   I suppose that it would be an amusing
 turnabout if, thanks to TOAST, TEXT was marginally faster than VARCHAR
 for INSERT & UPDATE in Postgres.

Warning, though; despite TOAST, you will still see a significant
 degradation is database performance with very large text fields if
 your machine has limited disk access (such as an IDE drive).
  Basically, if the size of your text file exceeds the buffer size
 you've set in postgresql.conf (default is 8k I think) then you'll see
 queries slow down 10-fold as the database engine shuttles your data to
 and from the swap files on disk.  You can fix this by increasing the
 buffer (which may require buying more memory for the computer!) and/or
 adding more/faster disks to the computer.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco