1

I have a folder in a directory in my PC which contains multiple SQL files. Each of the file is a Postgres function. I want to execute every SQL file situated in the folder at a time in PostgreSQL server using PgAdmin or in other way. How can I accomplish this?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Alien
  • 121
  • 1
  • 1
  • 11
  • Have you considered merging these files into a single one, which is fairly straightforward, and then executing it? ;) – Jim Jones Dec 21 '22 at 11:12
  • I thought about it but don't know how to do. I want a process by which I can execute all SQL files in PostgreSQL server. There are 500+ sql files. Currently I have to execute each sql file individually. This is too time consuming. – Alien Dec 21 '22 at 11:17

1 Answers1

1

I apologize if I'm oversimplifying your question, but if the main issue is how to execute all SQL files without having to call them one by one, you just need to put them in a loop, e.g. in bash calling psql

#!/bin/bash

for f in *.sql
do
  psql -h dbhost -d db -U dbuser -f $f
done

Or cat them and pipe the result to psql stdin:

$ cat /path/to/files/*.sql | psql -h dbhost -d db -U dbuser

And if you need them to run in a single transaction, consider merging the SQL files, e.g. using cat - this assumes all statements in your sql file are properly terminated:

$ cat /path/to/files/*.sql > merged.sql
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • where to execute the command? Is this solution for windows? My OS is windows.. – Alien Dec 22 '22 at 09:50
  • 1
    @Alien those are linux commands. Look for the equivalent on Windows (`cat` -> `type`?). I haven't touched Windows in ~20 years. – Jim Jones Dec 22 '22 at 09:52
  • where to execute the command? In CMD? – Alien Dec 22 '22 at 10:05
  • @Alien yes, in the command line terminal of Windows. There is certainly a way to emulate bash commands using windows though: https://www.geeksforgeeks.org/use-bash-shell-natively-windows-10/ – Jim Jones Dec 22 '22 at 10:12
  • and the CMD should be opened under the bin folder where PostgreSQL is installed? like `C:\Program Files\PostgreSQL\15\bin`? – Alien Dec 22 '22 at 11:01
  • @Alien no, not necessarily. As long as the psql path in the system variables is properly set, there is no need to be in the bin folder. – Jim Jones Dec 22 '22 at 11:04
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/250602/discussion-between-alien-and-jim-jones). – Alien Dec 22 '22 at 11:06
  • so can I execute the command using git bash? – Alien Dec 22 '22 at 11:07
  • @Alien you have to execute these commands in the Linux bash shell. Since you're on windows, you have to emulate it somehow, as Windows does not use it natively. If I may share a piece of advice -> never run postgres on Windows. – Jim Jones Dec 22 '22 at 11:12
  • I am just running postgres on windows in localhost. – Alien Dec 22 '22 at 11:14
  • @Alien still it is IMHO a bad idea, as the engine's behaviour might differ from production if it uses Linux (as it should). I've heard a few times something called powershell for windows, you might wanna take a look at that. – Jim Jones Dec 22 '22 at 11:19
  • Did it in windows CMD. The equivalent of cat in windows is type. you are right. Thanks a lot @Jim Jones. You saved my times.... – Alien Dec 22 '22 at 11:30
  • @Alien I'm glad it helped. You can use the same principle to do many things using psql, e.g. this other answer of mine on `copy`: https://stackoverflow.com/a/59304818/2275388 – Jim Jones Dec 22 '22 at 11:33
  • thanks. But unfortunately it executed 669 filed from 776. rest of the files did not get executed. – Alien Dec 22 '22 at 11:39
  • @Alien well, it means that either there were errors in the files or they had a different extension `.sql` != `.SQL` – Jim Jones Dec 22 '22 at 12:45
  • @ Jim Jones Every files ended with `.sql` . Is there any way to identify if there any error in files? Or can I find out files which are not executed? – Alien Dec 22 '22 at 12:50
  • @Alien normally psql shows the errors (if any) in the console itself if something happens. But you can always redirect the errors to `stderr`, e.g. https://stackoverflow.com/a/55862605/2275388 – Jim Jones Dec 22 '22 at 13:22