CODEWITHSUNDEEP
postgresql
equanimity
equanimity

Reputation: 2533

PostgreSQL error using pgAdmin4 to upload csv file

I am brand new to PostgreSQL.

I created a test table named Activity, with a column structure that looks as follows:

id    Color    Dimension    Style    COUNT    SUM    Date

I defined the columns as follows:

id: bigint, primary key, not null

Color: character varying 255

Dimension: character varying 255

Style: character varying 255

COUNT: bigint

SUM: double precision

DATE: date

My csv file has data that looks as follows:

Color   Dimension    Style    COUNT    SUM    Date
Blue    Circle       Large    4        800    6/19/2020
Blue    Circle       Small    5        1500   6/19/2020
Blue    Square       Medium   1        15     6/19/2020

In pgAdmin4, I go through the following steps:

  1. in the tree on the left-side pane, I right-click on the Activity table and select "Import/Export"
  2. on the resulting dialogue, I switch the "Export" flag to "Import"
  3. browse for the file name and select it
  4. select the "csv" format
  5. select Header = YES

After clikcing "OK", I see the following error message:

ERROR:  invalid input syntax for type bigint: "Blue"
CONTEXT:  COPY Trades, line 2, column id: "Blue"

The csv file does not contain the id column (which is the primary key) because my understanding is that PostgreSQL will automatically insert and increment that number based on the number of records I import.

Does anyone see why I'm getting the error message above?

Thanks in advance!

Upvotes: 0

Views: 1675

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19570

You are getting the error because your CSV file has one less column(id) then the table. The COPY command(which is being used by Import) has no idea that this field has a DEFAULT. In the Import dialog there should be a tab named Columns that allows you to select those columns that are in the CSV.

Upvotes: 1

Related Questions