Re: [GENERAL] Getting data from Xml to Postgresql database
aravind chandu wrote: Hi, I have some data in XML format and i need to upload in postgresql database using stored procedure. Can someone tell me the step by step procedure of the same as i will be doing it for the first time. Thank You, Avin. I have offered a python script that converts XML documents to psql COPY. It is part of this package: http://pgfoundry.org/projects/mstopsql/ As of yet I have been unable to get a password for the web page from pgfoundry so there is information on usage here: http://gwynux.ca/psql/ Gwyneth
Re: [GENERAL] MSSQL to PostgreSQL
Iv Ray wrote: Hi Ray, I have something that does this. I am currently awaiting project approval on pgfoundry. It does tables, views, and stored procedures. I have tested it on 450,000 lines of mssql code including 400 tables and 3700 stored procedures, although it is a work in progress. I applied last week and they said it takes 72 hours. As soon as it is approved I can post it. (give it a couple days I guess) Gwyneth Hi Gwyneth, sounds very interesting. We found this - http://wiki.postgresql.org/wiki/Converting_your_data_from_MS_SQL_Server_7_to_PostgreSQL_7.1.x (for very old version) - it is surprising that the SQL Enterprise Manager does this... We gave it a little try today and saw that indeed, there is "PostgreSQL" in there. But we left to try it tomorrow. Do you know it? We do not have stored procedures, only tables and views. Would be, in any case, interested in seeing what you have. Iv Ray, I think we tried that and I think it works but not for us. It may work for you. MS wants to copy all the tables over with exactly the same names including [dbo]. and so on. When it doesn't find an exact match, it wants to create the table. This may be ok for you but I am not sure what it does about mapping datatypes. This is controlled in my program by precreating the tables with the desired mappings. (we have mostly found what works but it can be changed easily) We have over 400 tables and as they are related, they need to be loaded in a particular order. I control this with a file of table names (dropped out of the conversion program) The data copy is only a few scripts largely based on FreeTDS. (do a google, I won't post a link here) So give the Enterprise Manager a try. It may work for you. I think we only had to install a Postgres odbc driver. My program(s) should be available soon (few days I figure) when I get a place to post it. Gwyneth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MSSQL to PostgreSQL
Iv Ray wrote: Hello, what is the current status of the MSSQL to PostgreSQL tools/tips? Google returns results as old as 2001... The MSSQL database in question has only tables, no stored procedures, etc. Thanks, Iv Hi Ray, I have something that does this. I am currently awaiting project approval on pgfoundry. It does tables, views, and stored procedures. I have tested it on 450,000 lines of mssql code including 400 tables and 3700 stored procedures, although it is a work in progress. I applied last week and they said it takes 72 hours. As soon as it is approved I can post it. (give it a couple days I guess) Gwyneth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xml and postgresql
aravind chandu wrote: Hi folks, I need to load xml data in to database can you tell me they way how do I import xml data into postgresql database. lets dat this is the xml file Harry Potter J K. Rowling 2005 29.99 Learning XML Erik T. Ray 2003 39.95 so finally the table name should be bookstore and column names are category,title, author,year,price and all the information in the xml file should be ported to the table can you please tell me how to do this its a bit confusing to me as i am just a beginner to this. Thanks in advance, Aravind. I have written a python program that may help you. It is part of a larger project I hope to post in a few days. Or I could sent it to you. Gwyneth
Re: [GENERAL] Delete from Join
Tom Lane wrote: Gwyneth Morrison <[EMAIL PROTECTED]> writes: What I am actually trying to get past is: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; The equivalent to that in Postgres would be DELETE FROM data_table1 USING data_table2 WHERE data_table1.fkey = data_table2.pkey; The fundamental issue here is that MSSQL expects the USING clause to contain a second reference to the delete target table, whereas PG does not --- if you write the table name again, that's effectively a self-join and you probably won't get the behavior you want. You can use JOIN syntax in USING in Postgres, but only for situations where the query really involves three or more tables. regards, tom lane Thank you Tom, That was exactly what I needed to know and yes it does work. I do know about the using/from clause and and the second table reference. Sorry about the above example, I cut it from something much larger to try and get my point across. Yes it is invalid. I should be more careful. I do have another question I will post as a separate posting. Gwyneth
Re: [GENERAL] Delete from Join
Scott Marlowe wrote: Take a look here, in the notes section: http://www.postgresql.org/docs/8.3/interactive/sql-delete.html on the using keyword. Thank you for your reply Scott, I guess this is where the confusion started for me. It says here in your reference that the using clause is not standard but that is fine as I am doing a subst. MS actually allows: DELETE from table from table. I change the second from to a using and it works fine most of the time. What I am actually trying to get past is: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; Where the INNER JOIN keyword is used in the delete. In the documentation section you cited, they refer to the from clause in the usinglist. The from clause link there refers to select which states that a join keyword is valid in a from clause. What I have found is it sometimes compiles but doesn't work. I am just seeking verification if it is supposed to work. Gwyneth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete from Join
--- On Wed, 7/2/08, Gwyneth Morrison <[EMAIL PROTECTED]> wrote: > From: Gwyneth Morrison <[EMAIL PROTECTED]> > Subject: [GENERAL] Delete from Join > To: pgsql-general@postgresql.org > Date: Wednesday, July 2, 2008, 3:15 PM > Hello, > > Is it possible to use a join keyword in a delete? > > For example: > > DELETE FROM data_table1 > using data_table2 INNER JOIN > data_table1 ON data_table1.fkey = > data_table2.pkey; > > > It is not directly mentioned in the delete syntax but the > delete refers > to the select clause where JOIN is valid. > > G > >i have a example >delete from t1 a using t2 b where a.id = b.oid >A standard way to do it is >delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) Thank you for your reply, You are absolutely correct, it IS the standard way. What I am actually trying to do here is write a program to convert MS SQL to Postgres. I have had quite a bit of success so far, but this is a sticking point. Apparently using the JOIN keyword directly in a delete statement is valid in MS. I am trying to determine if it is valid in postgres which I figure it is not but cannot find it exactly in the documentation. So I guess the real question is, can the JOIN keyword be used directly in a delete as above. G -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Delete from Join
Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
