current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

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

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

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

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;

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.

NOW,

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

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

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

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.

Regards, Sandy

share|improve this question
1  
Can you load your data into a temporary table and copy it from temporary table to a real one with INSERT ... SELECT ... ? –  Igor Romanchenko Jul 23 '13 at 7:44
    
lgor.. Thanks for the comment but as the file copy is for very huge record Hence using of temporary table will be time consuming task. –  Sandy Jul 23 '13 at 9:16
add comment

2 Answers

up vote 2 down vote

COPY with a column-list, and set a DEFAULT on the table columns you don't specify.

regress=> CREATE TABLE copydemo(a text not null, b text not null default 'blah');

regres=> \COPY copydemo(a) FROM stdin
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> blah
>> otherblah
>> \.
regres=> SELECT * FROM copydemo;
     a     |  b   
-----------+------
 blah      | blah
 otherblah | blah
(2 rows)

You're probably COPYing from a file rather than stdin; I just did it on stdin for a quick demo of what I mean. The key thing is that columns that require values not in the CSV have DEFAULTs set, and you specify a column-list in COPY, eg COPY (col1, col2).

There is unfortunately no equivalent to the COPY-specific SET that you want there. You can stage via a temporary table and do an INSERT INTO ... SELECT, as Igor suggested, if you can't or don't want to ALTER your table to set column DEFAULTs.

share|improve this answer
    
Craig.. Thanks for the comment but the default will not work for me. as the initial value for job_id will be different for each file copy. –  Sandy Jul 23 '13 at 9:14
    
@Sandy In Postgres ALTER TABLE statement is transactional. You can set it at the start of a transaction for every import job and they wont interfere with each other. And, at the end of transaction - just revert to old defaults. –  Igor Romanchenko Jul 23 '13 at 9:36
    
@IgorRomanchenko That's half-correct. ALTER TABLE is transactional, but it takes an ACCESS EXCLUSIVE lock on the table that prevents concurrent reads and writes. So you can roll it back, sure, but it's still plenty disruptive. In this case the best option is to follow your suggestion - stage to a temp table, then do an INSERT INTO ... SELECT as I outlined above. –  Craig Ringer Jul 23 '13 at 10:19
add comment
up vote 1 down vote

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 ','"

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.