create tables with a bash file from (Postgres) sql select statement stored in files

Question

Given a set of complex, consecutive (Postgres) SQL select statements, each stored in a single sql-file, how can I write a bash-script dropping (if exists) and creating a table with the results of each statement having the same table name as file name.

background: we share these sql-files in a git repo where different users want to use the statements in a different way. I want automatically create tables, the others use temp tables, thus I dont want to write the 'create table...' in the sql-file's headers.

I don't know much about bash scripting, but something like this might work: loop over all files, "echo" a drop table <filename> to an intermediate SQL script. At the same time echo a create table <filename> to another script and append the contents of the "current" file to that "header". Then run all the drop scripts and the create scripts. — user330315
– user330315, Commented Nov 30, 2013 at 14:04
In most cases, the order in which you submit the separate .sql files will be important, so you cannot automate this tasks wihtout having a summary/toc file/script that dictates this order. (a naming convention might help, but can lead to extreme uglyness) — wildplasser
– wildplasser, Commented Nov 30, 2013 at 14:15
You are right, the order is important. So, how can i create tables with the mentioned summary/toc file/script?? — Berlin_J
– Berlin_J, Commented Nov 30, 2013 at 18:15

Daniel Vérité · Accepted Answer · 2013-12-02 14:59:36Z

1

A skeleton for your shell script could look like this:

set -e  # stop immediately on any error
for script in s1 s2 s3
do
  echo "processing $script"
  select=`cat $script`
  psql -d dbname -U user <<EOF
DROP TABLE IF EXISTS "$script";
CREATE TABLE "$script" AS $select ;
EOF
done

Note however that any SELECT is not necessarily suitable as the source for a CREATE TABLE .. AS SELECT...

As the simplest example, consider the case when two different columns share the same name. This is legal in a SELECT, but an error condition when creating a table from it.

edited Dec 2, 2013 at 14:59

answered Nov 30, 2013 at 19:36

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

1 Comment

Berlin_J Over a year ago

thanks, that's it! for giving the tables just the file's names (without path and extension) i used:stackoverflow.com/questions/3362920/…

Collectives™ on Stack Overflow

create tables with a bash file from (Postgres) sql select statement stored in files

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related