postgresql - pgAdmin3 : Using query results to COPY files

Question

我想使用 pgAdmin3(在 Mac OSX 10.9.5 上)将数百个文本文件复制到我的 postgres 数据库中。我创建了所有文件名及其路径的列表，并将其复制到数据库中名为“文件名”的表中。我正在尝试设置一个 WHILE 循环来打开每个文件:

Create NewTable (NewData varchar(255)); 
SET @i=1;
    WHILE @i<10
    BEGIN
        SET @V1=(select path_and_filename from filenames where row_number=@i);
        PRINT @V1;
        COPY NewTable FROM @V1;
        SET @i=@i+1;
    END

结果如下:

[PGSCRIPT ] ("/Volumes/WINDOWS COM/Weather ISD-lite/2013/010100-99999-2013")
[WARNING  ] COPY NewTable FROM ("/Volumes/WINDOWS COM/Weather ISD-lite/2013/010100-99999-2013")
            ERROR:  syntax error at or near "("
            LINE 1: COPY NewTable FROM ("/Volumes/WINDOWS COM/Weather ISD-lite/2...

我还尝试了以下方法:

DECLARE @R1 {@A};
SET @i=1;
WHILE @i<10
BEGIN
    SET @R1=(select path_and_filename from filenames where row_number=@i);
    SET @V1=@R1[0][0];
    PRINT @V1;

    COPY NewTable FROM @V1;

    SET @i=@i+1;
END

结果如下:

[PGSCRIPT ] /Volumes/WINDOWS COM/Weather ISD-lite/2013/010100-99999-2013
[WARNING  ] COPY NewTable FROM /Volumes/WINDOWS COM/Weather ISD-lite/2013/010100-99999-2013
            ERROR:  syntax error at or near "/"
            LINE 1: COPY NewTable FROM /Volumes/WINDOWS COM/Weather ISD-lite/201...
                                       ^

Answer 1

从错误来看，路径名没有被引用，因此会产生语法错误。

尝试改变这个:

COPY NewTable FROM @V1;

为此:

COPY NewTable FROM '@V1';