Re: Chante domain type - Postgres 9.2

TEXT is a native type in PostgreSQL, and is highly optimized behind the scenes to be as fast and efficient as possible in both the storage and retrieval of the data.

Regarding user input validation, it is almost always better to let the customer-facing app do the validation instead of relying upon the backend storage engine to do this for you. One reason for this it isn't trivial to capture the error from PostgreSQL, parse it and present it to the user in an intelligible manner. Second, it is usually much easier to change the validation in the user interface than to do it in the backend database.

For example, if you have a user requirement to limit say, LastName to 50 characters, and years down the road you discover that you need to up it to 75 characters, you can change it in the interface and be done with it. But if you had originally used VARCHAR(50) in the backend, you will need to change it to VARCHAR(75) in every table where you use LastName. I have had to do this myself, and believe me, it is not fun.

As fellow DBAs and devs who have had these kinds of painful experiences, we are just trying to save you from the same pitfalls. But I guess that there is some truth to the old adage that we must learn from our own mistakes. :)

> On Sep 26, 2016, at 8:46 AM, Michael Sheaver <msheaver(at)me(dot)com> wrote:
>
> I have done some research after converting my database from MySQL 5.6 to PostgreSQL 9.6 (the best move I have ever made), and the consensus I found can be summed up as:
> 1. Never, neve, never use VARCHAR or even CHAR
> 2. Always always, always use TEXT
>
> Unless, that is, you have some kind of edge case. This may require a little work upfront, but it will save you from a TON of grief down the road.
>
>
>> On Sep 26, 2016, at 8:29 AM, Jan de Visser <jan(at)de-visser(dot)net <mailto:jan(at)de-visser(dot)net>> wrote:
>>
>> On 2016-09-26 1:15 AM, Gavin Flower wrote:
>>
>>> On 26/09/16 17:58, Patrick B wrote:
>>>> Hi guys,
>>>>
>>>> I've got this domain:
>>>>
>>>> CREATE DOMAIN public.a_city
>>>> AS character varying(80)
>>>> COLLATE pg_catalog."default";
>>>>
>>>>
>>>> And I need to increase the type from character varying(80) to character varying(255).
>>>>
>>>> How can I do that? didn't find info about it. I'm using Postgres 9.2
>>>>
>>>> Thanks!
>>>> Patrick
>>>
>>> Why not simply use the 'text' data type?
>>>
>>> To change the data type on a column you can use:
>>> ALTER [ COLUMN ] /column_name/ [ SET DATA ] TYPE /data_type/ [ COLLATE /collation/ ] [ USING /expression/ ]
>>>
>>> see:
>>> https://www.postgresql.org/docs/9.2/static/sql-altertable.html <https://www.postgresql.org/docs/9.2/static/sql-altertable.html>
>>>
>>>
>>> Note that 9.5 is the latest version of pg, with 9.6 being released very soon!
>>>
>>>
>>> Cheers,
>>> Gavin
>>>
>>
>> So I guess the answer to the question is:
>> - Find all occurrences of a_city
>> - Change the type of those columns to text (or varchar(80))
>> - Drop the domain
>> - Recreate with the proper definition. I agree with Gavin that text is a better choice. Experience has taught me that server side size constraint are more trouble than they're worth and that size constraints are better handled on the client side.
>> - Change the type of the columns back to the domain.
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general>