Create loop to create PostgreSQL columns

Question

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

score 3 · Accepted Answer · edited May 23 '17 at 11:44

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:

How to implement a many-to-many relationship in PostgreSQL?

edited May 23 '17 at 11:44

Community

1
1

answered Dec 05 '15 at 14:23

Erwin Brandstetter

605,456
145
1,078
1,228

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? – Erwin Brandstetter Dec 05 '15 at 14:24
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 Dec 05 '15 at 15:13
@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. – Erwin Brandstetter Dec 05 '15 at 15:31

score 0 · Answer 2 · edited May 23 '17 at 12:04

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.

edited May 23 '17 at 12:04

Community

1
1

answered Dec 05 '15 at 12:18

Juan Carlos Oropeza

47,252
12
78
118

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 Dec 05 '15 at 12:24
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 Carlos Oropeza Dec 05 '15 at 12:28
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? – Peter Dec 05 '15 at 12:52
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. – Juan Carlos Oropeza Dec 05 '15 at 14:13

Create loop to create PostgreSQL columns

2 Answers2