copy from snippets

Handling invalid CSV quoting with PostgreSQL's "COPY FROM"

Tagged copy from, postgresql, quote  Languages sql

PostgreSQL “COPY FROM” not importing all CSV rows and not reporting any errors?

This is an example of how to use a character that does not exist in the CSV file as a quote character to fix and handle invalid quoting issues:

CREATE TABLE xxx (
  id SERIAL,
  name VARCHAR,
  value VARCHAR,
  PRIMARY KEY (id)
);
COPY xxx(name, value)
FROM 'xxx.txt'
DELIMITER E'\t'
-- Use a character that does not exist in the CSV file as quote character to handle quoting
QUOTE E'\x01'
ENCODING 'utf8'
CSV HEADER;

Best of luck…

How to use PostgreSQL's "COPY FROM STDIN"

Tagged copy from, postgresql  Languages bash

This is an example of how to use PostgreSQL’s “COPY FROM STDIN”:

psql xxx_development -c "COPY table1(column1, column2) FROM STDIN DELIMITER E'\t' QUOTE E'\x01' ENCODING 'ISO-8859-1' CSV HEADER;" < sub.txt

NOTE: The following example where I put the “COPY FROM STDIN” in a file does not work:

# The COPY FROM STDIN SQL statement is in a file named copy.sql. This does not work:
psql xxx_development | cat copy.sql