Psql игнорировать ошибки

score:20

score:1

#!/bin/bash

input_file=./my_input.csv
tmp_file=/tmp/one-line.csv
cat $input_file | while read input_line; do
    echo "$input_line" > $tmp_file
    psql my_database 
     -c "
     COPY my_table 
     FROM `$tmp_file` 
     DELIMITER '|'
     CSV;
    "
done

score:1

#!/bin/bash
bad_line_number=5  # assuming line 5 is the bad line
sed ${bad_line_number}d < input.csv > filtered.csv

score:5

    CREATE OR REPLACE FUNCTION on_insert_in_original_table() RETURNS trigger AS  $$  
    DECLARE
        v_rec   RECORD;
    BEGIN
        -- we use the trigger to prevent 'duplicate index' error by returning NULL on duplicates
        SELECT * FROM original_table WHERE primary_key=NEW.primary_key INTO v_rec;
        IF v_rec IS NOT NULL THEN
            RETURN NULL;
        END IF; 
        BEGIN 
            INSERT INTO original_table(datum,primary_key) VALUES(NEW.datum,NEW.primary_key)
                ON CONFLICT DO NOTHING;
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        RETURN NULL;
    END;

More questions

• How to use PROGRAM clause of Postgres COPY command with Powershell command
• How to preserve new line character while performing psql copy command
• Postgres — How to use psql to create a database with the -c command and password authentication?
• How to log the output along with error messages to a file while running a script on psql command line on Freebsd OS?
• Postgresql dynamic query in COPY stament with psql command
• PostgreSQL — how to use copy command (importing text file) when the line ends with delimiter
• How do I copy data from a CSV dump into a PSQL database with a different structure?
• how to insert filenames with single quotes in a postgresql COPY command from delphi, parameters?
• How to PSQL COPY with PYTHON
• How to exit from PostgreSQL command line utility: psql
• Docker — How can run the psql command in the postgres container?
• How do I copy data from one table to another in postgres using copy command
• How to make a select with array contains value clause in psql
• How to copy from CSV file to PostgreSQL table with headers in CSV file?
• How to execute multiple queries using psql command from bash shell?
• In PostgreSQL, how to insert data with COPY command?
• How to make Postgres Copy ignore first line of large txt file
• Run batch file with psql command without password
• Is it possible to turn off quote processing in the Postgres COPY command with CSV format?
• How to use the psql command to list, create, use and examine databases?
• How Postgresql COPY TO STDIN With CSV do on conflic do update?
• How to copy structure of one table to another with foreign key constraints in psql?
• how to set up the psql command in cygwin?
• copy command with psycopg2 library

More questions with similar tag

• How to pass typed array as argument in PostgreSQL?
• Insert batch in postgres from list of lists using python and sql alchemy
• Lock multiple rows but get only one row as returned result
• Django Heroku DataError when populating database from script
• Regex to check if all the field is within brackets
• How to count repeating values in a column in PostgreSQL?
• connecting to PostgreSQL over a network
• What happens when AWS RDS CPU utilisation reaches100%?
• Graceful Primary Key Error handling in Python/psycopg2
• How to resume on error during csv import in Postgresql
• Slow Inserts in PostgreSQL
• Sequelize-auto for Postgres
• how to Insert multiple columns using PQputCopyData
• Column naming in Postgresql functions
• PostgreSQL on Elastic Beanstalk (Amazon Linux 2)
• Difference in sequence of query generated in Django and Postgres for select_for_update
• PostgreSql: order result — II
• PostgreSQL primary key length limit
• Is there a shared query plan cache for Postgres?
• Insert multiple rows across 3 tables in 1 query using returned id
• How to update table on postgres with join statement
• Postgres: Error using GROUP BY and ORDER (on heroku)
• postgresql select distinct on column but only if another column doesn’t contain a value
• how can i get the sum of two colums realtime
• malformed array literal — PostgreSQL
• Batch Insert from Dataframe to DB ignoring failed row in Pyspark
• Bit masking in Postgres
• Generate series of months in a column at postgresql
• postgres hierarchy — count of child levels and sort by date of children or grandchildren
• Python, SQLAlchemy and Postgresql: understanding inheritance

It is too bad that in 25 years Postgres doesn’t have -ignore-errors flag or option for COPY command. In this era of BigData you get a lot of dirty records and it can be very costly for the project to fix every outlier.

I had to make a work-around this way:

1. Copy the original table and call it dummy_original_table
2. in the original table, create a trigger like this:

    CREATE OR REPLACE FUNCTION on_insert_in_original_table() RETURNS trigger AS  $$      DECLARE        v_rec   RECORD;    BEGIN        -- we use the trigger to prevent 'duplicate index' error by returning NULL on duplicates        SELECT * FROM original_table WHERE primary_key=NEW.primary_key INTO v_rec;        IF v_rec IS NOT NULL THEN            RETURN NULL;        END IF;         BEGIN             INSERT INTO original_table(datum,primary_key) VALUES(NEW.datum,NEW.primary_key)                ON CONFLICT DO NOTHING;        EXCEPTION            WHEN OTHERS THEN                NULL;        END;        RETURN NULL;    END;

1. Run a copy into the dummy table. No record will be inserted there, but all of them will be inserted in the original_table

psql dbname -c copy dummy_original_table(datum,primary_key) FROM '/home/user/data.csv' delimiter E't'

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters