Copy a few of the columns of a csv file into a table

by

If it is an ad hoc task

Create a temporary table with all the columns in the input file

create temporary table t (x1 integer, ... , x10 text)

Copy from the file into it:

copy t (x1, ... , x10)
from '/path/to/my_file'
with (format csv)

Now insert into the definitive table from the temp:

insert into my_table (x2, x5, x7, x10)
select x2, x5, x7, x10
from t

And drop it:

drop table t

If it is a frequent task

Use the file_fdw extension. As superuser:

create extension file_fdw;

create server my_csv foreign data wrapper file_fdw;

create foreign table my_csv (
    x1 integer,
    x2 text,
    x3 text
) server my_csv
options (filename '/tmp/my_csv.csv', format 'csv' )
;

Grant select permission on the table to the user who will read it:

grant select on table my_csv to the_read_user;

Then whenever necessary read directly from the csv file as if it were a table:

insert into my_table (x2)
select x2
from my_csv
where x1 = 2

More Related Contents:

  1. How does the search_path influence identifier resolution and the “current schema”
  2. How do you find the row count for all your tables in Postgres
  3. Run batch file with psql command without password
  4. How do you use variables in a simple PostgreSQL script?
  5. How to make “case-insensitive” query in Postgresql?
  6. Rownum in postgresql
  7. How to switch databases in psql?
  8. Postgresql: Scripting psql execution with password
  9. dynamic sql query in postgres
  10. Cannot create a database table named ‘user’ in PostgreSQL
  11. Is there a timeout for idle PostgreSQL connections?
  12. How to make connection to Postgres via Node.js
  13. docker postgres pgadmin local connection
  14. Find difference between timestamps in seconds in PostgreSQL
  15. Unable to connect PostgreSQL to remote database using pgAdmin
  16. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  17. How can I change a PostgreSQL user password?
  18. Docker wait for postgresql to be running
  19. I forgot the password I entered during PostgreSQL installation
  20. SQL query column does not exist error
  21. Switch role after connecting to database
  22. Postgresql enforce unique two-way combination of columns
  23. When do Postgres column or table names need quotes and when don’t they?
  24. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  25. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  26. how to store PostgreSQL jsonb using SpringBoot + JPA?
  27. Orbeon Forms Postgres DB connection
  28. How to delete an enum type value in postgres?
  29. Why unsigned integer is not available in PostgreSQL?
  30. now() default values are all showing same timestamp

Leave a Comment