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!
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? |