Is it possible to make the psql \copy see a line inside a csv file as a comment?

user_4572853 0
Viewed 647

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 \copy 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?

postgresql-copy psql postgresql
2 Answers

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'
user_17212390

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)
user_70706130
Related