Re: Chante domain type - Postgres 9.2 - Mailing list pgsql-general
From | Michael Sheaver |
---|---|
Subject | Re: Chante domain type - Postgres 9.2 |
Date | |
Msg-id | 6A4EB9B8-5236-454B-B3C4-14791649B23F@me.com Whole thread Raw |
In response to | Re: Chante domain type - Postgres 9.2 (Michael Sheaver <msheaver@me.com>) |
List | pgsql-general |
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.
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@me.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 CHAR2. Always always, always use TEXTUnless, 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@de-visser.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
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: