Uploading csv file in docker using Postgres

Question

I have an unconventional question. I have established a db in Postgres

psql -h 192.168.99.100 -p 15432 -U postgres

And created a table in the db using:

$ docker=# CREATE TABLE cities (
docker(#     name            varchar(80),
docker(#     location        point
docker(# );

However, I haven't been able to upload a csv into the table I've created. Can you please show me how to do it? (I am using Docker Command window to do this) Thanks in advance.

Answer 1

Here's a sample of copying lat/lon points in a CSV to the cities table using psql inside of a container.

# Sample CSV data
echo "somecity",45,-45 > cities.csv

# Create database
docker run --name postgres -p 15432:5432 -d postgres

# Create cities table and a temp table for loading point coordinates
# Uses host network to access database published on port 15432 of the host
docker run --rm --network=host postgres psql -h localhost -p 15432 -U postgres -c '
  CREATE TABLE temp (
    name varchar(80),
    latitude numeric(12,8),
    longitude numeric(12,8)
  );
  CREATE TABLE cities (
    name varchar(80),
    location point
  );'

# \copy data from file (mounted by volume)
docker run --rm --network=host -v `pwd`:/data postgres \
  psql -h localhost -p 15432 -U postgres -c \
    "\\copy temp FROM '/data/cities.csv' WITH csv"

# Insert into cities creating point from coordinates
docker run --rm --network=host postgres psql -h localhost -p 15432 -U postgres -c "
  INSERT INTO cities (name, location)
  SELECT name, point(temp.latitude,temp.longitude) 
  FROM temp;
  DROP TABLE temp"

# Show the point
docker run --rm --network=host postgres psql -h localhost -p 15432 -U postgres -c \
  "SELECT * FROM cities;"

The last command outputs:

  name   | location
----------+----------
 somecity | (45,-45)
(1 row)