0

I have a dataset of 320 columns in a csv file where the header is simply the number of trip.

Each column represents a trip, a route of street names from A to B. (It was a simple list, that I transposed in excel to create the trip numbers as heads.)

I would like to import it to a postgreSQL table.

I saw previous similar question, but as it's only 320, I wonder if that is the best structure and if yes how to loop the column creation in order to insert the data through pgAdmin.

I figured so far this, that returns error message:

DO
$do$
BEGIN 
FOR i IN 1..320 LOOP
   INSERT INTO runs (col_i, col_id) -- use col names
   SELECT i, id
   FROM   tbl;
END LOOP;
END
$do$;

Many thanks

Improve this question
0

2 Answers 2

Reset to default
3

To create a table with 320 text columns:

DO
$$BEGIN
EXECUTE (
   SELECT 'CREATE TABLE my_tbl (c'
        || string_agg(g::text, ' text, c')
        || ' text)'
   FROM generate_series(1,320) g);
END$$;

A sane relational design might be (wild guess, there's not nearly enough information):

CREATE TABLE street (
  street_id serial PRIMARY KEY
, street    text NOT NULL
);

CREATE TABLE trip (
  trip_id  serial PRIMARY KEY
, whatever text
);

CREATE TABLE trip_step (
  trip_id   int REFERENCES trip
, step      int
, street_id int NOT NULL REFERENCES street
, PRIMARY KEY (trip_id, step)
);

Consider:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

But a table with that many columns is typically a big misunderstanding. Are you sure you didn't mean 320 rows consisting of 2 columns or something?
Thanks Erwin. Maybe I should have a different data structure. what would you suggest? These are all street names, so same data type, but each column represents a separate route.
@Peter: There's not enough information, I can only guess. See update. For anything more I suggest you start a new question about the design, where you provide all the necessary information.
0

Depending on your datatype max number of columns go from 250 to 1600, so that may be a problem

What is the maximum number of columns in a PostgreSQL select query

If you already have your data in excel, you can use save as CSV and use COPY to import the data

https://stackoverflow.com/a/2987451/3470178

OR use string concatenation to create INSERT statmente in excel.

Improve this answer

4 Comments

I did try the copy but it returns error: ERROR: extra data after last expected column SQL state: 22P04 Context: COPY runs, line 1: "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,3..." I assume because there is no column identified yet? I created table "runs" but no columns. My data is simple text, with first line header can be INT.
Peter try to create a small sample with 10 columns to make sure the problem isnt the number of columns and validate you have proper sintaxis. If still get sintaxis error you need to provide a full example so we can help. And you need create the columns on the csv the link i give you say how.
Juan, I successfully imported 3 columns when I created the columns beforehand. So the question returns: how can I create 320 columns as a loop so that I can import the csv?
Are the column names some kind of sequence? why not just create the columns by hand if the table exists using ALTER TABLE distributors ADD COLUMN address varchar(30);. If the table doesnt exist the link i provide will create the columns for you if the csv has a header.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.