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

    Your class should look like this. As @jsanalytics mentioned in the comment. Name and Status should be properties, not fields.

    public class Employee { public string Name { get; set; } public string Status { get; set; } }

    read more
  • S

    I just start learning xamarin and I know I should go to MVVM approach but before that I want to try the code behind binding but it is not working. I can see there's 2 lines in mobile but I can't see the binding value. Why?

    TestListView.xaml

    <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="XamarinTabbed.Views.TestListView"> <ContentPage.Content> <StackLayout> <ListView x:Name="MyLV"> <ListView.ItemTemplate> <DataTemplate> <ViewCell> <StackLayout Padding="10"> <Label Text="{Binding Name}" TextColor="Black" /> <Label Text="{Binding Status}" TextColor="AliceBlue" /> </StackLayout> </ViewCell> </DataTemplate> </ListView.ItemTemplate> </ListView> </StackLayout> </ContentPage.Content> </ContentPage>

    TestListView.xaml.cs

    [XamlCompilation(XamlCompilationOptions.Compile)] public partial class TestListView : ContentPage { public TestListView () { InitializeComponent (); MyLV.ItemsSource = new ObservableCollection<Employee> { new Employee { Name = "t1", Status = "tt" }, new Employee { Name = "t2", Status = "t2" } }; } public class Employee { public string Name; public string Status; } }

    read more
  • S

    it shows a red underline in code when i use some attributes that declare in .d.ts file (in node_moudles lib),

    lib code("lib project") before compile(global.ts)

    interface WebGLVertexArrayObject extends WebGLObject {} interface WebGLRenderingContext { beWebgl2: boolean; bindpoint: number; beActiveVao: boolean; beActiveInstance: boolean; addExtension(extName: string): void; createVertexArray(): any; bindVertexArray(vao?: WebGLVertexArrayObject | null): void; deleteVertexArray(vao: WebGLVertexArrayObject): void; vertexAttribDivisor(index: number, divisor: number): void; drawElementsInstanced(mode: number, count: number, type: number, offset: number, instanceCount: number): void; drawArraysInstanced(mode: number, first: number, count: number, instanceCount: number): void; }

    after compile/publish (umd),produce file global.d.ts which content is same as before.

    while i install and use it in my project("another project"),

    let gl: WebGLRenderingContext=.....; gl.beActiveVao=..;

    beActiveVao is underlined in red in Vscode.

    how can i write "interface WebGLRenderingContext" in my lib,so it can be use conveniently and no error warn in my project(install mylib);

    read more
  • S
    if("200".equals(SampleResult.getResponseCode())&& "0".equals(SampleResult.time.toString())) { // Success SampleResult.setSuccessful(false); // Change sampler status to success AssertionResult.setFailure(true); // Set assertion status to success as well } else { AssertionResult.setFailure(false); // Set assertion status to failure }

    posted this code in JSR223 assertion. i get the desired result and the assertion should be inside the sampler i.e at the sampler level..

    read more
  • S

    I want to over-ride the status in j-meter for 200-OK with elapsed time 0ms.

    Actually i have used java with jmeter, and put SampleResult.sampleStart() and sampleEnd() for measuring time taken by the sampler, but in some case due to some exception and error my that block of code is not being executed so my elapsed time is coming zero(0) but with status "200 ok" as pass .

    I want to fail that scenario.

    Can anyone help me please? Thanks in advance.

    WDS.sampleResult.sampleStart(); for (int count = 0; count <= 100; count++) { if (WDS.browser.findElements(By.xpath("//div[@class='divshow loadingimage']//span[@class='spinnerloader loadermain']")).size() > 0) { Thread.sleep(1000); WDS.log.info("Waiting for spinners to disappear"); } else { WDS.log.info("Break----------"); break; } } WDS.log.info("Page Loaded"); WDS.sampleResult.sampleEnd();

    read more

推荐阅读