File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes text datatype in postgres Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "text datatype in postgres" Watch "text datatype in postgres" New topic

text datatype in postgres

RamandeepS Singh
Ranch Hand

Joined: Aug 25, 2009
Posts: 63

I have to store string of variable lengths in our postgres database.. Strings could be too long
SO can anyone please tell me the pros and crons of the type text(unlimited length), or should i go for some other way to store the string

Ramandeep S

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

This forum uses "text" in postgres to store forum posts. It's similar to the CLOB type in other databases.

The only warning I can think of is that it is good practice to store the text/CLOB type in a table separate from your main data. For example, the jforum_posts table has about 20 columns including a post_id, but not the big field. The jforum_posts_text table has only two columns: post_id and text.

The reason for this is so the database has more rows in each "page" on memory from the main table and can more quickly execute filters (the where clause) against it.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Richard Broersma
Ranch Hand

Joined: Apr 28, 2009
Posts: 63

PostgreSQL should put large text fields in a Toast table automatically for your.
I agree. Here's the link:
subject: text datatype in postgres
It's not a secret anymore!