varchar vs varchar(n)

From: john snow <ofbizfanster(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: varchar vs varchar(n)
Date: 2017-11-12 19:19:20
Message-ID: CAE67tvU_AzJvh6LxFDBewyUq-BhC1Aw=OZkNGnc-RJ2WKiNZ6A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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!

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2017-11-12 19:34:52 Re: varchar vs varchar(n)
Previous Message Tom Lane 2017-11-09 03:56:19 Re: what does t(x) in select x from generate_series(1, 10) as t(x) stand for?