Solved

replace numeric date format in sql postgresql file

Posted on 2008-11-03
10
734 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
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.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

705 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

17 Experts available now in Live!

Get 1:1 Help Now