Solved

replace numeric date format in sql postgresql file

Posted on 2008-11-03
10
736 Views
Last Modified: 2013-12-26
I have a postgresql dump file which I want to import to version 8.3
I need to replace numeric data in this file with format "10-25-2004" to "25-10-2004".
i.e. the middle  field needs to be swapped with the first field.

How do I do this in linux?

0
Comment
Question by:1Tsupp
10 Comments
 
LVL 40

Expert Comment

by:omarfarid
ID: 22867541
can you show sample file content?
0
 
LVL 14

Expert Comment

by:cjl7
ID: 22867566
Hi,

This is one way...

echo "10-25-2004" |perl -pe 's/(..)-(..)-(....)/$2-$1-$3/g'

And if you have a dump-file

cat file |perl -pe 's/(..)-(..)-(....)/$2-$1-$3/g'

If you know it's only this specific date and nothing else you could use sed (sed -i 's/10-25-2004/25-10-2004/g' <file>)


0
 

Author Comment

by:1Tsupp
ID: 22867617
sample data line will be:
mrproper      Can Music Support Interfaces to Complex Databases?      cpchr      Euromicro - 1997, Frontiers of Information Technology      06-13-1997      Proper      D.      DAta2      J.L.      Long      F.W                  \N      \N      \N      \N      \N      \N            \N      \N      \N                  261      \N      Budapest      Hungary      y      \N      \N            Published      \N      \N      \N      \N      \N      \N

I want to take the field in this instance 06-13-1997 and swap it to 13-06-1997 leaving the rest of the line unaltered.
0
 

Author Comment

by:1Tsupp
ID: 22867766
I've just tried cat file |perl -pe 's/(..)-(..)-(....)/$2-$1-$3/g'
and it appears to have partially worked however I have just realised the data is a little more complex.

The data line:

author      Applications of Virtual Reality      bked            01-01-1995      Author1      R.A.      Bloggs      H.      Vince      J.A.                  \N      0-12-227755-4      \N      \N      \N      \N      328      \N                              286      \N      \N      \N      \N      Academic Press      \N            Published      \N      \N      \N      \N      \N      \N

is now:

author      Applications of Virtual Reality      bked            01-01-1995      Author1      R.A.      Bloggs      H.      Vince      J.A.                  \N      0-227755-4      \N      \N      \N      \N      328      \N                              286      \N      \N      \N      \N      Academic Press      \N            Published      \N      \N      \N      \N      \N      \N

i.e. 0-12-227755-4 has changed to 0-227755-4
I want to leave this unchanged so the test needs to be strictly:  
Test for  first2 digit numeric-second2 digit numeric-4 digit numeric
Swap second2 digit numeric-first2 digit numeric-4 digit numeric

Any clues?

0
 
LVL 19

Expert Comment

by:jools
ID: 22868331
Can you re-export the database in the right date format?

Sorry, had to ask...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 22

Expert Comment

by:earth man2
ID: 22868877
http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-DATESTYLE
prepend data in file with a set command ie

SET DATESTYLE iso, mdy;
0
 
LVL 3

Accepted Solution

by:
bishillo earned 250 total points
ID: 22869613
the "SET DATESTYLE iso, mdy;" is the best way to go. You should use same configuration for import/import, in dates and also in charset (iso-8859-1, utf-8...).

But in perl you should use [0-9] (\d for decimal is also valid) iinstead of dot. Dot matches every character, with [0-9] or \d just numbers, so it won't broke other data.

cat file | perl -pe 's/(\d{2})-(\d{2})-(\d{4})/$2-$1-$3/g'
0
 
LVL 14

Expert Comment

by:cjl7
ID: 22873905
Working with regular expressions is always tricky. It's probably better to get the data into the database and then manipulate it. After all that's one thing a SQL server is good at! ;)
0
 

Author Comment

by:1Tsupp
ID: 22874743
cat file | perl -pe 's/(\d{2})-(\d{2})-(\d{4})/$2-$1-$3/g' works without altering other data.
I cannot use "SET DATESTYLE iso, mdy;" - i'm migrating data from postgres 6.5 to 8.3
and massaging the 6.5 pg_dump file.
Thanks to all for your help.

0
 

Author Closing Comment

by:1Tsupp
ID: 31512704
Thanks
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now