Question 288986 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Stripping out newline characters from input JSON string when using COPY Command in Postgres

I have a set of code that is importing a JSON file provided into a temp table as follows:

    DROP TABLE IF EXISTS tmp;
    CREATE TEMP  table tmp (c JSONB );
    
    sqltxt:='COPY tmp from '''||filepath||''' with (FORMAT TEXT, DELIMITER ''~'')';

Now in order to process this file sucessfully , I have to manually strip out the newline characters in the incoming file.

I would like to do this in my postgres code by using a function know as regexp_replace

I am struggling to do this

This is my attempt at this :

    DROP TABLE IF EXISTS tmp;
    CREATE TEMP  table tmp (c JSONB );

    -- Populate temp table with incoming JSON
    sqltxt:='COPY translate(tmp, E''n,'', '''') from '''||filepath||''' with (FORMAT TEXT,     DELIMITER ''~'')';
    EXECUTE sqltxt;

this produceds the following code which when run produces the error:


COPY translate(tmp, E'n,', '') from 'C:ChrisDevReadings14.json' with (FORMAT TEXT, DELIMITER '~')

[42601] ERROR: syntax error at or near "E'n,'"

How can I run this code to strip out all newline characters fr

Tags:

2

Answers


  1. 0

    — Create a temporary table
    DROP TABLE IF EXISTS tmp;
    CREATE TEMP TABLE tmp (c JSONB);

    — Populate the temporary table with incoming JSON, replacing newline characters
    sqltxt := ‘COPY translate(tmp, c) FROM ”’ || filepath || ”’ WITH (FORMAT TEXT)’;
    EXECUTE sqltxt;

    — Replace newline characters in the JSONB column using regexp_replace
    UPDATE tmp
    SET c = regexp_replace(c::TEXT, E’n’, ”, ‘g’)::JSONB;

    — Now, your temporary table (tmp) contains JSON data with newline characters removed

    Login or Signup to reply.
  2. 0

    You cannot do that in PostgreSQL. The only way to do something like that would be to call a program that modifies the file before you feed it to COPY:

    COPY tmp FROM PROGRAM $$sed -z -e 's/n/ /g' '/path/to/file'$$;

    That example is for Linux. On other operating systems, you may need to call different programs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search