Re: Chante domain type - Postgres 9.2

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Rob Sargent <robjsargent(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Chante domain type - Postgres 9.2
Date: 2016-09-26 14:44:01
Message-ID: 13b9455e-4f3d-06da-5687-a5287fad2c6e@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 09/26/2016 07:38 AM, Rob Sargent wrote:
>
>
> On 09/26/2016 08:14 AM, Adrian Klaver wrote:
>> On 09/26/2016 06:54 AM, Thomas Kellerer wrote:
>>> Rakesh Kumar schrieb am 26.09.2016 um 15:08:
>>>>> You sound like you think that varchar(50) is somehow cheaper than
>>>>> text.
>>>>
>>>> The biggest impediment to text cols in other RDBMS is no index
>>>> allowed.
>>>> If PG has an elegant solution to that, then yes I see the point made
>>>> by the
>>>> original poster.
>>>
>>> Don't confuse Postgres' "text" data type with "text" in other DBMS.
>>
>> Just be aware that layers above the database often do not understand
>> that and will see text as a memo field. For instance in Django a text
>> field will get rendered as a Textarea widget whereas a varchar field
>> will be rendered as an TextInput widget. You can override that, but it
>> is extra work. Luckily Postgres has the notion of an unbounded varchar:
>>
>> https://www.postgresql.org/docs/9.5/static/datatype-character.html
>>
>> ".. If character varying is used without length specifier, the type
>> accepts strings of any size. The latter is a PostgreSQL extension."
>>
>> This allows you to have 'text' without it being called text, as stated
>> below.
>>
>>>
>>> There is no difference whatsoever between varchar and text in Postgres.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
> Does that trick remove the overhead (length check) Tom mentioned upstream?
>

I believe so if I am reading the docs right:

https://www.postgresql.org/docs/9.5/static/datatype-character.html

"(The maximum value that will be allowed for n in the data type
declaration is less than that. It wouldn't be useful to change this
because with multibyte character encodings the number of characters and
bytes can be quite different. If you desire to store long strings with
no specific upper limit, use text or character varying without a length
specifier, rather than making up an arbitrary length limit.)

Tip: 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 and slower sorting. In most
situations text or character varying should be used instead."

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-09-26 14:56:18 Re: Chante domain type - Postgres 9.2
Previous Message Rob Sargent 2016-09-26 14:38:13 Re: Chante domain type - Postgres 9.2