0

I get data files from one of our vendors. Each line is a continuous string with most places filled out. They have plenty of sections that are just space characters to be used as filler locations for future columns. I have a parser that formats it into a CSV so I can upload it into postgres. Today the vendor informs us that they are adding a column by splitting one of their filler fields into 2 columns. X and Filler

For example index 0:5 is the name, 5:20 is filler and 20:X is other stuff. They are splitting 5:20 into 5:10 and 10:20 where 10:20 will still be a placeholder column. 

 NAME1          AUHDASFAAF!@#!12312312541  -> NAME1, ,AUHDASFAAF,.....

Is now

 NAME1AAAAA     AUHDASFAAF!@#!12312312541 -> NAME1,AAAAA, ,AUHDASFAAF,......

Modifying my parser to account for this change is the easy part. How do I edit my postgres table to accept this new column from the CSV file? Ideally I dont want to remake and reupload all of the data into the table.

Sam
  • 293
  • 3
  • 19
  • Use [`alter table`](https://www.w3schools.com/SQl/sql_alter.asp) to add a new column. – Schwern Aug 27 '19 at 17:21
  • where does that column go though? From my understanding its pushed to the end and i need it in the middle.. – Sam Aug 27 '19 at 17:28
  • 1
    Your question implies you're relying on the order of the columns in the table to match the CSV. You'll have to write out the columns explicitly in the correct order in your import query (and every other insert query). Could you show how you're importing the CSV? – Schwern Aug 27 '19 at 17:53
  • This change is occurring to only one of five files i get on a daily basis. Each one has over 200 columns to it and anywhere from 200-5000 lines a day. As such I never wanted to type up an extended import line. I am simply using COPY custom.tb1 FROM 'C:/././temp/tb1.csv' DELIMITER ',' CSV; – Sam Aug 27 '19 at 18:10
  • Use information_schema.columns to generate the column list and/or the full copy command. – Belayer Aug 27 '19 at 19:22

1 Answers1

0

Columns are in the order they are defined. When you add a new column it goes at the end. There's no direct way to add a column in the middle. While insert values (...) is convenient, you should not rely on the order of columns in the table.

There are various work arounds like dropping and recreating the table or dropping and adding columns. These are all pretty inconvenient and you'll have to do it again when there's another change.

You should never make assumptions about the order of columns in the table either in an insert or select *. You can either spell out all the columns, or you can create a view which specifies the order of the columns.

You don't have to write the columns out by hand. Get them from information_schema.columns and edit their order as necessary for your queries or to set up your view.

select column_name
from information_schema.columns
where table_name = ?
Schwern
  • 153,029
  • 25
  • 195
  • 336