Comparison of PostgreSQL text types

Question

I'm migrating from MySQL to PostgreSQL because Oracle. There is a great MySQL text type reference, here is the relevant information for MySQL...

CHAR( ) A fixed section from 0 to 255 characters long.

VARCHAR( ) A variable section from 0 to 255 characters long.

TINYTEXT A string with a maximum length of 255 characters.

TEXT A string with a maximum length of 65535 characters.

BLOB A string with a maximum length of 65535 characters.

MEDIUMTEXT A string with a maximum length of 16777215 characters.

MEDIUMBLOB A string with a maximum length of 16777215 characters.

LONGTEXT A string with a maximum length of 4294967295 characters.

LONGBLOB A string with a maximum length of 4294967295 characters.

PostgreSQL seems a bit different, there is a text type looking through phppgAdmin, not sure what else there is and I'm not finding any good comparison tables.

What are all the available text types in PostgreSQL?

Answer 1

PostgreSQL has more advanced types but doesn't need the distinction between text sizes.

There are 3 string types in PostgreSQL and a binary type:

text

Just a text object with a non-specified size. You can put anything in here and it will be stored. Size doesn't matter.

varchar(n) / character varying(n)

Basically a text which has a size check, there is virtually no (except for checking the size while inserting) performance difference here.

char(n) / character(n)

Just a text where all the extra characters will be padded with space characters so you always get n characters back.

bytea

The blob type you've mentioned is a totally different type alltogether. You could replace it with the bytea type: http://www.postgresql.org/docs/9.3/static/datatype-binary.html

---

Source: http://www.postgresql.org/docs/9.3/static/datatype-character.html