使用PostgreSQL COPY FROM命令的MySQL LOAD DATA INFILE [英] MySQL LOAD DATA INFILE with PostgreSQL COPY FROM command

问题描述

我是PostgreSQL的新手。其实我以前使用过MySQL，但是出于某些项目特定的原因，我需要使用postgreSQL并进行POC。

I am very new to PostgreSQL. Actually I was using MySQL before but for some project specific reason I need to use postgreSQL and do a POC.

现在的问题是：
我正在使用MySQL LOAD DATA INFILE命令将列内容从文件加载到我的数据库表中。

Now the problem is: I was using MySQL LOAD DATA INFILE command to load the column content from a file to my database table.

我的表结构是：
表名：MSISDN
表列名称：ID（主键-自动生成），JOB_ID，MSISDN，区域，状态

My table structure is : Table name: MSISDN Table Column Names: ID(primary key - auto_generated), JOB_ID, MSISDN, REGION, STATUS

但是我的输入文本文件（rawBase.txt）仅具有以下列：
MSISDN，区域

But my input text file(rawBase.txt) is having only below columns: MSISDN, REGION

所以我正在使用以下命令将上面的2列加载为初始JOB_ID和STATUS。

so I was using the below command to load these above 2 column with initial JOB_ID and STATUS.

LOAD DATA INFILE 'D:\\project\\rawBase.txt' INTO TABLE MSISDN 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(MSISDN,REGION) 
SET JOB_ID = 'XYZ1374147779999', STATUS = 0;

您可以看到LOAD DATA INFILE命令中有一个选项，我可以在其中设置特定的输入文本文件中不存在的列的初始值（JOB_ID和STATUS）。

as you can see that there is a option available in LOAD DATA INFILE command where I can SET a particular initial value for the columns which are not present(JOB_ID and STATUS) in the input text file.

现在，

在使用postgreSQL的情况下，我希望发生相同的事情。

in case of postgreSQL, I want same thing to happen.

也有相同类型的命令COPY FROM
，如下所示：

There is also a same kind of command available COPY FROM like below:

COPY MSISDN FROM 'D:\\project\\rawBase.txt' WITH DELIMITER AS ','

但我无法为不存在的其余列（JOB_ID和STATUS）设置特定的初始值我的输入文本文件。我没有得到任何富有成效的例子。

but I am not able to SET a particular initial value for the rest columns which are not present(JOB_ID and STATUS) in my input text file. I am not getting any fruitful example of doing this.

请尽可能提出一些建议。

Please give some suggestion if possible.

Sandy

Regards, Sandy

推荐答案

您可以使用管道以 Unix方式进行操作：

You may do it the "Unix way" using pipes:

cat rawbase.txt | awk '{print $0",XYZ1374147779999,0"}' | psql -d dbname -c "copy MSISDN FROM stdin with delimiter AS ','"

现在从您似乎正在使用MS-Windows时出现问题的文件路径，但是Unix shell和命令行工具（如 awk ）可通过 MSYS 或 Cygwin 。

Now from the file paths in the question it appears you're using MS-Windows, but a Unix shell and command-line tools like awk are available for Windows through MSYS or Cygwin.

这篇关于使用PostgreSQL COPY FROM命令的MySQL LOAD DATA INFILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！