Creating a table with PSQL as clone of another table but excluding a column

Question

Short Version: What psql command should I use to create temporary table, which is a clone of a given table, excluding a given column?

create temp table bar_temp as <columns from foo.bar excluding 'insert_date'>

Reasoning: I have some very large CSV files I want to bulk upload into a Postges DB (currently 8.4 - migrating to 9.x).

When uploading these CSV files I want to add an additional field for example a "date" field.

Using this post as inspiration: (How to update selected rows with values from a CSV file in Postgres?) I'm trying to do the following:

1. Create a temp table from the schema of the target table excluding the "date" column.
2. Run '\COPY' command to upload into the temp table.
3. Update the entries in the target table with the entries in the temp table + the additional date column entry.

This is all straight forward except for step 1. If the column names are known in advance this line can be hard coded. In my case I have a number of similar input files with corresponding tables and want the solution to be flexible to handle changes in schema.

My hacky solution is to have a pre-processing script that determines the temp table schema then writes that CREATE command to the SQL script before running, eg:

"SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name FROM information_schema.columns As c WHERE table_name = '$table' AND c.column_name NOT IN('dt')), ',') || ' FROM $schema.$table As o'"

which gives me something like

SELECT o.name,o.address from foo.bar As o

which can then be used in the create statement

create temp table temp_tbl as SELECT o.name,o.address from foo.bar As o

However I think that there should be away of doing this schema discovery step within PSQL script itself. I've looked at using "EXEC" but as far as I can see I would need to put this in a function, the return type of this needs to list the table columns!

Any suggestions?

Answer 1

You could do something like this:

create table foo like bar;
alter table foo drop column baz;
-- and now full the thing