PostgreSQL equivalent to SQL Server NVARCHAR (MAX)

Question

I know that in PostgreSQL, VARCHAR(n) is equivalent to SQL Server's NVARCHAR(n), but how can I use something like VARCHAR(MAX) in PostgreSQL, something similar to NVARCHAR(MAX) in SQL Server?

`text`: https://www.postgresql.org/docs/current/datatype-character.html — Abelisto, Jun 10 '19 at 06:35
It is too short for the answer. Just read PostgreSQL documentation carefully - IMO it is really nice reading :) — Abelisto, Jun 10 '19 at 06:53
@Abelisto True, but I wish if you provide here in answer so that it can be helpful for someone else in future. — Pratik, Jun 10 '19 at 06:57
It's **`NVARCHAR`** - not **`NVCHAR`** - in SQL Server - corrrected — marc_s, Jun 10 '19 at 07:07

Abelisto · Accepted Answer · 2019-06-10T07:23:40.263

In PostgreSQL for char(n) and varchar(n) it is just limited by n argument (not sure about nature of such limitation, it seems that it is 20 Mb or 10M of 2-bytes characters or something):

postgres=# select 'a'::varchar(999999999);
ERROR:  length for type varchar cannot exceed 10485760

So if you really need the equivalent for MS SQL Server nvarchar(max) - it could be varchar(10485760)

However to use really unlimited character values there are two ways: varchar or character varying without argument or just text type. Read more in the documentation and note about Tip provided in it:

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

score 4 · Answer 2 · answered Jun 10 '19 at 08:12

If you want to created an "unbounded" varchar column just use varchar without a length restriction.

From the manual:

If character varying is used without length specifier, the type accepts strings of any size

So you can use:

create table foo
( 
  unlimited  varchar
);

Another alternative is to use text:

create table foo
( 
  unlimited text
);

More details about character data types are in the manual:
http://www.postgresql.org/docs/current/static/datatype-character.html

score 0 · Answer 3 · answered May 21 '20 at 20:16

text, varchar can be used for this requirement. i used varchar

Character Types Description

character varying(n), varchar(n) --- variable-length with limit

character(n), char(n) -- fixed-length, blank padded

text, varchar -- variable unlimited length

More Details: https://www.postgresqltutorial.com/postgresql-char-varchar-text/

PostgreSQL equivalent to SQL Server NVARCHAR (MAX)

3 Answers3

Linked