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.
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)
