I'm successfully inserting
csv files to
postgresql with the following command:
\COPY tablename(col1, col2, col3) FROM '/home/user/mycsv.txt' WITH CSV HEADER DELIMITER ';' NULL AS 'null';
However, I'd like to write some metadata inside this
csv file with data that's repeatable. I know I could create a different file to store this data but I think it'd be a lot more convenient to store this metadata in the same
csv file where the majority of the data is stored. I imagine a file like the following:
-- commented line with some metadata col1;col2;col3 value;value;value value;value;value value;value;value
I've tried using
/* /* and
# as comments but the
\copy command fails to import the data when I do that. Is there any way that I can tell the
psql command to see specific lines as comments just so I can insert data with lines that are not a part of the
csv file? Is it possible?
Use the FROM PROGRAM construct to tell something else to filter them out.
\COPY tablename(col1, col2, col3) FROM PROGRAM 'egrep -v "^-- " mycsv.txt' WITH CSV HEADER DELIMITER ';' NULL AS 'null'
You see how pg_dump does it:
pg_dump -d test -U postgres -t orders -a -f test.sql cat test.sql -- -- PostgreSQL database dump -- -- Dumped from database version 12.3 -- Dumped by pg_dump version 12.3 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; -- -- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.orders (order_id, total, order_date, user_id) FROM stdin; 1 100 2020-06-20 00:00:00 1 2 250 2020-06-20 00:00:00 2 \. -- -- Name: orders_order_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.orders_order_id_seq', 2, true); -- -- PostgreSQL database dump complete -- psql -d test -U postgres -f test.sql Null display is "NULL". SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET COPY 2 setval -------- 2 (1 row)