我可以自动创建一个表在PostgreSQL从csv文件与头？

Question

我在OS X 10.6.8上运行PostgreSQL 9.2.6。我想从带有列标题的CSV文件导入数据到数据库。我可以用COPY语句做到这一点，但前提是我首先手动创建一个表，其中包含CSV文件中每个列的列。有没有办法根据CSV文件中的标题自动创建此表？

每个问题我都试过了

COPY test from'/path/to/test.csv'CSV HEADER；

但我只是得到了这个错误：

错误：关系“test”不存在

如果我首先创建一个没有列的表：

创建测试表

我得到：

错误：最后一个预期列后有额外数据

我在PostgreSQL COPY文档中找不到任何关于自动创建表的内容。是否有其他方法可以从带有标题的CSV文件自动创建表？

Answer 1

我正在使用csvsql生成表格布局（它将自动猜测格式）：

head -n 20 table.csv | csvsql --no-constraints --tables table_name 

然后我在psql中使用\COPY。这是我导入CSV文件的最快方法。

您还可以将sed与csvsql一起使用，以获得所需的数据类型：

head -n 20 table.csv | csvsql --no-constraints --tables table_name  | sed 's/DECIMAL/NUMERIC/' | sed 's/VARCHAR/TEXT/'

Answer 2

还有第二种方法，我在这里找到了（来自mmatt）。基本上，您在Postgres中调用一个函数（最后一个参数指定列数）。

select load_csv_file('myTable','C:/MyPath/MyFile.csv',24)

这是mmatt的函数代码，我不得不稍微修改一下，因为我正在处理公共模式。（复制

CREATE OR REPLACE FUNCTION load_csv_file(
    target_table text,
    csv_path text,
    col_count integer)
  RETURNS void AS
$BODY$

declare

iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet

begin
    set schema 'public';

    create table temp_table ();

    -- add just enough number of columns
    for iter in 1..col_count
    loop
        execute format('alter table temp_table add column col_%s text;', iter);
    end loop;

    -- copy the data from csv file
    execute format('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path);

    iter := 1;
    col_first := (select col_1 from temp_table limit 1);

    -- update the column names based on the first row which has the column names
    for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
    loop
        execute format('alter table temp_table rename column col_%s to %s', iter, col);
        iter := iter + 1;
    end loop;

    -- delete the columns row
    execute format('delete from temp_table where %s = %L', col_first, col_first);

    -- change the temp table name to the name given as parameter, if not blank
    if length(target_table) > 0 then
        execute format('alter table temp_table rename to %I', target_table);
    end if;

end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION load_csv_file(text, text, integer)
  OWNER TO postgres;

注意：导入与编码相关的文本文件有一个常见问题。csv文件应为UTF-8格式。然而，有时程序并没有完全实现这一点，它们试图进行编码。我通过在记事本中打开文件并将其转换为ANSI和UTF8来克服这个问题。

Answer 3

有一个非常好的工具可以将表从csv文件导入Postgres。它是一个名为pgFutter的命令行工具（包含windows、linux等的二进制文件）。它的一大优点是它也能识别属性/列名。

该工具的使用方法很简单。例如，如果您想导入myCSVfile.csv：

pgfutter --db "myDatabase" --port "5432" --user "postgres" --pw "mySecretPassword" csv myCSVfile.csv

这将创建一个表（名为myCSVfile），其中的列名取自csv文件的标题。此外，将根据现有数据确定数据类型。

注意：命令pgfutter根据您使用的二进制文件而有所不同，例如，它可能是pgfutter\u windows\u amd64.exe（如果您想经常使用此命令，请重命名它）。必须在命令行窗口中执行上述命令（例如，在Windows中运行cmd，并确保pgfutter可访问）。如果希望使用不同的表名，请添加表“myTable”；要选择特定的数据库模式，请使用us——模式“mySchema”。如果要访问外部数据库，请使用主机“myHostDomain”。

将myFile导入myTable的pgfutter更详细的示例如下：

pgfutter --host "localhost" --port "5432" --db "myDB" --schema "public" --table "myTable" --user "postgres" --pw "myPwd" csv myFile.csv

导入后，您很可能会更改一些数据类型（从文本到数字）：

alter table myTable
  alter column myColumn type numeric
    using (trim(myColumn)::numeric)