Thread: Problems when copy data from dump file
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial">I get an error when I’m trying to fill my table with data. I have a postgresql dump file and copy-pastethe text from the file into my webbased phppgadmin interface. I have created my tables correctly.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial">Down here I have post the error message I get. I suppose it’s something with the tab between “5”and the text? I have tried to make both tab and single space but I get the same error both times.</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial">SQL error:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial">ERROR: syntax error at or near "5"</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">LINE 2: 5 Slitasje og vedlikehold PRAKTISKE OPPLYSNINGER\r\n\r\nSn&#...</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> ^</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">COPY artikkel (id, tittel, tekst) FROM stdin;</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">5 Slitasje og vedlikehold PRAKTISKE OPPLYSNINGER\r\n\r\nSnø ogis.\r\nSnør...and more text... \.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial">Does anyone have some tips to an solution?</span></font><p class="MsoNormal"><font face="Arial"size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial">The postgresql version is </span></font><span class="platform">7.4.17.</span><font face="Arial"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font color="#a6a6a6" face="Verdana" size="2"><span lang="EN-US"style="font-size:10.0pt;font-family:Verdana;color:#A6A6A6"> </span></font></div>
Klas Stockhem wrote: > SQL error: > > ERROR: syntax error at or near "5" > > LINE 2: 5 Slitasje og vedlikehold PRAKTISKE OPPLYSNINGER\r\n\r\nSn&#... > > ^ > COPY artikkel (id, tittel, tekst) FROM stdin; > > 5 Slitasje og vedlikehold PRAKTISKE > OPPLYSNINGER\r\n\r\nSnø og is.\r\nSnør...and more text... \. 1. Do you really have a blank line between "COPY" and "5"? If so, that's your problem. 2. Are you perhaps copying+pasting from a Windows machine to a Linux one or similar? You might be getting "\r\n" at the end of lines when it's expecting "\n". > The postgresql version is 7.4.17. Might want to set aside some time to upgrade in the near future - you're missing four full versions since 2003. At the very least, upgrade to 7.4.25 at your earliest convenience. -- Richard Huxton Archonet Ltd
Klas Stockhem wrote: > Thanks for your mail! Remember to cc: the mailing list too. Just hit "reply to all". Oh, and perhaps don't top-quote. It makes it difficult for others to follow the message. > 1. Yes, the 5 and other text are at line 2 (a new line). I tried to put > the whole command on the first line and execute it but I get same error: > > > ERROR: syntax error at or near "1" > LINE 1: COPY artikkel (id, tittel, tekst) FROM stdin 1 test test; No, the data needs to start on the line below COPY, but your first email had a blank line there. Your message had: Line 1: COPY artikkel (id, tittel, tekst) FROM stdin; Line 2: (blank) Line 3: 5 Slitasje og vedlikehold PRAKTISKE OPPLYSNINGER What you want is something like: COPY artikkel (id, tittel, tekst) FROM stdin; 1<tab>Title one<tab>Some text 2<tab>Title two<tab>Some text \. Where the <tab> marks are real tabs. > I have also separated the 1 and the "test-text" with tab. Is the syntax > for the line 1 correct? I have read some about using DELIMITER. Do you > have a suggestion how a can use this in the command? > > 2. Yes I have opened the dump file on in a windows system and copy+paste > it into the phppgadmin web interface. Hmm - reading the phppgadmin documentation, the FAQ says the following: "Only uploaded SQL scripts can contain COPY commandsand for this to work, you must have PHP 4.2 or higher." So - it looks like you should save the COPY script to a text-file and upload it. I don't know how you do that, but it should be in the manual. -- Richard Huxton Archonet Ltd
-----Ursprungligt meddelande----- Från: Richard Huxton [mailto:dev@archonet.com] Skickat: den 4 augusti 2009 15:27 Till: Klas Stockhem Kopia: PostgreSQL Ämne: Re: SV: [SQL] Problems when copy data from dump file Klas Stockhem wrote: > Thanks for your mail! Remember to cc: the mailing list too. Just hit "reply to all". Oh, and perhaps don't top-quote. It makes it difficult for others to follow the message. > 1. Yes, the 5 and other text are at line 2 (a new line). I tried to put > the whole command on the first line and execute it but I get same error: > > > ERROR: syntax error at or near "1" > LINE 1: COPY artikkel (id, tittel, tekst) FROM stdin 1 test test; No, the data needs to start on the line below COPY, but your first email had a blank line there. Your message had: Line 1: COPY artikkel (id, tittel, tekst) FROM stdin; Line 2: (blank) Line 3: 5 Slitasje og vedlikehold PRAKTISKE OPPLYSNINGER What you want is something like: COPY artikkel (id, tittel, tekst) FROM stdin; 1<tab>Title one<tab>Some text 2<tab>Title two<tab>Some text \. Where the <tab> marks are real tabs. > I have also separated the 1 and the "test-text" with tab. Is the syntax > for the line 1 correct? I have read some about using DELIMITER. Do you > have a suggestion how a can use this in the command? > > 2. Yes I have opened the dump file on in a windows system and copy+paste > it into the phppgadmin web interface. Hmm - reading the phppgadmin documentation, the FAQ says the following: "Only uploaded SQL scripts can contain COPY commandsand for this to work, you must have PHP 4.2 or higher." So - it looks like you should save the COPY script to a text-file and upload it. I don't know how you do that, but it should be in the manual. -- Richard Huxton Archonet Ltd > > > ok, I have downloaded and installed EMS SQL query 2007 local on my computer and it works fine to connect to my database serverfrom this software. (It did'nt work with the phpPGadmin web interface.) I have also inserted data into my tables ansql query it works fine. I have just one problem left: I must type the schema name and table name in all sql queries to get some outputs. E.g Schema2.table This makes some problems in my web applications. When I execute my php files in the web browser the sql server returns "schema"public2" does not exist". It reads the sql query in my web application like a small p but I have typed big P in thephp file. I can run this in the EMS software and there it works fine. When I try to just type the table name in sql query like "SELECT * FROM tablename" I get error "relation "tablename" doesnot exist". Does the php engine interpret the big P like a small one? Is it recommended to always have small letters in schema names? How do I make so I not must type the schema name in every sql query? The best would be if I just can type the table name. //Klas
Klas Stockhem wrote:> Does the php engine interpret the big P like a small one? It's not PHP, but PostgreSQL itself. All names in PostgreSQL are case insensitive unless you double-quote them. PostgreSQL actually translates everything to lower-case internally. CREATE TABLE1 ...; -- gives "table1" CREATE "TABLE2" ...; -- gives "TABLE2" SELECT * FROM table1 -- works SELECT * FROM TABLE1 -- works SELECT * FROM TaBlE1 -- works SELECT * FROM "table1" -- works SELECT * FROM "TABLE1" -- FAILS, actually "table1" SELECT * FROM table2 -- FAILS, looks for "table2" SELECT * FROM TABLE2 -- FAILS, still looking for "table2" SELECT * FROM "TABLE2" -- works So - if you double-quote a table-name (or function or schema name) when you create it, you should always double-quote it when using it. I'm guessing something added double-quotes for you when you created the schema. > Is it recommended to always have small letters in schema names? Personally, I do. I think it looks neater. > How do I make so I not must type the schema name in every sql query?> The best would be if I just can type the table name. There is a variable called "search_path" which controls what schemas are checked for tables, functions etc. If you have two tables with the same name but different schemas you'll need to use the <schema>.<table> format though. SET search_path = public2,public; ALTER USER myuser SET search_path = ... ALTER DATABASE mydb SET search_path = ... -- Richard Huxton Archonet Ltd
Klas Stockhem wrote: > The data in the dump file are structured and I want to execute this> automatically (copy+paste it) in the EMS software.>I still get the syntax error even if I use the command you send me:> COPY artikkel (id, tittel, tekst) FROM stdin;1<tab>Title one<tab>Some> text \. Why not just load the dump file into PostgreSQL? Why are you trying to copy+paste if you want the entire dump? -- Richard Huxton Archonet Ltd
Klas Stockhem wrote:> Does the php engine interpret the big P like a small one? It's not PHP, but PostgreSQL itself. All names in PostgreSQL are case insensitive unless you double-quote them. PostgreSQL actually translates everything to lower-case internally. CREATE TABLE1 ...; -- gives "table1" CREATE "TABLE2" ...; -- gives "TABLE2" SELECT * FROM table1 -- works SELECT * FROM TABLE1 -- works SELECT * FROM TaBlE1 -- works SELECT * FROM "table1" -- works SELECT * FROM "TABLE1" -- FAILS, actually "table1" SELECT * FROM table2 -- FAILS, looks for "table2" SELECT * FROM TABLE2 -- FAILS, still looking for "table2" SELECT * FROM "TABLE2" -- works So - if you double-quote a table-name (or function or schema name) when you create it, you should always double-quote it when using it. I'm guessing something added double-quotes for you when you created the schema. > Is it recommended to always have small letters in schema names? Personally, I do. I think it looks neater. > How do I make so I not must type the schema name in every sql query?> The best would be if I just can type the table name. There is a variable called "search_path" which controls what schemas are checked for tables, functions etc. If you have two tables with the same name but different schemas you'll need to use the <schema>.<table> format though. SET search_path = public2,public; ALTER USER myuser SET search_path = ... ALTER DATABASE mydb SET search_path = ... -- Richard Huxton Archonet Ltd > > > Yes It works fine when I execute the SET command and the ALTER USER command in the EMS software! Now my web application return data from the database tables that contains some data. (I have manually put data in some tables from the EMS software). I have "a lot of" data still left in the dump file to put in the database. I can manually put the data in the tables but this will take some times. The data in the dump file are structured and I want to execute this automatically (copy+paste it) in the EMS software. I still get the syntax error even if I use the command you send me: COPY artikkel (id, tittel, tekst) FROM stdin; 1<tab>Title one<tab>Some text \. This is the error I get : "ERROR: syntax error at or near "1" at character 47". It feels like it is some simple error but I can't find it out. I would help me a lot if I get a solution. //Klas