Postgres COPY FROM csv file- No such file or directory



  • I'm trying to import a (rather large) .txt file into a table geonames in PostgreSQL 9.1. I'm in the /~ directory of my server, with a file named US.txt placed in that directory. I set the search_path variable to geochat, the name of the database I'm working in. I then enter this query:

    COPY geonames
    FROM 'US.txt',
    DELIMITER E'\t',
    NULL 'NULL');
    
    

    I then receive this error:

    ERROR: could not open file "US.txt" for reading: No such file or directory.
    
    

    Do I have to type in \i US.txt or something similar first, or should it just get it from the present working directory?



  • A couple of misconceptions:

    1.

    I'm in the /~ directory of my server

    There is no directory /~. It's either / (root directory) or ~ (home directory of current user). It's also irrelevant to the problem.

    2.

    I set the search_path variable to geochat, the name of the database I'm working in

    The search_path has nothing to do with the name of the database. It's for schemas inside the current database. You probably need to reset this.

    3.
    You are required to use the absolute path for your file. As documented in the manual here:

    filename

    The absolute path name of the input or output file.

    4.
    DELIMITER: just noise.

    The default is a tab character in text format

    5.
    NULL: It's rather uncommon to use the actual string 'NULL' for a NULL value. Are you sure?

    The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format.

    My guess (after resetting search_path - or you schema-qualify the table name):

    COPY geonames FROM '/path/to/file/US.txt';
    
    


  • Maybe a bit late, but hopefully useful:

    Use \copy instead

    https://wiki.postgresql.org/wiki/COPY

    jvdw



  • The paths are relative to the PostgreSQL server, not the psql client.

    Assuming you are running PostgreSQL 9.4, you can put US.txt in the directory /var/lib/postgresql/9.4/main/.



  • if you're running your COPY command from a script, you can have a step in the script that creates the COPY command with the correct absolute path.

    MYPWD=$(pwd)
    echo "COPY geonames FROM '$MYPWD/US.txt', DELIMITER E'\t';"
    MYPWD=
    
    

    you can then run this portion into a file and execute it

    ./step_to_create_COPY_with_abs_path.sh >COPY_abs_path.sql
    psql -f COPY_abs_path.sql -d your_db_name
    
    


最新帖子

最新内容

  • S

    I'm making an easy android application for tests. I want to generate dump file/files to fill available space on external storage in phone. I'm using this code:

    try (FileOutputStream out = new FileOutputStream(f2)) { byte[] bytes = new byte[m]; for (int i = 0; i < a; i++) { out.write(bytes); } out.flush(); out.close(); }

    It' working but'it's too slow (250 mb/ 20 s) because I'm writting to file actual bytes. I used RandomAccessFile object before. It was very fast to generate files with specfifc size, but Android didn't see size of this files, which was stranged (File had good size but the available space did't change). Is it better solution?

    read more
  • S

    I'm meeting a problem with show message UNICODE on console with mycli (with image attachment). Please help me how to config mycli to show message unicode beautiful.

    I was researched all source but can't config.

    https://user-images.githubusercontent.com/18625823/56551471-ac407400-65b2-11e9-8d1b-e26aeea269b6.png

    read more
  • S

    What you describe would be ^_[a-zA-Z._]+$

    ^ means start of string _ is literally your underscore [a-zA-Z._]+ is lower, upper, dot and underscore 1 or more times $ is end of string

    However it allows __ and _. as username

    <pre class="snippet-code-js lang-js prettyprint-override">``` const regex = /^_[a-zA-Z._]+$/gm; const str = `_username _username1 .username username`; let m; while ((m = regex.exec(str)) !== null) { // This is necessary to avoid infinite loops with zero-width matches if (m.index === regex.lastIndex) { regex.lastIndex++; } // The result can be accessed through the `m`-variable. m.forEach((match, groupIndex) => { document.write(`Found match, group ${groupIndex}: ${match}`); }); }

    read more
  • S

    When creating regex, you can help yourself using https://regex101.com/.

    That said, here is your regex :

    <pre class="snippet-code-js lang-js prettyprint-override">``` function test(username) { const regex = new RegExp('^_{1}[A-Za-z\.]*$', 'i'); // Alternative version considering @thomas points // const regex = new RegExp('^_[A-Za-z._]+$', 'i'); return regex.test(username); } console.log(test('test')); console.log(test('_test')); console.log(test('_te.s.t')); console.log(test('_teST')); console.log(test('Test_')); console.log(test('^zdq^dz.')); console.log(test('_teS/T')); console.log(test('_9901A'));

    read more
  • S

    I need some help creating regexp. It's just I don't quite understand how to create a regexp. How do i create a validation for username with some rules like this

    only Uppercase, lowercase, underscore(_) and dot(.) are allowed start with an underscore(_)

    I've already tried some regexp from mozilla developer site, but it doesn't seems right

    <pre class="lang-js prettyprint-override">``` var usernameRegex = new RegExp(/_+[A-Za-z]/); var usernameRegexFound = usernameRegex.test(username.value); if (!usernameRegexFound) { msg = "Invalid Username"; } I expect some username like so \_username = true \_username1 = false .username = false username = false and also are there any sites for me to understand how to create regexp, because I got some more thing to do with it

    <pre class="snippet-code-js lang-js prettyprint-override">```
    function validuser(username) {
    var msg = "valid";
    var usernameRegex = new RegExp(/_+[A-Za-z]/);
    var usernameRegexFound = usernameRegex.test(username);
    if (!usernameRegexFound) {
    msg = "Invalid Username";
    }
    return msg;
    }

    console.log(validuser("_username","Valid?"));
    console.log(validuser("_username1","Invalid?"));
    console.log(validuser(".username","Invalid?"));
    console.log(validuser("username","Invalid?"));

    read more

推荐阅读