Chapter 4: ERROR: value too long for type character varying(3) CONTEXT: COPY us_counties_2010, line 2, column state_us_abbreviation: "STUSAB"
abdusali1234 opened this issue · 3 comments
What's your operating system (e.g. Windows 10, macOS Catalina, Ubuntu, etc.)?
Arch Linux
Did you install PostgreSQL and pgAdmin according to the steps on page xxviii of the book's Introduction? If not, please describe your installation.
I installed PostgreSQL and pgAdmin through the official Arch Linux package manager (pacman) and the Arch User Repository respectively. I have mostly been using the command line via psql, and have not used pgAdmin as much.
Which versions of PostgreSQL and pgAdmin 4 are you using? If you aren't sure, for PostgreSQL run the SQL command SELECT version(); and for pgAdmin 4, navigate to "About pgAdmin 4" under the "Help" menu.
PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.1.0, 64-bit
Did you download the book's code examples and data from GitHub using the directions on page xxvii of the Introduction? If not, please describe how you loaded the material on your computer.
Yes
Issue or Question
Chapter, page and code listing number:
Chapter 4, listing 4-3
Describe the issue or your question:
I seem to be unable to copy the US_counties_2010 csv file, despite copying the sql code and downloading the csv file.
Please paste the code and error message here. It's OK to alter identifying info such as a folder name
CREATE TABLE us_counties_2010 (
geo_name varchar(90), -- Name of the geography
state_us_abbreviation varchar(2), -- State/U.S. abbreviation
summary_level varchar(3), -- Summary Level
region smallint, -- Region
division smallint, -- Division
state_fips varchar(2), -- State FIPS code
county_fips varchar(3), -- County code
area_land bigint, -- Area (Land) in square meters
area_water bigint, -- Area (Water) in square meters
population_count_100_percent integer, -- Population count (100%)
housing_unit_count_100_percent integer, -- Housing Unit count (100%)
internal_point_lat numeric(10,7), -- Internal point (latitude)
internal_point_lon numeric(10,7), -- Internal point (longitude)
-- This section is referred to as P1. Race:
p0010001 integer, -- Total population
p0010002 integer, -- Population of one race:
p0010003 integer, -- White Alone
p0010004 integer, -- Black or African American alone
p0010005 integer, -- American Indian and Alaska Native alone
p0010006 integer, -- Asian alone
p0010007 integer, -- Native Hawaiian and Other Pacific Islander alone
p0010008 integer, -- Some Other Race alone
p0010009 integer, -- Population of two or more races
p0010010 integer, -- Population of two races:
p0010011 integer, -- White; Black or African American
p0010012 integer, -- White; American Indian and Alaska Native
p0010013 integer, -- White; Asian
p0010014 integer, -- White; Native Hawaiian and Other Pacific Islander
p0010015 integer, -- White; Some Other Race
p0010016 integer, -- Black or African American; American Indian and Alaska Native
p0010017 integer, -- Black or African American; Asian
p0010018 integer, -- Black or African American; Native Hawaiian and Other Pacific Islander
p0010019 integer, -- Black or African American; Some Other Race
p0010020 integer, -- American Indian and Alaska Native; Asian
p0010021 integer, -- American Indian and Alaska Native; Native Hawaiian and Other Pacific Islander
p0010022 integer, -- American Indian and Alaska Native; Some Other Race
p0010023 integer, -- Asian; Native Hawaiian and Other Pacific Islander
p0010024 integer, -- Asian; Some Other Race
p0010025 integer, -- Native Hawaiian and Other Pacific Islander; Some Other Race
p0010026 integer, -- Population of three races
p0010047 integer, -- Population of four races
p0010063 integer, -- Population of five races
p0010070 integer, -- Population of six races
-- This section is referred to as P2. HISPANIC OR LATINO, AND NOT HISPANIC OR LATINO BY RACE
p0020001 integer, -- Total
p0020002 integer, -- Hispanic or Latino
p0020003 integer, -- Not Hispanic or Latino:
p0020004 integer, -- Population of one race:
p0020005 integer, -- White Alone
p0020006 integer, -- Black or African American alone
p0020007 integer, -- American Indian and Alaska Native alone
p0020008 integer, -- Asian alone
p0020009 integer, -- Native Hawaiian and Other Pacific Islander alone
p0020010 integer, -- Some Other Race alone
p0020011 integer, -- Two or More Races
p0020012 integer, -- Population of two races
p0020028 integer, -- Population of three races
p0020049 integer, -- Population of four races
p0020065 integer, -- Population of five races
p0020072 integer, -- Population of six races
-- This section is referred to as P3. RACE FOR THE POPULATION 18 YEARS AND OVER
p0030001 integer, -- Total
p0030002 integer, -- Population of one race:
p0030003 integer, -- White alone
p0030004 integer, -- Black or African American alone
p0030005 integer, -- American Indian and Alaska Native alone
p0030006 integer, -- Asian alone
p0030007 integer, -- Native Hawaiian and Other Pacific Islander alone
p0030008 integer, -- Some Other Race alone
p0030009 integer, -- Two or More Races
p0030010 integer, -- Population of two races
p0030026 integer, -- Population of three races
p0030047 integer, -- Population of four races
p0030063 integer, -- Population of five races
p0030070 integer, -- Population of six races
-- This section is referred to as P4. HISPANIC OR LATINO, AND NOT HISPANIC OR LATINO BY RACE
-- FOR THE POPULATION 18 YEARS AND OVER
p0040001 integer, -- Total
p0040002 integer, -- Hispanic or Latino
p0040003 integer, -- Not Hispanic or Latino:
p0040004 integer, -- Population of one race:
p0040005 integer, -- White alone
p0040006 integer, -- Black or African American alone
p0040007 integer, -- American Indian and Alaska Native alone
p0040008 integer, -- Asian alone
p0040009 integer, -- Native Hawaiian and Other Pacific Islander alone
p0040010 integer, -- Some Other Race alone
p0040011 integer, -- Two or More Races
p0040012 integer, -- Population of two races
p0040028 integer, -- Population of three races
p0040049 integer, -- Population of four races
p0040065 integer, -- Population of five races
p0040072 integer, -- Population of six races
-- This section is referred to as H1. OCCUPANCY STATUS
h0010001 integer, -- Total housing units
h0010002 integer, -- Occupied
h0010003 integer -- Vacant
);
When I use the COPY command it says permission denied, so I have been using the \copy command instead, which worked for me in chapter 3.
\copy us_counties_2010 FROM '/home/xxxx/xxxxx/xxxx/us_counties_2010.csv' WITH (FORMAT CSV, HEADER)
I get the following error:
ERROR: value too long for type character varying(2)
CONTEXT: COPY us_counties_2010, line 2, column state_us_abbreviation: "STUSAB"
I have tried changing the character length limit for STUSAB but I still get the same error.
Hi, please confirm that you are reading/using the 1st Edition of Practical SQL. If you're using the 2nd Edition, please visit that repo at https://github.com/anthonydb/practical-sql-2 for the correct code and data files.
Can confirm that I'm reading the 1st edition.
SOLVED
The first line of my CSV file was entirely blank :p . I removed it and now it works!