Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free
and readily available?

It also has a graphical table-from-file loader as well.

On Fri, 7 Dec 2018 at 23:35, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/7/18 9:04 AM, s400t(at)yahoo(dot)co(dot)jp wrote:
> > 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?
>
> My basic procedure in troubleshooting is starting from the known and
> working out to the unknown. So my questions about the schema(s) and the
> table definition where to establish a know starting point. Also a common
> issue that hit this list are multiple versions(across schemas) of an
> object in a database and code hitting the wrong version. One of the
> signs of that being error messages of the form you got.
>
>
> >
> > 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.
>
> See above.
>
> >
> > 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...
>
> Notepad is not a text editor to use in general and in particular for
> data transformation work. It has limited knowledge of the text format.
> If you need to do that on Windows use Wordpad or better yet Notepad++:
>
> https://notepad-plus-plus.org/
>
> >
> > 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.
>
> The original encoding was Win-10 (Japanese) correct?
>
> >
> > 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(dot)klaver(at)aklaver(dot)com>
> > *To:* s400t(at)yahoo(dot)co(dot)jp; rob stone <floriparob(at)gmail(dot)com>;
> > "pgsql-general(at)lists(dot)postgresql(dot)org"
> > <pgsql-general(at)lists(dot)postgresql(dot)org>
> > *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(at)yahoo(dot)co(dot)jp <mailto:s400t(at)yahoo(dot)co(dot)jp>
> 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(at)gmail(dot)com
> > <mailto:floriparob(at)gmail(dot)com>>
> > > *To:* Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>>; s400t(at)yahoo(dot)co(dot)jp
> > <mailto:s400t(at)yahoo(dot)co(dot)jp>;
> > > "pgsql-general(at)lists(dot)postgresql(dot)org
> > <mailto:pgsql-general(at)lists(dot)postgresql(dot)org>"
> > > <pgsql-general(at)lists(dot)postgresql(dot)org
> > <mailto:pgsql-general(at)lists(dot)postgresql(dot)org>>
> > > *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(at)yahoo(dot)co(dot)jp
> > <mailto:s400t(at)yahoo(dot)co(dot)jp> <mailto:s400t(at)yahoo(dot)co(dot)jp
> > <mailto:s400t(at)yahoo(dot)co(dot)jp>>
> > > 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>