Re: [SQL] Export tab delimited from mysql to postgres.
A tested example in Python :
Basically it counts the \t and accumulates the lines until it has enough
and then prints the line.
Note : as an exercise you could add a test so that there are exactly
(columns-1) delimiters and not >=(columns-1).
def grouplines( in_stream, columns, delimiter ):
num_delimiters = columns - 1
accum = ''
for line in in_stream:
accum += line
if accum.count( delimiter ) >= num_delimiters:
print accum.replace( "\n", "\\n" )
accum = ''
if accum:
print "Last line unterminated."
grouplines( open( 'data.in' ), 3, "\t" ):
Input data (I added a column over your example):
1 What a day! A
2 What a week it has
been! B
3 What the! C
Output :
1 What a day! A\n
2 What a week it has\nbeen! B\n
3 What the! C
Have fun with your copy !
On Tue, 12 Oct 2004 15:33:46 +1000, Theo Galanakis
<[EMAIL PROTECTED]> wrote:
Thanks for all your comments,
I have beent trying the insert within a transaction block, however
it does not seem to reduce the time it takes to process each records.
Mind
you there are 80 column and the insert statement explicitly defines the
column to insert into.
I need any tip I can get help me transform the text file into a
format postgres copy will successfully read.
Here is sample of the current format of a mysql tab delimited dump..
columnA columnB
1 What a day!
2 What a week it has
been!
3 What the!
As you can see row 2 has a value that holds a CR value which ends up
wrapping around onto the third line. Postgres copy command does not like
this and mysql is unable to replace the value with another type of
delimiter, like a \r.
So I gather I have to some how manually replace the carriage return with
something postgres understand \r...
columnA columnB
1 What a day!
2 What a week it has \r been!
3 What the!
How do I do this without getting a text file that looks like this
1 What a day! \r\n2 What a week it has \r been!\r\n3
What the!\r\n
Any help would be appreciated.
Theo
-Original Message-
From: Christopher Browne [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 12 October 2004 10:46 AM
To: [EMAIL PROTECTED]
Subject: Re: [SQL] Export tab delimited from mysql to postgres.
Quoth [EMAIL PROTECTED] (Theo Galanakis):
Could you provide a example of how to do this?
I actually ended up exporting the data as Insert statements,
which strips out cf/lf within varchars. However it takes an eternity
to import 200,000 records... 24 hours infact Is this normal?
I expect that this results from each INSERT being a separate transaction.
If you put a BEGIN at the start and a COMMIT at the end, you'd doubtless
see
an ENORMOUS improvement.
That's not even the _big_ improvement, either. The _big_ improvement
would
involve reformatting the data so that you could use the COPY statement,
which is _way_ faster than a bunch of INSERTs. Take a look at the
documentation to see the formatting that is needed:
http://techdocs.postgresql.org/techdocs/usingcopy.php
http://www.faqs.org/docs/ppbook/x5504.htm
http://www.postgresql.org/docs/7.4/static/sql-copy.html
--
output = ("cbbrowne" "@" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/lsf.html
Question: How many surrealists does it take to change a light bulb?
Answer: Two, one to hold the giraffe, and the other to fill the bathtub
with brightly colored machine tools.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
__
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Information about the command SQL " create synonym".
Hello ! I am trying to find some informations about the SQL command "create synonym". The command "create synonym" does not exit in the Postgres database. I had tested with the latest version (postgres8.0 beta3) and this command is not present. I supposed that this command "create synnonym" is an extention of the SQL92 on the other database like Oracle, Informix etc.. Could you say if this command will be implemanted in a future version of a postgres database ? Best regards. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] PL/pgSQL, RETURN NEXT, ORDER
Hi, When I call a PL/pgSQL function that looks roughly like the following: ... FOR x IN SELECT ... ORDER BY ... LOOP RETURN NEXT x; END LOOP; RETURN; END; Is the order of the rows guaranteed to be preserved? Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] PL/pgSQL, RETURN NEXT, ORDER
Ð ÐÑÑ, 12.10.2004, Ð 15:24, Markus Bertheau ÐÐÑÐÑ: > Is the order of the rows guaranteed to be preserved? Neil clarified on IRC that - there is no interface guarantee - in the current implementation the order is preserved - a future implementation is likely to change that Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Export tab delimited from mysql to postgres.
Theo Galanakis <[EMAIL PROTECTED]> writes: > As you can see row 2 has a value that holds a CR value which ends up > wrapping around onto the third line. Postgres copy command does not like > this and mysql is unable to replace the value with another type of > delimiter, like a \r. > So I gather I have to some how manually replace the carriage return with > something postgres understand \r... > columnA columnB > 1 What a day! > 2 What a week it has \r been! > 3 What the! > How do I do this without getting a text file that looks like this > 1 What a day! \r\n2 What a week it has \r been!\r\n3 > What the!\r\n Looks like a simple sed problem to me: cat myfile | sed 's/\r$//' | sed 's/\r/\\r/g' >newfile (Most likely you can do both steps in one sed process, but this is easy to follow.) This assumes that you only have newlines (\n) at the real ends of lines, else you need to think harder about how to tell the difference between data and formatting. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL confusion
This is untested, but it might be enough to get you started: SELECT namecounter FROM name n WHERE NOT EXISTS ( SELECT 1 FROM name WHERE hh > 0 AND famnu = n.famnu ) GROUP BY famnu HAVING birthdate = min( birthdate ); What I'm trying to do here is grab all families that don't have a head of household, group them by family, and get only the namecounter corresponding to the minimum birthdate for that family. If I recall, I've had some trouble using HAVING with min/max in ways that seem intuitive to me, but this might help get you started. -tfo On Oct 9, 2004, at 3:39 PM, Andrew Ward wrote: I'm trying to figure out how to do a particular query, and I'm beating my head against a wall. Here's my situation: I'm running postgres 7.3.2 on linux, and making my requests from Perl scripts using DBD::Pg. My table structure is as follows (irrelevant cols removed) CREATE TABLE name ( namecounter integer NOT NULL, firstmiddle character varying(64) NOT NULL, lastname character varying(64) NOT NULL, birthdate date, hh smallint, famnu integer, ); Each row represents a person with a unique namecounter. Families share a famnu, and usually one person in a family is marked as head of household (hh>0), with everyone else hh=0. However, there are a few families with nobody marked as hh, and I'd like to elect one by age. The query I'm trying to do is to pull one person from each household, either the head of household if available, or the eldest if not. I want them sorted by last name, so I'd prefer to find them all in one query, no matter how ugly and nested it has to be. I can pull the list with hh>0 easily enough, but I'm not sure how to pull out the others. I realize that this could be done through some looping in the Perl script, but I'd like to avoid pulling the whole list into memory in case the list gets long. My preference is to just handle one record at a time in Perl if possible. Help? Andrew Ward [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Information about the command SQL " create synonym".
[EMAIL PROTECTED] wrote: > Could you say if this command will be implemanted in a future version > of a postgres database ? I'm not currently aware of any concrete proposals to implement this feature, but previous discussion has not shown any strong resistance against the concept. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Export tab delimited from mysql to postgres.
Title: RE: Export tab delimited from mysql to postgres. Thankyou all for your advice, Looking further into the issue I have realised that "data and formatting" lines are denoted by the same CR/LF symbols. So if I where to replace all CR/LF symbols it would create a file with one line... I have replace the CR/LF for reading purposes below with \r\n or alternatively 0D 0A (hex): columnA columnB 1 What a day!\r\n 2 What a week it has\r\n been!\r\n 3 What the!\r\n What this means that it is impossible to move line three back to line two to look something like this: columnA columnB 1 What a day!\r\n 2 What a week it has\rbeen!\r\n 3 What the!\r\n I was thinking of writing code that checked each line to see if correct number of tab delimited elements in this case 2 elements, if this was not the case replace the \r\n with \r. Theo -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 13 October 2004 1:38 AM To: Theo Galanakis Subject: Re: Export tab delimited from mysql to postgres. > Could you provide a example of how to do this? Depends on what exactly your dump file contains. Probably something like sed 's/\\\r/\\r/g' This means: replace all carriage returns preceded by a backslash by '\r'. __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
[SQL] JDBC +CIDR
Hi, I’ve a table containing a CIDR field. I’m using an EJB to create a new record in this table. I don’t know how to pass the CIDR value. I tried by String but I get this error : javax.ejb.FinderException: Find failed: java.sql.SQLException: ERROR: operator does not exist: cidr = text So how should I do? Thanks in advance…
