Thread: Chante domain type - Postgres 9.2
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
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
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.
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@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
>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.
>TON of grief down the road.
Can you elaborate? Why would anyone create a text column to store customer name or product name which can very well be in varchar(50) type of cols.
Rakesh Kumar <rakeshkumar464@outlook.com> writes:
> Can you elaborate? Why would anyone create a text column to store customer name or product name which can very well
bein varchar(50) type of cols.
You sound like you think that varchar(50) is somehow cheaper than text.
That's backwards (at least in PG, other DBMSes may be different).
There's no advantage storage-wise, and there is a cost, namely the cost
of applying the length check on every update.
If you feel that you must have a check for application-specific reasons,
then sure, use varchar(n). But the number had better be one that you
can trace to crystal-clear application requirements. varchar(n) where
n has been plucked from the air is a good sign of bad database design.
regards, tom lane
>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.
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
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. There is no difference whatsoever between varchar and text in Postgres.
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. > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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?
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.klaver@aklaver.com
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? Should have said earlier, the other side of the story is it makes your schema less portable. Since I gave up on that some time ago it is not something that is my first concern. -- Adrian Klaver adrian.klaver@aklaver.com
Rob Sargent <robjsargent@gmail.com> writes: > On 09/26/2016 08:14 AM, Adrian Klaver wrote: >> 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." > Does that trick remove the overhead (length check) Tom mentioned upstream? Partly. It should get rid of actual calls to the varchar length checking function. There's still some distributed overhead arising from the fact that text, not varchar, is the native string type in Postgres. So for example anyplace you apply a concatenation operator, varchar inputs have to be casted to text, and the result has to be casted to varchar if it's being stored into a varchar field. I've never seen any serious attempt to quantify how much that costs, but it's not zero. regards, tom lane
> > Can you elaborate? Why would anyone create a text column to store > customer name or product name which can very well be in varchar(50) > type of cols. > > You sound like you think that varchar(50) is somehow cheaper than text. > That's backwards (at least in PG, other DBMSes may be different). > There's no advantage storage-wise, and there is a cost, namely the cost > of applying the length check on every update. > > If you feel that you must have a check for application-specific > reasons, then sure, use varchar(n). But the number had better be one > that you can trace to crystal-clear application requirements. > varchar(n) where n has been plucked from the air is a good sign of bad > database design. What a about using text x varchar(n) in primary key?