CODEWITHSUNDEEP
sqlpostgresql
Sam
Sam

Reputation: 323

Postgres copy to select * with a type cast

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.

Upvotes: 0

Views: 668

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247260

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.

Upvotes: 1

Related Questions