Issue with variable concatenation in postgres using copy from command

Question

I am trying to insert a number of text files with json data into a database. Each file is is suffixed with 1,2,3... etc. (shapes_routes1.json, shapes_routes2.json etc.). To do this I am concatenating an index to the base file name from within a loop. I am getting this error:

psql:insertshapes.sql:37: ERROR:  syntax error at or near "file_path"
LINE 17: copy temp_json from file_path;

Can you not supply copy from with a variable as the path? Or is there something I need to do to the variable (file_path) so psql knows its a path? Any help or recommendations on this would be appreciated.

 delete from shapes;

    DO $$

    declare
        n INTEGER := 1;
        prefix TEXT := '/Users/me/model/json/filechunks/shapes_routes';
        i TEXT := NULL;
        file_path TEXT := NULL;

    BEGIN
    LOOP 
    EXIT WHEN n = 166;
    i := CAST(n as TEXT);
    file_path := prefix || i || '.json';
    n := n + 1;

    create temporary table temp_json (values text);
    copy temp_json from file_path; --GETTING ERROR ON THIS LINE
    insert into shapes

    select  values->>'shape_id' as shape_id,
            (CAST(values->>'shape_pt_lat' as real)) as shape_pt_lat,
            (CAST(values->>'shape_pt_lon' as real)) as shape_pt_lon,
            (CAST(values->>'shape_pt_sequence' as integer)) as shape_pt_sequence,
            (CAST(values->>'shape_dist_traveled' as real)) as shape_dist_traveled,
            values->>'route_id' as route_id

    from   (
            select json_array_elements(replace(values,'\','\\')::json) as values 
            from   temp_json
           ) a;

    drop table temp_json;
    END LOOP; 
    END $$;

If the files are, like you said, shapes_routes1.json shapes_routes2.json ... Why are you concatenating a / on its name? It will make the name as /Users/me/model/json/filechunks/shapes_routes/1.json and not the names you mention... — Jorge Campos
– Jorge Campos, Commented Jul 5, 2018 at 5:40
Unrelated, but: it will be much more efficient, if you create the temp table once before the loop and truncate it before each import. — user330315
– user330315, Commented Jul 5, 2018 at 6:26

Jasen · Accepted Answer · 2018-07-05 06:56:48Z

2

COPY requires a string literal, you can't use a subselect for the filename.

if you need to you need to vary the filename you'll need to use dynamic sql, ( EXECUTE )

eg:

EXECUTE 'copy temp_json from '||quote_literal(file_path);

edited Jul 5, 2018 at 6:56

answered Jul 5, 2018 at 6:04

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ozzyzig Over a year ago

I am getting this error now after putting EXECUTE copy from etc.

ozzyzig Over a year ago

psql:insertshapes.sql:38: ERROR: relation "file_path" does not exist LINE 1: SELECT copy temp_json from file_path ^ QUERY: SELECT copy temp_json from file_path CONTEXT: PL/pgSQL function inline_code_block line 18 at EXECUTE

ozzyzig Over a year ago

Does this mean it can't find the file or it can't see variable file_path?

Jasen Over a year ago

execute can't see variables.

ozzyzig Over a year ago

Thanks man that worked! You're a champ. That was driving me bananas

Collectives™ on Stack Overflow

Issue with variable concatenation in postgres using copy from command

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related