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!