Copy data from one table to another table in Postgres which contains many Columns

Question

I want to copy data from Table A to Table B in Postgres. Table A contains 40 columns and Table B contains 20 columns. It's like Table B is the subset of Table A, which means - Table B contains only some columns which are in Table A.

I have found the answer https://stackoverflow.com/a/7483174/12556735 for copying the data if there are less number of columns.

Since there are many columns , is there any way in which we can copy data without mentioning the Column names?

Without mentioning the column names I'm afraid there is no way of PostgreSQL knowing which data you want to copy to other tables. — Jim Jones, Feb 07 '20 at 07:29
The question is not about "WHY" but about "HOW". My problem is very broad and I asked it in a simplified manner. — Bhanu Prakash, Feb 07 '20 at 09:16
'is there any way in which we can copy data without mentioning the Column names?' - no — P.Salmon, Feb 07 '20 at 09:25

score 0 · Answer 1 · answered Feb 11 '20 at 16:19

Choice 1: If you notice, your referred question / answer itself answers your question as does not have any limitation on the number of columns. But you should know the original column definition and the required column list. It can run well for many columns.

Choice 2: If the columns are unknown, you shall try using

create table 'newtable' as (select * from 'existingtable');

Choice 3: If the columns are unknown and you wanted to create a new table with selected columns ! (which means you should know about columns), you shall try

select * from information_schema.columns where table_schema= 'yourdatabase' and table_name= 'yourtable';

And among the columns list, you shall use column_name, column_type, is_nullable, etc can be used on your script.

Copy data from one table to another table in Postgres which contains many Columns

1 Answers1