Search Postgresql Archives

Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I didn't specify any schema, so it was created in public schema.
The error message also says "public"...
//------------------------------------------------------------------
ERROR: column "rec_id" of relation "spec" does not exist
LINE 1: INSERT INTO "public"."spec" ("rec_id","title_c...
//------------------------------------------------------------------

Output of the \d spec:


                 Table "public.spec"
           Column           |          Type           | Modifiers 
----------------------------+-------------------------+-----------
 rec_id                     | character varying(32)   | not null
 title_category             | character varying(255)  | 
 doctype                    | character varying(255)  | 
 ... goes on like this for other columns.

What are you trying to see in the output of \d spec?

I don't understand what you mean by the import code is trying to insert in to wrong version of the table.
I visually checked the left side "menu like" structure of the phpPgAdmin- there is no other table of that name.

You mentioned that quoted identifiers are not the issue.
This prompted me to test the process in a table with a few columns and ascii characters.
Immediately it was clear that quoted identifiers were not to blame.

I found that I got that error when I change encoding of the tab delimited file to UTF-8. 
Because my data contains non-ascii characters, if I don't use UTF-8, I get this error.

ERROR:  invalid byte sequence for encoding "UTF8": 0x82

... and I read somewhere that if I open the text file in notpad and save it with UTF-8 encoding, I can get rid of the error. (When inserting using pyDev (psycopg2)/Eclipse, that does get rid of the error...

That's why I changed encoding.

And now I am stuck with this error.

But at least, now I am not blaming phpPgAdmin :)
Thanks for the lead. 

BTW, both server and client encoding of my pg db are UTF8.

testdb=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 UTF8
(1 row)

testdb=# SHOW CLIENT_ENCODING;
 client_encoding 
-----------------
 UTF8
(1 row)

testdb=#


----- Original Message -----
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
To: s400t@xxxxxxxxxxx; rob stone <floriparob@xxxxxxxxx>; "pgsql-general@xxxxxxxxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Date: 2018/12/7, Fri 23:47
Subject: Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

On 12/7/18 12:28 AM, s400t@xxxxxxxxxxx wrote:
> Hello Adrian, Rob!
>
> Thank you for the comments.
>
> Oh, yes, I forgot to mention that I am using Postgresql version 9.6.
> I had read somewhere that last supported version was 9.3 or something,
> could be 9.2 as you say.
>
> I wanted to use phpPgAdmin, that's why I went back to 9.6 even if I had
> installed ver. 10 first.
> But if the phpPgAdmin quotes identifiers by defaults, I will need to
> look for some other ways.

I don't think the quoted identifiers are the issue. I am suspecting that
the import code may be trying to INSERT into the wrong version of the
table. Some questions:

1) Which schema did you create spec in?

2) In psql what does \d spec show?


>
> Strictly speaking, I don't need to use the phpPgAdmin, but I am trying
> to find a GUI way to upload a file quickly (the file has thousands of
> records.)
>
> Now, I am using pyDev in Eclipse to insert records, but I need to use a
> web-based click and upload.

?
https://www.pgadmin.org/

>
> If I could find a php version of my python code that would be great!
> My Python code:
>
> cur = conn.cursor()
>
> with io.open(fileName,'r',encoding='utf8') as f:
>      next(f)  # Skip header row.
>      cur.copy_from(f, tableName, sep='\t')
> conn.commit()
> cur.close()
> f.close()
>
>
> I have found PHP samples for line by line read and insert, but that will
> be too slow for me.
>
> Cheers!
>
>
>
>    ----- Original Message -----
>    *From:* rob stone <floriparob@xxxxxxxxx>
>    *To:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>; s400t@xxxxxxxxxxx;
>    "pgsql-general@xxxxxxxxxxxxxxxxxxxx"
>    <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
>    *Date:* 2018/12/7, Fri 16:47
>    *Subject:* Re: Importing tab delimited text file using phpPgAdmin
>    5.1 GUI
>
>    Hello,
>
>    On Thu, 2018-12-06 at 21:29 -0800, Adrian Klaver wrote:
>      > On 12/6/18 7:54 PM, s400t@xxxxxxxxxxx <mailto:s400t@xxxxxxxxxxx>
>    wrote:
>      >
>      > Most GUI tools I am familiar with quote identifiers by default.
>      > > How/where can I tell the phpPgAdmin not to add that extra "" around
>      > > the
>      > > field name?
>      >
>      > I don't know. I have been under the impression that phpPgAdmin was
>      > no
>      > longer maintained/in use.
>      >
>      > You might have more luck here:
>      >
>      > https://sourceforge.net/p/phppgadmin/discussion/115884
>      >
>      > >
>      > > Thanks for reading and suggestions.
>      >
>      >
>
>    If you look at phppgadmin on sourceforge, the tarball files are all
>    dated April 15th., 2013.
>    It appears to have stalled at Postgres version 9.2.
>    I doubt if it will run on versions 10 or 11.
>
>    Cheers,
>    Rob
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux