Is there a way in SQL to import ALL .csv contained in a directory to my postgres table? (window os)

Question

Is there a way in SQL to import ALL csv contained in a directory to my postgres table ?

Thank you for your help.

Answer 1

This isn't PostgreSQL specific at all. You need to use a loop to invoke psql once per file, generate a master file that contains \include commands for each input file, or concatenate all the input files and then run them.

All of these tasks are done with the operating system command prompt tools, they're not to do with PostgreSQL.

To concatenate the files and run the result:

type *.sql | psql

To loop over them using cmd.exe's amazingly, incredibly ugly for loop syntax (untested, see How to loop through files matching wildcard in batch file and Iterate all files in a directory using a 'for' loop for details):

FOR %%f IN (*.sql) DO (
  echo %%~nf
  psql -f %%~nf
)

I'm sure the 3rd way is possible too. This might work (the equivalent works in a real shell on a unix box) but is untested as I don't have Windows conveniently to hand:

FOR %%f IN (*.sql) DO (
  echo "\include %%~nf"
) | psql

As far as I know none of these guarantee that the files are run in any particular order. You'd need to look up the documentation on the Windows shell.

Personally - I recommend doing this sort of thing in Powershell, or in Perl or some other less awful scripting language than the cmd.exe batch language.

Either way, all the files must have the system default 1-byte text encoding or must contain an explicit SET client_encoding= ... statement.