How to run PostgreSQL commands inside docker container from host?

Question

I need to run a bunch of PostgreSQL commands/queries running as a Docker container. I have something similar in MySQL which works but I can't seem to figure out a way to do the same for a PostgreSQL database:

# MySQL
docker exec $CONTAINER mysql -u$MYSQL_USER -p$PASSWORD -e "USE $DATABASE;"

When I tried something similar for PostgreSQL:

# PostgreSQL
docker exec -i db psql -h localhost -U postgres -d app -e "SELECT * FROM users;"

I get the following error:

psql: warning: extra command-line argument "SELECT * FROM users;" ignored

Answer 1

Suppose that you launch your PostgreSQL container like this:

docker run --rm --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=password -d postgres

You can submit a query directly using psql in the container:

docker exec -i postgres psql -U postgres -d postgres -c "SELECT * FROM pg_user;"

You can also just submit a query directly from the host. Note that you need to expose a port when you launch your PostgreSQL container using -p 5432:5432.

export PGPASSWORD='password'
psql -h localhost -U postgres -d postgres -c "SELECT * FROM pg_user;"