Command to read a file and execute script with psql

Question

I am using PostgreSQL 9.0.3. I have an Excel spreadsheet with lots of data to load into couple of tables in Windows OS.

I have written the script to get the data from input file and Insert into some 15 tables. This can't be done with COPY or Import. I named the input file as DATALD.
I find out the psql command -d to point the db and -f for the script sql. But I need to know the commands how to feed the input file along with the script so that the data gets inserted into the tables..

For example this is what I have done:

begin
for emp in (select distinct w_name from DATALD where w_name <> 'w_name') 
--insert in a loop 
INSERT INTO tblemployer( id_employer, employer_name,date_created, created_by) 
  VALUES (employer_id,emp.w_name,now(),'SYSTEM1');

Can someone please help?

You seem to have written an SQL script. How do you imagine data will be imported to Postgres without using `COPY`? — Erwin Brandstetter, Jul 16 '13 at 20:47
I dont know.. I thought COPY is for one file to one table. for example this is what I have done. begin for emp in (select distinct w_name from DATALD where w_name <> 'w_name') --insert in a loop INSERT INTO tblemployer( id_employer, employer_name,date_created, created_by) VALUES (employer_id,emp.w_name,now(),'SYSTEM1'); — user2486675, Jul 16 '13 at 20:55
All of this (and more, like the Postgres version number) should go into the question, not into comments. Click `edit` under your question. — Erwin Brandstetter, Jul 16 '13 at 21:09
"PostgreSQL 9.0.3". Um. http://postgresql.org/support/versioning/ . Update to 9.0.13, you're missing a good three years of fixes. — Craig Ringer, Jul 17 '13 at 02:07
*"This can't be done with COPY or Import"*. **Why not?** That's the most sensible first choice. — Craig Ringer, Jul 17 '13 at 02:09

score 1 · Answer 1 · edited May 23 '17 at 12:11

For an SQL script you must ..

either have the data inlined in your script (in the same file).
or you need to utilize COPY to import the data into Postgres.

I suppose you use a temporary staging table, since the format doesn't seem to fit the target tables. Code example:
How to bulk insert only new rows in PostreSQL

There are other options like pg_read_file(). But:

Use of these functions is restricted to superusers.

Intended for special purposes.

Command to read a file and execute script with psql

1 Answers1