Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

[s400t]

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.
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.
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 
 To: Adrian Klaver ; s4...@yahoo.co.jp; 
"pgsql-general@lists.postgresql.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, s4...@yahoo.co.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






   

!= ANY(array) does not behave as expected

[Chris Wilson]

Dear Postgres users,

I like using ANY(array) instead of IN (...), as we can pass the array as binary 
data, avoiding the need to render its contents (which might be integers) into a 
SQL string, for Postgres to parse them back into integers again, and it also 
works with an empty 
list.
 For example:

create table foo (id integer);
insert into foo (id) values (1), (2), (3);
select * from foo where id IN (1, 2); /* returns rows 1 and 2 */
select * from foo where id = ANY (ARRAY[1, 2]); /* returns rows 1 and 2 */

However, if we try to invert it by using the != operator, then we get 
unexpected results:

select * from foo where id NOT IN (1, 2); /* returns row 3 only, as expected */
select * from foo where id != ANY (ARRAY[1, 2]); /* returns all rows, 
unexpected */

I don't really understand why this is the case. I guess that perhaps an 
ANY-object has an equality operator that tests for membership of the array, but 
its inequality operator does something different. I don't understand what it's 
doing at all, or how it might be useful. Could anyone enlighten me?

I did find a workaround that may be useful to others (perhaps something to add 
to the documentation?):

select * from foo where NOT(id = ANY (ARRAY[1, 2])); /* returns row 3 only, as 
expected */

In a search for a solution or workaround, to pass arrays of IDs to exclude into 
queries, I noted that the manual 
says:

expression NOT IN (subquery)
The right-hand side is a parenthesized subquery, which must return exactly one 
column.
I tried to pass an expression that returns one column, but that failed:

select * from foo where id NOT IN (unnest(ARRAY[1, 2])); /* fails with 
"set-returning functions are not allowed in WHERE" */

But if I use a real subquery then it succeeds:

select * from foo where id NOT IN (SELECT * FROM unnest(ARRAY[1, 2])) /* 
returns row 3 only */

If the current behaviour of != ANY (ARRAY...) is not useful, then is there any 
support for (or opposition to) fixing it? And is it a bug that one can't use 
unnest in a NOT IN expression in the WHERE clause?

Thanks, Chris.

Re: != ANY(array) does not behave as expected

[Thomas Kellerer]

Chris Wilson schrieb am 07.12.2018 um 13:39:
> However, if we try to invert it by using the != operator, then we get 
> unexpected results:
> 
> select * from foo where id NOT IN (1, 2); /* returns row 3 only, as expected 
> */
> select * from foo where id != ANY (ARRAY[1, 2]); /* returns all rows, 
> unexpected */

id <> ANY (...) means: return "true" if at least one of the elements is not 
equal to the value on the left side. 

What you are looking for is the ALL operator

  select * 
  from foo 
  where id <> ALL (ARRAY[1, 2]); 

That is essentially the equivalent to NOT IN

Re: != ANY(array) does not behave as expected

[Thomas Kellerer]

Thomas Kellerer schrieb am 07.12.2018 um 13:48:
> Chris Wilson schrieb am 07.12.2018 um 13:39:
>> However, if we try to invert it by using the != operator, then we get 
>> unexpected results:
>>
>> select * from foo where id NOT IN (1, 2); /* returns row 3 only, as expected 
>> */
>> select * from foo where id != ANY (ARRAY[1, 2]); /* returns all rows, 
>> unexpected */
> 
> id <> ANY (...) means: return "true" if at least one of the elements is not 
> equal to the value on the left side. 
> 
> What you are looking for is the ALL operator
> 
>   select * 
>   from foo 
>   where id <> ALL (ARRAY[1, 2]); 
> 
> That is essentially the equivalent to NOT IN

See here for a longer explanation:

   https://stackoverflow.com/a/10675636

Re: psql profiles?

[Arthur Zakirov]

On 07.12.2018 01:34, Matt Zagrabelny wrote:



On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera > wrote:


Sure, just define a pg_service.conf file.
https://www.postgresql.org/docs/11/libpq-pgservice.html


Thanks Alvaro!

Is there any shorter version than:

psql "service=foo"

?

If not, I can make a shell alias that puts the "service=$@" into the 
command.


Also you can set environment variable PGSERVICE=foo in your .bashrc. Or 
you can just set variable PGHOST=db-host-1.example.com. In last case you 
don't need pg_service.conf file.


--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

PL/pgSQL HTTP Request

[Sathish Kumar]

Hi Team,

Do PL/pgSQL support to create a function to make HTTP request. We have a
requirement to send data to external server from Postgres DB using
HTTP/HTTPS Post Method.

Re: PL/pgSQL HTTP Request

[Pavel Stehule]

Hi

pá 7. 12. 2018 v 14:48 odesílatel Sathish Kumar  napsal:

> Hi Team,
>
> Do PL/pgSQL support to create a function to make HTTP request. We have a
> requirement to send data to external server from Postgres DB using
> HTTP/HTTPS Post Method.
>


Surely It doesn't support it. You can use some untrusted language - but it
is not safe.

you can use https://github.com/pramsey/pgsql-http extension. It is much
better, but still it not good idea. Stored procedures are perfect for work
inside database. Communication with outer world is not good

a) outer world is not transactional
b) outer world is too slow.

This is task for application (communication) server.

Regards

Pavel

Re: PL/pgSQL HTTP Request

[Sathish Kumar]

Hi Pavel,

We would like to use with Google Cloud Sql where third party extensions are
not supported.

On Fri, Dec 7, 2018, 9:55 PM Pavel Stehule  Hi
>
> pá 7. 12. 2018 v 14:48 odesílatel Sathish Kumar 
> napsal:
>
>> Hi Team,
>>
>> Do PL/pgSQL support to create a function to make HTTP request. We have a
>> requirement to send data to external server from Postgres DB using
>> HTTP/HTTPS Post Method.
>>
>
>
> Surely It doesn't support it. You can use some untrusted language - but it
> is not safe.
>
> you can use https://github.com/pramsey/pgsql-http extension. It is much
> better, but still it not good idea. Stored procedures are perfect for work
> inside database. Communication with outer world is not good
>
> a) outer world is not transactional
> b) outer world is too slow.
>
> This is task for application (communication) server.
>
> Regards
>
> Pavel
>

Re: PL/pgSQL HTTP Request

[Pavel Stehule]

pá 7. 12. 2018 v 14:58 odesílatel Sathish Kumar  napsal:

> Hi Pavel,
>
> We would like to use with Google Cloud Sql where third party extensions
> are not supported.
>

Then it easy - you cannot to do from Postgres.

Pavel


>
> On Fri, Dec 7, 2018, 9:55 PM Pavel Stehule 
>> Hi
>>
>> pá 7. 12. 2018 v 14:48 odesílatel Sathish Kumar 
>> napsal:
>>
>>> Hi Team,
>>>
>>> Do PL/pgSQL support to create a function to make HTTP request. We have a
>>> requirement to send data to external server from Postgres DB using
>>> HTTP/HTTPS Post Method.
>>>
>>
>>
>> Surely It doesn't support it. You can use some untrusted language - but
>> it is not safe.
>>
>> you can use https://github.com/pramsey/pgsql-http extension. It is much
>> better, but still it not good idea. Stored procedures are perfect for work
>> inside database. Communication with outer world is not good
>>
>> a) outer world is not transactional
>> b) outer world is too slow.
>>
>> This is task for application (communication) server.
>>
>> Regards
>>
>> Pavel
>>
>

Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

[Adrian Klaver]

On 12/7/18 12:28 AM, s4...@yahoo.co.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 
*To:* Adrian Klaver ; s4...@yahoo.co.jp;
"pgsql-general@lists.postgresql.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, s4...@yahoo.co.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.kla...@aklaver.com

Re: psql profiles?

[Matt Zagrabelny]

On Fri, Dec 7, 2018 at 7:42 AM Arthur Zakirov 
wrote:

> On 07.12.2018 01:34, Matt Zagrabelny wrote:
> >
> >
> > On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera  > > wrote:
> >
> > Sure, just define a pg_service.conf file.
> > https://www.postgresql.org/docs/11/libpq-pgservice.html
> >
> >
> > Thanks Alvaro!
> >
> > Is there any shorter version than:
> >
> > psql "service=foo"
> >
> > ?
> >
> > If not, I can make a shell alias that puts the "service=$@" into the
> > command.
>
>
>
Thanks for the hints and discussion about this.

Here's my final implementation for the curious and to close the loop:

# a zsh function to avoid having to type "service="

$ which pssql
pssql () {
psql "service=$@"
}

# and a zsh completion function:

$ cat ~/.fpath/_pssql
#compdef pssql

PG_SERVICES_CONF=~/.pg_service.conf

if [[ -r ${PG_SERVICES_CONF} ]]; then
compadd $(sed -nE 's/^ *\[(.*)\] *$/\1/p' ${PG_SERVICES_CONF})
fi

 It works like a charm!

Thanks for all the help!

-m

Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

[s400t]

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 existLINE 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 
 To: s4...@yahoo.co.jp; rob stone ; 
"pgsql-general@lists.postgresql.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, s4...@yahoo.co.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 
>    *To:* Adrian Klaver ; s4...@yahoo.co.jp;
>    "pgsql-general@lists.postgresql.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, s4...@yahoo.co.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

Re: psql profiles?

[Ron]

On 12/07/2018 10:45 AM, Matt Zagrabelny wrote:


On Fri, Dec 7, 2018 at 7:42 AM Arthur Zakirov > wrote:


On 07.12.2018 01:34, Matt Zagrabelny wrote:
>
>
> On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera
mailto:alvhe...@2ndquadrant.com>
> >>
wrote:
>
>     Sure, just define a pg_service.conf file.
> https://www.postgresql.org/docs/11/libpq-pgservice.html
>
>
> Thanks Alvaro!
>
> Is there any shorter version than:
>
> psql "service=foo"
>
> ?
>
> If not, I can make a shell alias that puts the "service=$@" into the
> command.



Thanks for the hints and discussion about this.

Here's my final implementation for the curious and to close the loop:

# a zsh function to avoid having to type "service="

$ which pssql
pssql () {
        psql "service=$@"
}

# and a zsh completion function:

$ cat ~/.fpath/_pssql
#compdef pssql

PG_SERVICES_CONF=~/.pg_service.conf

if [[ -r ${PG_SERVICES_CONF} ]]; then
    compadd $(sed -nE 's/^ *\[(.*)\] *$/\1/p' ${PG_SERVICES_CONF})
fi

 It works like a charm!


I made bash functions to do something similar, with just hardcoded server 
names.  It has auto-complete and allows me to use descriptive names instead 
of server names and custom postgres versions.


--
Angular momentum makes the world go 'round.

Re: != ANY(array) does not behave as expected

[Ken Tanzer]

On Fri, Dec 7, 2018 at 5:21 AM Thomas Kellerer  wrote:

> Thomas Kellerer schrieb am 07.12.2018 um 13:48:
> > Chris Wilson schrieb am 07.12.2018 um 13:39:
> >> However, if we try to invert it by using the != operator, then we get
> unexpected results:
> >>
> >> select * from foo where id NOT IN (1, 2); /* returns row 3 only, as
> expected */
> >> select * from foo where id != ANY (ARRAY[1, 2]); /* returns all rows,
> unexpected */
> >

> id <> ANY (...) means: return "true" if at least one of the elements is
> not equal to the value on the left side.
> >
> > What you are looking for is the ALL operator
> >
> >   select *
> >   from foo
> >   where id <> ALL (ARRAY[1, 2]);
> >
> > That is essentially the equivalent to NOT IN
>
 See here for a longer explanation:

>
>https://stackoverflow.com/a/10675636
>
>
>
You can also just do this:

select * from foo where NOT id = ANY (ARRAY[1, 2]);

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: != ANY(array) does not behave as expected

[Andrew Gierth]

> "Chris" == Chris Wilson  writes:

 Chris> However, if we try to invert it by using the != operator, then
 Chris> we get unexpected results:

Mr. De Morgan would like a word.
https://en.wikipedia.org/wiki/De_Morgan%27s_laws

In short, if you have a condition of the form (a OR b) and you want to
negate it, then you find that:

NOT (a OR b)  is equivalent to  (NOT a) AND (NOT b)

Since  x = ANY (array[1,2])  is equivalent to (x = 1) OR (x = 2), then
the negation would be (x != 1) AND (x != 2), not OR.

Which can be conveniently expressed as  x != ALL (array[1,2]).

So just as you interchange AND and OR when inverting the sense of a
condition, you also interchange ALL and ANY for exactly the same
reasons.

 Chris> expression NOT IN (subquery)

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN

 Chris> And is it a bug that one can't use unnest in a NOT IN expression
 Chris> in the WHERE clause?

No.

-- 
Andrew (irc:RhodiumToad)

Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

[Adrian Klaver]

On 12/7/18 9:04 AM, s4...@yahoo.co.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 
*To:* s4...@yahoo.co.jp; rob stone ;
"pgsql-general@lists.postgresql.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, s4...@yahoo.co.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 mailto:floripa...@gmail.com>>
 >    *To:* Adrian Klaver mailto:adrian

syntax error with alter type

[Kevin Brannen]

I'm running Pg 9.6.5 if it matters...

I'm trying to drop a value from an ENUM (type) and it seems like I'm following 
the fine manual yet I still get an error. For example:

nms=# create type alphabet as enum ('a', 'b', 'c', 'd');
CREATE TYPE

nms=# alter type alphabet drop attribute if exists 'c';
ERROR:  42601: syntax error at or near "'c'"
LINE 1: alter type alphabet drop attribute if exists 'c';
 ^
LOCATION:  scanner_yyerror, scan.l:1086

What am I doing wrong? The goal is to get rid of the 'c' value from the enum. 
Yes, you can assume I've already removed of all the 'c' values in the table 
where it's used.

Or does that statement not do what I think it does and I have to do the "create 
new type, change the table to use the new type, drop old type, rename new type 
to old type" routine?

Thanks,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: syntax error with alter type

[Adrian Klaver]

On 12/7/18 3:17 PM, Kevin Brannen wrote:

I’m running Pg 9.6.5 if it matters…

I’m trying to drop a value from an ENUM (type) and it seems like I’m 
following the fine manual yet I still get an error. For example:


nms=# create type alphabet as enum ('a', 'b', 'c', 'd');

CREATE TYPE

nms=# alter type alphabet drop attribute if exists 'c';

ERROR:  42601: syntax error at or near "'c'"

LINE 1: alter type alphabet drop attribute if exists 'c';

  ^

LOCATION:  scanner_yyerror, scan.l:1086

What am I doing wrong? The goal is to get rid of the ‘c’ value from the 
enum. Yes, you can assume I’ve already removed of all the ‘c’ values in 
the table where it’s used.


https://www.postgresql.org/docs/10/datatype-enum.html

"Although enum types are primarily intended for static sets of values, 
there is support for adding new values to an existing enum type, and for 
renaming values (see ALTER TYPE). Existing values cannot be removed from 
an enum type, nor can the sort ordering of such values be changed, short 
of dropping and re-creating the enum type.





Or does that statement not do what I think it does and I have to do the 
“create new type, change the table to use the new type, drop old type, 
rename new type to old type” routine?


Thanks,

Kevin

This e-mail transmission, and any documents, files or previous e-mail 
messages attached to it, may contain confidential information. If you 
are not the intended recipient, or a person responsible for delivering 
it to the intended recipient, you are hereby notified that any 
disclosure, distribution, review, copy or use of any of the information 
contained in or attached to this message is STRICTLY PROHIBITED. If you 
have received this transmission in error, please immediately notify us 
by reply e-mail, and destroy the original transmission and its 
attachments without reading them or saving them to disk. Thank you.



--
Adrian Klaver
adrian.kla...@aklaver.com

RE: syntax error with alter type

[Kevin Brannen]

On 12/7/18 3:17 PM, Kevin Brannen wrote:
> I'm running Pg 9.6.5 if it matters...
>
> I'm trying to drop a value from an ENUM (type) and it seems like I'm
> following the fine manual yet I still get an error. For example:
>
> nms=# create type alphabet as enum ('a', 'b', 'c', 'd');
>
> CREATE TYPE
>
> nms=# alter type alphabet drop attribute if exists 'c';
>
> ERROR:  42601: syntax error at or near "'c'"
>
> LINE 1: alter type alphabet drop attribute if exists 'c';
>
>   ^
>
> LOCATION:  scanner_yyerror, scan.l:1086
>
> What am I doing wrong? The goal is to get rid of the 'c' value from
> the enum. Yes, you can assume I've already removed of all the 'c'
> values in the table where it's used.

https://www.postgresql.org/docs/10/datatype-enum.html

"Although enum types are primarily intended for static sets of values, there is 
support for adding new values to an existing enum type, and for renaming values 
(see ALTER TYPE). Existing values cannot be removed from an enum type, nor can 
the sort ordering of such values be changed, short of dropping and re-creating 
the enum type.


>
> Or does that statement not do what I think it does and I have to do
> the "create new type, change the table to use the new type, drop old
> type, rename new type to old type" routine?

Adrian,

Thanks, I hadn't seen that "existing values cannot be removed from an enum 
type" since I was looking on the ALTER TYPE page.

So, can you (or anyone) help me understand what "alter type  drop 
attribute" is meant to do? I don't see "attribute" on the page you reference.

Thanks,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: syntax error with alter type

[Tom Lane]

Kevin Brannen  writes:
> So, can you (or anyone) help me understand what "alter type  drop 
> attribute" is meant to do? I don't see "attribute" on the page you reference.

IIRC, that drops a column from a composite type; it's more or less a
variant spelling of ALTER TABLE DROP COLUMN.

regards, tom lane