PostgreSQL:DataTypes

lastmod 2023-07-16created 2021-09-155 edits

Data types

• Basics of creating tables in PostgreSQL
• [Recommended] A Look At Various PostgreSQL Integer Data Types

JSON type

• JSON data type operators in PostgreSQL
• Querying a specific value in an array of the JSONB data type in PostgreSQL
• PostgreSQL: Documentation: 9.4: JSON Types

Postgresql supports the JSON data type.

• JSON: The JSON data type was added in PostgreSQL 9.2, so JSON objects can be stored in columns. This is a very useful feature: you can take advantage of being Schemaless like NoSQL while still using the features of an RDB.
• JSONB: The JSONB data type was added in PostgreSQL 9.4. According to the documentation, Insert is slower than JSON, but because it is stored in a binary format, processing is much more flexible.

Do not use varchar by default

• Don't Do This - PostgreSQL wiki

Don't use the type varchar(n) by default. Consider varchar (without the length limit) or text instead.

Why not?

varchar(n) is a variable width text field that will throw an error if you try and insert a string longer than n characters (not bytes) into it.

varchar (without the (n)) or text are similar, but without the length limit. If you insert the same string into the three field types they will take up exactly the same amount of space, and you won't be able to measure any difference in performance.

If what you really need is a text field with an length limit then varchar(n) is great, but if you pick an arbitrary length and choose varchar(20) for a surname field you're risking production errors in the future when Hubert Blaine Wolfe­schlegel­stein­hausen­berger­dorff signs up for your service.

Some databases don't have a type that can hold arbitrary long text, or if they do it's not as convenient or efficient or well-supported as varchar(n). Users from those databases will often use something like varchar(255) when what they really want is text.

If you need to constrain the value in a field you probably need something more specific than a maximum length - maybe a minimum length too, or a limited set of characters - and a check constraint can do all of those things as well as a maximum string length.

When should you?

When you want to, really. If what you want is a text field that will throw an error if you insert too long a string into it, and you don't want to use an explicit check constraint then varchar(n) is a perfectly good type. Just don't use it automatically without thinking about it.

Also, the varchar type is in the SQL standard, unlike the text type, so it might be the best choice for writing super-portable applications.

Limiting character length instead of varchar(n)

See PostgreSQL:Constraints#Character length limit. In short:

ALTER TABLE names ADD CONSTRAINT namechk CHECK (char_length(name) <= 255);

See also

• PostgreSQL

Favorite site

• PostgreSQL: Documentation: 9.5: Data Types