how to put my two psql lines together in a batch file in order to have them executed.?

Posted on 2012-09-10
Last Modified: 2012-09-10
hello experts,

i'd like to create a batch file on windows, that when i click on it, it connects to postgresql and export with COPY a selection into a csv file.

I have my connection line and my export line, but if i paste them in a .bat file and launch the batch, only the connection is processed. The output file is not written, i  have the psql prompt that shows that i'm connected to the db but i'ts not what i expect.
i'd like my first line to connect, my second does the exports.

what am i doing wrong ? how can i correct this. ?

inside my .bat file.
psql -h localhost -U User -d my_db
COPY public.rs_container_today FROM 'C:/folder/today_container_status.csv' WITH DELIMITER '
,' ;
Question by:toshi_
    LVL 11

    Accepted Solution

    To run a single SQL command, I think you want:
      psql -h localhost -U User -d my_db -c "COPY ..."

    Author Comment

    hello tel2,

    thank you for your answer,
    this case i exposed has only one command.

    Do you think this is possible for several commands ?
    i'm going to give it a try,


    LVL 11

    Expert Comment

    Hi toshi_,

    As I understand it, connecting in the way you did, will put you into psql interactive mode, and I doubt that the next command in the batch file will run there, as batch file commands are run on the DOS shell (or whatever it's called these days).

    And if you ever want to run multiple SQL commands after connecting, have a look at the "psql automated shell examples" section near the bottom of this psql cheat sheet:
    Especially the "Run an sql batch script against a database" part.

    Author Comment

    thank you very much,
    LVL 11

    Expert Comment

    Thanks for the points, toshi_ (or can I just call you "_" for short?).

    At a guess, this might also work:
        TYPE command_file.sql | psql -h localhost -U User -d my_db
    where the command_file.sql contains your SQL commands.  I'm not in a position to test it.  But using the "-c" option is probably easier, and should definitely work.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
    Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now