how big is Text in Postgres?

Posted on 2010-01-09
Medium Priority
Last Modified: 2012-05-08
I set up a table schema using hiberante.

the config is

            <property name="bio"  not-null="true" type="text" length="2000" >
                  <column name="BIO" default="''" />

when I look at the table schema using a sql client, the field type is indeed "Text", but no indication of size.

How do find out what size was really allocated?
Question by:bhomass
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 30

Accepted Solution

Reza Rad earned 300 total points
ID: 26276275
TEXT is for strings of "unlimited" length (though all fields in PostgreSQL are subject to a maximum value length of one gigabyte).



Author Comment

ID: 26276355
is there an alternative to set the max length at 2000, let's say?
LVL 12

Assisted Solution

cminear earned 200 total points
ID: 26296582
If you want a character field to be limited in size in PostgreSQL, use "character varying(<size>)" or "varchar(<size>)", where you set <size> to the desired length limit.

Using 'psql', this command would change the column definition to use the 'varchar(2000)' type.

  ALTER TABLE your_table ALTER COLUMN bio TYPE varchar(2000);

This will only work if all records have 2000 characters or less in the 'bio' field.

With Hibernate, it would appear that you'd want the type to be "string" instead of "text".

Author Comment

ID: 26297204
thanks for the advice. I am going to reward the points, but could someone give me some guidance whether I suffer performance and efficiency setback by staying with Text type?

it seems since Text has no max, it must be allocating space as the data grows. in that sense, it is not very different from varchar.
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 300 total points
ID: 26297629

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question