Обсуждение: [NOVICE] varchar vs varchar(n)

Поиск
Список
Период
Сортировка

[NOVICE] varchar vs varchar(n)

От
john snow
Дата:
do postgresql developers just use varchar instead of specifying a limit n when dealing with string types? if so, are there any gotcha's i should be aware of?

i'm doing the last "routes" exercise in Chapter 3 of Apress' book MariaDB and MySQL Common Table Expressions and Window Functions Revealed.

for that exercise, the original DDL for the routes table was:
create table routes (
  id serial primary key,
  departing varchar(100) not null,
  arriving varchar(100) not null
);

the exercise involved crafting a recursive CTE to enumerate all possible paths to a destination that started in Raleigh.

this is the query i executed (modified to replace MySQL's LOCATE to postgresql's POSITION):
WITH RECURSIVE full_routes AS (
  SELECT departing AS path, arriving
  FROM routes
  WHERE departing='Raleigh'
UNION
  SELECT
    CONCAT(full_routes.path, ' > ', routes.arriving),
routes.arriving
  FROM full_routes, routes
  WHERE
    full_routes.arriving=routes.departing
AND
-- POSITION is the equivalent of MySQL LOCATE
POSITION(routes.arriving IN full_routes.path)=0
)
SELECT * FROM full_routes;

and these are the table's data:
c:\csvs>type bartholomew-ch03-routes.csv
1,"Raleigh","Washington"
2,"Raleigh","Atlanta"
3,"Raleigh","Miami"
4,"Atlanta","Chicago"
5,"Chicago","New York"
6,"New York","Washington"
7,"Washington","Raleigh"
8,"New York","Toronto"
9,"Washington","New York"
10,"Atlanta","Miami"
11,"Atlanta","Raleigh"
12,"Miami","Raleigh"
13,"Houston","Chicago"
14,"Toronto","New York"

when i ran the query in pgAdmin4's query tool, i got a message saying that "departing" in the non-recursive part of the CTE is defined to be a varchar(100), but is a varchar overall everywhere else.

looks like other people have encountered similar problems in the past, and they just modified the DDL to use varchar, which in my case would be:
create table routes (
  id serial primary key,
  departing varchar not null,
  arriving varchar not null
);

when dealing with relatively short-lengthed string types, are there any downsides to just specifying them as varchar instead of varchar(n)?

thanks for helping!

Re: [NOVICE] varchar vs varchar(n)

От
Tom Lane
Дата:
john snow <ofbizfanster@gmail.com> writes:
> do postgresql developers just use varchar instead of specifying a limit n
> when dealing with string types? if so, are there any gotcha's i should be
> aware of?

Generally speaking, I would only use varchar(n) when there is a clear
reason traceable to application requirements why there has to be a
limit, and why the limit should be n and not some other number.
Otherwise you're just creating issues for yourself.  The habit of
inventing arbitrary limits on text column width is just a hangover
from punched-card days.

Actually, Postgres people tend to use "text" rather than unconstrained
"varchar".  In principle those two types behave equivalently; but the
system has to jump through some extra hoops to work with varchar, and
every so often you'll run into a case where "varchar" is not optimized
as well as "text".
        regards, tom lane


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: [NOVICE] varchar vs varchar(n)

От
john snow
Дата:
thanks!

On Sun, Nov 12, 2017 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
john snow <ofbizfanster@gmail.com> writes:
> do postgresql developers just use varchar instead of specifying a limit n
> when dealing with string types? if so, are there any gotcha's i should be
> aware of?

Generally speaking, I would only use varchar(n) when there is a clear
reason traceable to application requirements why there has to be a
limit, and why the limit should be n and not some other number.
Otherwise you're just creating issues for yourself.  The habit of
inventing arbitrary limits on text column width is just a hangover
from punched-card days.

Actually, Postgres people tend to use "text" rather than unconstrained
"varchar".  In principle those two types behave equivalently; but the
system has to jump through some extra hoops to work with varchar, and
every so often you'll run into a case where "varchar" is not optimized
as well as "text".

                        regards, tom lane

Re: [NOVICE] varchar vs varchar(n)

От
Ken Benson
Дата:

 

From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of john snow
Sent: Sunday, November 12, 2017 1:53 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] varchar vs varchar(n)

 

thanks!

 

On Sun, Nov 12, 2017 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

john snow <ofbizfanster@gmail.com> writes:
> do postgresql developers just use varchar instead of specifying a limit n
> when dealing with string types? if so, are there any gotcha's i should be
> aware of?

Generally speaking, I would only use varchar(n) when there is a clear
reason traceable to application requirements why there has to be a
limit, and why the limit should be n and not some other number.
Otherwise you're just creating issues for yourself.  The habit of
inventing arbitrary limits on text column width is just a hangover
from punched-card days.

Actually, Postgres people tend to use "text" rather than unconstrained
"varchar".  In principle those two types behave equivalently; but the
system has to jump through some extra hoops to work with varchar, and
every so often you'll run into a case where "varchar" is not optimized
as well as "text".

                        regards, tom lane

[KenB]
Is this true – even if the column in question will be used in (or as part of) an INDEX.
It seems to be the index would work best if the length of the columns involved is a known value.

Re: [NOVICE] varchar vs varchar(n)

От
Tom Lane
Дата:
Ken Benson <Ken@infowerks.com> writes:
> On Sun, Nov 12, 2017 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
> Generally speaking, I would only use varchar(n) when there is a clear
> reason traceable to application requirements why there has to be a
> limit, and why the limit should be n and not some other number.

> Is this true – even if the column in question will be used in (or as part of) an INDEX.
> It seems to be the index would work best if the length of the columns involved is a known value.

varchar(n) doesn't have a known length.  Even char(n) doesn't have a known
length in the presence of variable-width text encodings.  So there are no
optimizations of the sort you're imagining in Postgres.
        regards, tom lane


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice