Postgres copy to select * with a type cast

Question

I have a group of two SQL tables in postgres. A staging table and the main table. Among the variety of reasons for the staging table, the data i am uploading has irregular and different formats for all of the date columns. During the upload process these values go into staging table as varchars to be manipulated into usable formats.

In the main table the column type for the date fields is of type 'date' in the staging table they are of type varchar.

The question is, does postgres support a copy expression similar to

insert into production_t select *,textdate::date from staging_t

I need to change the format of a single field during the copy process. I know i can individually type out all of the column names during the insert and typecast the date columns there, but this table has over 200 columns and is one of 10 tables with similar issues. I want to accomplish this insert+typecast in one line that i can apply to all tables rather than having to type 2000+ lines of sql queries.

SQL? DRY need not apply. You could probably do this dynamically by querying the system tables for the columns but after you've done all that it probably would have been faster to script out 10 tables and copy and paste your type cast to the necessary places. Maybe someone will be kind enough to write it out for you though. — Jacob H
– Jacob H, Commented Feb 5, 2018 at 14:42
I didnt mention this in the original post,I don't mind typing it. I'm interacting with psotgres through python. and I dont want to have to fit static queries into my code. right now i use a one line copy query for all tables and i only switch out the table names. I want to keep it as clean as possible. — Sam
– Sam, Commented Feb 5, 2018 at 14:49

Laurenz Albe · Accepted Answer · 2018-02-05 15:54:51Z

1

You have to write every column of such a query, there is no shorthand.

May I say that a design with 200 columns is questionable.

answered Feb 5, 2018 at 15:54

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

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

1 Comment

Sam Over a year ago

I know...200 columns is excessive... but that's how someone at Fidelity decided to format the data. Most of the time half or more of the 200 columns are Null.

Collectives™ on Stack Overflow

Postgres copy to select * with a type cast

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related