Database issues when adding GUI

[Rich Shepard]

This is an unusual message and I hope someone(s) here can offer insights
into the cause of the problem I've been wrestling with for the past couple
of weeks.

Context: For the past decade I've run my business tracking database from the
psql shell (currently with postgresql-12.2) and decided it's time to add a
frontend so it's a stand-alone desktop application. I'm learning to use
PyQt5 as the widget set and application framework.

The database contains three lookup tables: activitytypes, industrytypes, and
statustypes, all have a single column and few rows. So I've started with
these to get python3 and PyQt5-5.13.2 to run the SELECT query and display
the results in a QTableView.

The problem with all three is that my code produces an empty window and
hangs. There's no python error displayed and the application reports finding
the database but not the tables. For example, the activitytypes debugging
log contains:
INFO:root:found database
DEBUG:root:Defining model/view
DEBUG:root:model error:  Unable to find table activitytypes
DEBUG:root:about to execute select query
DEBUG:root:End of Program

I, and others on the python and pyqt mail lists and stackoverflow, can find
nothing wrong with the python code. This suggests it's something with the
database itself. But I have no idea where to look. The database structure
is:
bustrac=# \d
List of relations
 Schema |   Name|   Type   |  Owner 
+---+--+--

 public | activities| table| rshepard
 public | activitytypes | table| rshepard
 public | industrytypes | table| rshepard
 public | locations | table| rshepard
 public | organizations | table| rshepard
 public | organizations_org_nbr_seq | sequence | rshepard
 public | people| table| rshepard
 public | people_person_nbr_seq | sequence | rshepard
 public | projects  | table| rshepard
 public | statustypes   | table| rshepard
(10 rows)

What might stop a front-end application from finding a table that has been
readily accessed from the psql shell?

All suggestions and recommendations are needed.

TIA,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rob Sargent wrote:


This looks like a permissions problem, as though you are connecting as a
role/user without permission to select from the tables.


Rob,

I'm a trusted user for all my databases as I'm the only one using them.

bustrac=# \d
List of relations
 Schema |   Name|   Type   |  Owner 
+---+--+--

 public | activities| table| rshepard
 public | activitytypes | table| rshepard
 public | industrytypes | table| rshepard
 public | locations | table| rshepard
 public | organizations | table| rshepard
 public | organizations_org_nbr_seq | sequence | rshepard
 public | people| table| rshepard
 public | people_person_nbr_seq | sequence | rshepard
 public | projects  | table| rshepard
 public | statustypes   | table| rshepard
(10 rows)


Are these tables in a schema other than public.


Nope.


can you show a psql session which accesses these tables, including
connection string?


Not sure about the 'connection string' part but:
bustrac=# select * from activitytypes;
  act_name 


 Phone
 Email
 Fax
 Meeting
 Conference
 Referral
 Called me
 Other
(8 rows)

Did I answer your questions?

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Philip Semanchuk wrote:


I agree with Rich. Permissions, or you’re connecting as the wrong user.
You might find it helpful to simplify and not use a GUI application until
you get your connection issues sorted out. A simple command line Python
app that connects to the database and prints the result of “select * from
activitytypes limit 1” will enable quick(er) debugging of your connection
issues.


Philip,

I've used the psql shell for years. That's not a GUI.

bustrac=# select * from activitytypes;
  act_name 


 Phone
 Email
 Fax
 Meeting
 Conference
 Referral
 Called me
 Other
(8 rows)

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rob Sargent wrote:


Sorry, I see they are in public.  Still need you conection setttings, for
both psql and app.  And any ~/.pg* files.


Rob, et al.:

Somehow pg_hba.conf got lost. Only the sample is present in
/usr/share/postgresql-12.

I edited pg_hba.conf set DATABASE and USER to all and METHOD to trust.
Restarted postgres. Application still shows blank window and the log says it
cannot find the table.

Must be something else,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rob Sargent wrote:


psql --host machine --user role --dbname something
For your app it might be a config file with corresponding entries, or perhaps 
you command line invocation?

I suspect there's a mis-match between the two.


Rob,

$ psql --host salmo --user rshepard --dbname bustrac
psql: error: could not connect to server: could not connect to server: 
Connection refused
Is the server running on host "salmo" (127.0.1.1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "salmo" (192.168.55.1) and accepting
TCP/IP connections on port 5432?

Yet, /tmp has .s.PGSQL.5432= and .s.PGSQL.5432.lock

and I can access all databases using psql; e.g., 
$ psql jerrittmine 
psql (12.2)

Type "help" for help.

You're on the right path here. The server is running on salmo (the local
host) and accepting connections on port 5432. At least, that's how I'm
seeing it.

Regards,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:

What Philip is suggesting is to use Python only simple script to connect to 
database and retrieve from table. Something like:


import psycopg2

con = psycopg2.connect()
cur = con.cursor()
cur.execute('select * from activitytypes')
rs = cur.fetchall()
print(rs)


Adrian,


con = psycopg2.connect(bustrac)

Traceback (most recent call last):
  File "", line 1, in 
NameError: name 'bustrac' is not defined

That's because there's a problem with connecting to the database. See my
reply to Rob's message.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


Are you sure the app is connecting to the right database?



Tom,

The problem source is postgres telling me it cannot connect to the database
but I can do so directly using psql:

$ psql --host salmo --user rshepard --dbname bustrac
psql: error: could not connect to server: could not connect to server: 
Connection refused
Is the server running on host "salmo" (127.0.1.1) and accepting
TCP/IP connections on port 5432?

yet,

$ psql bustrac
psql (12.2)
Type "help" for help.

bustrac=#

I'm thoroughly confused not before encountering this issue.

Regards,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


What you've got there is that Unix-socket connections work, but TCP
connections do not. Check the server's listen_addresses setting. If that
says to allow connections on these IP addresses, next check your kernel
firewall.


Tom,

What I read on  is
# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASEUSERADDRESS METHOD
hostall all 127.0.0.1/32trust

Here, in /usr/share/postgresql-12/pg_hba.conf I have
# IPv4 local connections:
hostall all 127.0.0.1/32trust

The firewall is on the router, not this server/workstation.

And I did restart postgres after editing pg_hba.conf.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


I think you could clarify things quite a bit by enabling log_connections
and log_statement, and then watching the postmaster log while you run the
app.


Tom,

In /var/log/postgresql-12 the shut-down and restart so an error I don't
understand:

2021-06-07 08:46:51.980 PDT [1455] LOG:  received smart shutdown request
2021-06-07 08:46:51.983 PDT [1455] LOG:  background worker "logical replication 
launcher" (PID 1462) exited with exit code 1
2021-06-07 08:46:51.984 PDT [1457] LOG:  shutting down
2021-06-07 08:46:51.998 PDT [1455] LOG:  database system is shut down
2021-06-07 08:46:55.375 PDT [6708] LOG:  starting PostgreSQL 12.2 on 
x86_64-slackware-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
2021-06-07 08:46:55.375 PDT [6708] LOG:  listening on IPv4 address "127.0.0.1", 
port 5432
2021-06-07 08:46:55.378 PDT [6708] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2021-06-07 08:46:55.394 PDT [6709] LOG:  database system was shut down at 
2021-06-07 08:46:51 PDT
2021-06-07 08:46:55.398 PDT [6708] LOG:  database system is ready to accept 
connections
2021-06-07 08:47:21.108 PDT [6734] ERROR:  column pg_attrdef.adsrc does not 
exist at character 128
2021-06-07 08:47:21.108 PDT [6734] STATEMENT:  select pg_attribute.attname,
pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen,
pg_attribute.atttypmod, pg_attrdef.adsrc from pg_class, pg_attribute left
join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and
pg_attrdef.adnum = pg_attribute.attnum) where
pg_table_is_visible(pg_class.oid) and pg_class.relname = 'industrytypes' and
pg_attribute.attnum > 0 and pg_attribute.attrelid = pg_class.oid and
pg_attribute.attisdropped = false order by pg_attribute.attnum

Fixing this error might well fix the issues I'm experiencing; I don't know
where to start.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Alan Hodgson wrote:


If you don't specify a host name, psql/libpq connects using the UNIX
domain socket in /tmp. If you do specify a host name it connects using a
TCP socket. Your PostgreSQL doesn't seem to be listening on TCP, or
possibly you have a firewall issue.


Alan,

The firewall is external to this host. It's a Ubiquiti ER-X between the FiOS
and the switch connecting all hosts.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Edson Carlos Ericksson Richter wrote:


Are you sure it should be 127.0.1.1, not 127.0.0.1? AFAIK, localhost
should be 127.0.0.1
May be an issue in /etc/hosts for "salmo" host?


Edson,

salmo, 127.0.0.1 is the server/workstation that has everything installed. It
is localhost.
127.0.0.1   localhost.localdomain   localhost
127.0.1.1   salmo.appl-ecosys.com   salmo # for slrn

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command. You need to get a more up-to-date copy
that knows what to do instead.


Tom,

I appear to have a choice: 12.7 or 13.3. Which would be better for me to
upgrade?

Thanks!

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, David G. Johnston wrote:


Those are PostgreSQL versions...but it's your non-psql client software
that needs upgrading, not PostgreSQL.


David,

Ah! Now I understand.


Given that the messages above indicate you are presently running 12.2 you
should be upgrading to the current minor release in the v12 branch
regardless.


That's what I thought.

I'll upgrade 12-2 to 12.7, then upgrade PyQt5.

Thanks for clarifying,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


No this is because you did not use an actual connection string e.g:
psycopg2.connect("host=salmo user=rshepard dbname=bustrac")
Instead you used an undefined variable bustrac per the error message:
NameError: name 'bustrac' is not defined


Adrian,

First I'll upgrade 12.2 to 12.7, then I'll upgrade PyQt5.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


The code snippet I showed is not tied to PyQt5, it is using psycopg2 and
was just a way of seeing if you could connect to the database via Python
and get results.


Adrian,

Yes, it's a PyQt5 version issue.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command. You need to get a more up-to-date copy
that knows what to do instead.


Tom,

In what version was that removed? Now I have postgresql-12.7 installed and
when I try to access the activitytypes table it still fails with the same
error:

2021-06-07 10:36:38.304 PDT [29537] LOG:  starting PostgreSQL 12.7 on 
x86_64-slackware-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
2021-06-07 10:36:38.304 PDT [29537] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2021-06-07 10:36:38.310 PDT [29537] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2021-06-07 10:36:38.322 PDT [29538] LOG:  database system was shut down at 
2021-06-07 10:36:17 PDT
2021-06-07 10:36:38.330 PDT [29537] LOG:  database system is ready to accept 
connections
2021-06-07 14:19:45.491 PDT [31353] ERROR:  column pg_attrdef.adsrc does not 
exist at character 128
2021-06-07 14:19:45.491 PDT [31353] STATEMENT:  select pg_attribute.attname, 
pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen, 
pg_attribute.atttypmod, pg_attrdef.adsrc from pg_class, pg_attribute left join 
pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = 
pg_attribute.attnum) where pg_table_is_visible(pg_class.oid) and pg_class.relname 
= 'activitytypes' and pg_attribute.attnum > 0 and pg_attribute.attrelid = 
pg_class.oid and pg_attribute.attisdropped = false order by pg_attribute.attnum

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


See:

https://www.postgresql.org/docs/11/catalog-pg-attrdef.html
https://www.postgresql.org/docs/12/catalog-pg-attrdef.html

So in version 12.


Adrian,

Then it doesn't matter which minor version's installed. I've asked on the
PyQt mail list about this because the issue must be with the QPSQL driver.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, David G. Johnston wrote:


If all you did was upgrade the server, and not the client that is issuing
the query, then, yes, you will get the same error, since the server isn't
the problem.


David,

I also upgraded the client from PyQt5-5.13.2 to PyQt5-5.15.2. I'm hoping for
an answer there.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


How did you upgrade? Exact command please.


Adrian,

Because slackware64-14.2 had python3-PyQt-5.13.2 and the latest version in
-current64 is PyQt5-5.15.2-x86_64-3, I removed the former and installed the
latter.

The commands are 'removepkg ' and 'installpkg '.

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


Alright. What is the version of Qt installed on the machine. Do in
terminal:


$ ls /var/log/packages/ | grep qt
qt5-5.12.8-x86_64-1_SBo

among others.

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:



That will show what is currently installed, not what has been installed
over time. The bottom line is that this is Qt issue and to solve it is
going to require working from the Qt side and that is only going to work
by knowing what version of Qt(not PyQt) is being used.


This is the qt version installed: qt5-5.12.8-x86_64-1_SBo. Currently. Now.

If you want to know what prior versions were installed I can send you a list
of removed packages.

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


I'm guessing qt5-5.12.8 is not recent enough to deal with the changes in
Postgres 11+.


Adrian,

I agree. I've overlooked that PyQt is a wrapper around Qt and that's where
the drivers are installed.


Does your version of Slackware allow you to upgrade the Qt version to
5.15.x?


It should, unless it requires newer versions of libraries such as glibc.
I'll check first.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, David G. Johnston wrote:


The version 12 compatibility commit was done in 5.15 only (5.12 works up
to v11).


David,

Thanks for that information. I'll see if I can upgrade to 5.15.

Regards,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Sam Gendler wrote:


It still looks a lot like the salmo hostname is going to be a problem once
you have the client version sorted out. Postgresql is listening only on
127.0.0.1:5432 and pg_hba.conf only has entries for 127.0.0.1:5432, not
127.0.1.1 You can tell it to listen on all addresses with * in
listen_addresses, and then just put the appropriate value in pg_hba.conf
to allow connections on the address you will be using for the user you
will be using. That's in addition to the version mismatch problems you
seem to be having.


Sam,

The only use of 127.0.1.1 is for slrn, a newsreader I've not used in years.

I'm the only user on this system but I'll change listen_addresses to *.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rich Shepard wrote:


The only use of 127.0.1.1 is for slrn, a newsreader I've not used in
years.


Ah, I was looking in the wrong directory. /usr/share/postgresql-12/ should
have only the templates. The conf file postgres uses is in
/var/lib/pgsql/12/data/ and has this content:
# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
hostall all 192.168.55.0/24 trust
# IPv6 local connections:
hostall all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all trust
hostreplication all 127.0.0.1/32trust
hostreplication all ::1/128 trust

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:

2021-06-07 10:36:38.330 PDT [29537] LOG:  database system is ready to accept 
connections
2021-06-07 14:19:45.491 PDT [31353] ERROR:  column pg_attrdef.adsrc does not 
exist at character 128

"


Adrian,

Postgres dropped the pg_attrdev.adsrc column from the system table in
version 12.0. As you wrote yesterday, Qt5 added support for postgres-12 in
the QPSQL driver for 5.15.0. I'm now working on building Qt5-5.15.2 on my
slackware-14.2 host.

While QSqlDatabase is ready to accept connections it cannot recognize tables
using the installed version.

Regards,

Rich

Re: Aw: Re: Database issues when adding GUI

[Rich Shepard]

On Tue, 8 Jun 2021, Karsten Hilbert wrote:


The problem source is postgres telling me it cannot connect to the database
via TCP/IP
but I can do so directly using psql:
via UNIX domain sockets.


Karsten,

That's because psql is direct while PyQt5-5.12.3 cannot access postgres-12.x
tables. I'm working on getting Qt5-5.15.2 built.

Regards,

Rich

Use of '&' as table prefix in query

[Rich Shepard]

Long ago I wrote a query which was greatly improved (i.e., it actually
worked as intended) by help here):

/* This query selects all activity information for a named person */

SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active,
   o.org_name,
   l.loc_nbr, l.loc_name,
   a.act_date, a.act_type, a.notes, a.next_contact
FROM People AS p
 JOIN Organizations AS o ON o.org_nbr = p.org_nbr
 JOIN Locations AS l ON l.org_nbr = o.org_nbr and l.loc_nbr = p.loc_nbr
 JOIN Activities AS a ON a.person_nbr = p.person_nbr
WHERE p.lname = &p.lname AND p.fname = &p.fname;

I did not save the reason why the ampersand is used in the WHERE row selection
phrase and want now to learn why it's there. Probably needed to concatenate
separate names?

TIA,

Rich

Re: Use of '&' as table prefix in query

[Rich Shepard]

On Mon, 12 Jul 2021, Tom Lane wrote:


AFAICS this is invoking a prefix operator named "&". There is no such
operator built into Postgres. Maybe psql's "\do+ &" would jog your memory
about where yours came from.


tom,

I thought it wasn't part of postgres. I've no idea why it's there, but I'll
remove it and see what happens.

Thanks!

Rich

Re: Use of '&' as table prefix in query

[Rich Shepard]

On Mon, 12 Jul 2021, Rob Sargent wrote:


These look like value substitutions, usually done on the client at it
sends the sql. How is this sql getting to the server (presumably after
substitution).


Rob,

I was running queries from the psql back then. Now I'm adding a GUI
(tkinter) and using psycopg2.

Thanks,

Rich

Re: Use of '&' as table prefix in query

[Rich Shepard]

On Mon, 12 Jul 2021, Adrian Klaver wrote:



You should also follow Tom's suggestion and do:
\do+ &
in psql.


It's the bitwise 'and':
# \do+ &
   List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |   Function   | Description 
+--+---++-+--+-

 pg_catalog | &| bigint| bigint | bigint  | int8and 
 | bitwise and
 pg_catalog | &| bit   | bit| bit | bitand  
 | bitwise and
 pg_catalog | &| inet  | inet   | inet| inetand 
 | bitwise and
 pg_catalog | &| integer   | integer| integer | int4and 
 | bitwise and
 pg_catalog | &| macaddr   | macaddr| macaddr | macaddr_and 
 | bitwise and
 pg_catalog | &| macaddr8  | macaddr8   | macaddr8| 
macaddr8_and | bitwise and
 pg_catalog | &| smallint  | smallint   | smallint| int2and 
 | bitwise and
(7 rows)

which doesn't seem appropriate in this context.

Thanks,

Rich

Re: Use of '&' as table prefix in query

[Rich Shepard]

On Mon, 12 Jul 2021, Rob Sargent wrote:


Having a hard time seeing the value in p.lname = p.lname and straight sql.


Me, too. That's why I wanted an explanation. Seems to me I added it sometime
for no valid reason. As I've not run that query in a very long time I'll
clean up the query and test it with the next opportunity.

Rich

Re: Use of '&' as table prefix in query [RESOLVED]

[Rich Shepard]

On Mon, 12 Jul 2021, Rich Shepard wrote:


Long ago I wrote a query which was greatly improved (i.e., it actually
worked as intended) by help here):


Ah! It finally came back to me as I looked to revise it. What I want both
&p.lname and &p.fname are specific names passed in from the user. Now I know
what they represent I'll find just how to specify them using python and
psycopg2.

Rich

psycopg2 mail list subscription issues

[Rich Shepard]

I apologize for writing to the list, but my attempts to subscribe to the
psycopg2 mail list fail and I've not seen a response to my email message
sent to the mail list owner.

I don't know my postgres mail list password as I've not had a reason to use
it ... until now. Subscribing to the psycopg2 mail list and, apparently
other lists, requires a community account password which my password to this
mail list apparently is.

Thinking I might have a postgres community account (needed to subscribe to
that mail list) I tried the reset password link on this page

but no email message with instructions to reset my password were received.

This suggested I didn't have an account, so I tried to create one. The
immediate response is that I do have a community account. Checkmate. Can't
reset my password and I have an existing account with my email address.

Suggestions on how I can subscribe to the psycopg2 mail list are needed.

TIA,

Rich

Re: psycopg2 mail list subscription issues

[Rich Shepard]

On Tue, 13 Jul 2021, Diego wrote:

Here is the link to reset your password and manage your account: 
https://www.postgresql.org/account/reset/


Diego,

I tried the list management link and that didn't work. The URL you sent has
the same results as the password reset link on the community account page:
no URL to reset the password and an instant message from the Mail Delivery
System :

A message that you sent could not be delivered to one or more of its
recipients. This is a permanent error. The following address(es) failed:

  da...@fetter.org
host mail.fetter.org [46.226.110.146]
SMTP error from remote mail server after RCPT TO::
550 5.7.23 : Recipient address rejected:
Message rejected due to: SPF fail - not authorized. Please see 
http://www.openspf.net/Why?s=mfrom;id=rshep...@appl-ecosys.com;ip=217.196.149.56;r=

Thanks,

Rich

Re: psycopg2 mail list subscription issues

[Rich Shepard]

On Tue, 13 Jul 2021, Tom Lane wrote:


Is it possible that you're sending the list request mail out of some other
IP address?


tom,

No. I have a personal domain but use my business domain for all
business-related mail lists.

Thanks,

Rich

Formating psql query output

[Rich Shepard]

Until I finish building the python/tkinter/psycopg2 front end to my business
tracking tool I continue to work using the psql shell.'

I have a working .sql script that reports my contacts between two dates; the
script returns more columns than I want included in the report. I want to
pipe the output through an awk script to extract, in order, the columns I
need. I'm stuck at the point of defining options to psql.

The current command line is:
psql -d bustrac -f prospecting_log.sql -o contacts.txt --csv

However, using the --csv output conversion makes separate fields from a
varchar column that can contain commas with the text contents.

Without --csv I get normal psql output with column headings and separator
lines such as these:
 person_nbr |  act_date  | act_type  |  notes  | person_nbr |  lname   |   fname| org_nbr | org_nbr |   org_name 
++---+-++--++-+-+--


Is there an option that will retain the '|' separator but exclude the
headings? Reading the psql document page I don't see such an option.

TIA,

Rich

RE: Formating psql query output

[Rich Shepard]

On Mon, 19 Jul 2021, Basques, Bob (CI-StPaul) wrote:


-F separator
--field-separator=separator
Use separator as the field separator for unaligned output. This is equivalent 
to \pset fieldsep or \f.


Bobb,

I should have mentioned that I tried that. Without the --csv option the results
have the headings and separator. With --csv the field separator is ignored
regardless of postion within the command string.

Thanks,

Rich

Re: Formating psql query output [RESOLVED]

[Rich Shepard]

On Mon, 19 Jul 2021, David Santamauro wrote:


echo 'select 1,2,3,4;' | psql -At -F'|'
1|2|3|4

-A Switches to unaligned output mode. (The default output mode is aligned.) 
This is equivalent to \pset format unaligned.
-t Turn off printing of column names and result row count footers, etc. This is 
equivalent to \t or \pset tuples_only.
-F Use separator as the field separator for unaligned output. This is 
equivalent to \pset fieldsep or \f.


David,


HTH


Sure enough, it does. And I learned more on using psql options.

Thank you very much,

Rich

Re: Formating psql query output

[Rich Shepard]

On Mon, 19 Jul 2021, Adrian Klaver wrote:


Is there a reason you can't just restrict the query to the columns you want?


Adrian,

As far as I know I need to specify FK and PK columns when tables are joined;
I don't need those key columns in the output.

Thanks,

Rich

Re: Formating psql query output

[Rich Shepard]

On Mon, 19 Jul 2021, Rob Sargent wrote:


Can we see on line of the csv output? The field with commas should be in
quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\””
might, heavy on the might.


Rob,

Here's a redacted output line:

8,2019-04-08,Phone,Went to voice mail @ 14:48; didn't leave a message. Call
Wednesday morning,8,,,537,537,

No quoted text fields.

Rich

Re: Formating psql query output

[Rich Shepard]

On Mon, 19 Jul 2021, Rob Sargent wrote:


Postgres version?


postgresql-12.7-x86_64-1_SBo

Re: Formating psql query output

[Rich Shepard]

On Mon, 19 Jul 2021, Adrian Klaver wrote:

You need them in the JOIN and/or WHERE sections, but not necessarily in the 
field list in the SELECT portion.


Adrian,

I wondered about that and thought I needed to include them in the SELECT
phrase.

Thanks for the lesson.

Regards,

Rich

Selecting table row with latest date

[Rich Shepard]

I have a table of contacts (PK is the person_nbr and contact_date) and I
want to select only the row with the latest (most recent) contact_date. The
Comparison Date/Time sections in the postgres 12 doc doesn't appear to have
what I want, and when I try to use the max() aggregate function it throws an
error.

Please point me to the proper place in the docs where I can learn how to do
this.

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Rich Shepard wrote:


Please point me to the proper place in the docs where I can learn how to do
this.


If I use DISTINCT ON would this produce the most recent date for each
person_nbr?

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, David G. Johnston wrote:


Select distinct on (person_nbr) ….. order by person_nbr, contact_date
desc;


David,

Please clarify: would this produce the most recent contact_date for each
person_nbr? The manual reads that two rows (e.g., for the same person_nbr)
are considered distinct if they differ in any one column (e.g.,
contact_dates).

Still confused.

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Tom Lane wrote:


The best way is usually like

   select * from mytable order by contact_date desc limit 1;

If you have an index on contact_date this should work very well indeed.


Tom,

I don't have an index on that table. I'll add one and try you suggestion.

Thanks,

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Tom Lane wrote:


The best way is usually like
   select * from mytable order by contact_date desc limit 1;
If you have an index on contact_date this should work very well indeed.


tom,

I added an index on contact_date and the query returned only one row. Huh!
Not what I expected.

This is the script I need to fine-tune (and I've forgotten the role of sq
since someone suggested it a few years ago):

--
/* This query selects all whose next_contact date is today or earlier; no nulls.
   This version should select the most recent contact_date by person_nbr,
   order by person_nbr and next_contact date. STILL NEEDS WORK.
*/

select p.person_nbr, p.lname, p.fname, p.direct_phone, p.cell_phone, 
o.company_name, sq.*
from people as p
 join companies as o on p.company_nbr = o.company_nbr
 cross join
 lateral
 (select *
 from contacts as a
 where a.person_nbr = p.person_nbr and
 a.next_contact <= current_date and
 a.next_contact is not null
 order by person_nbr, a.next_contact ASC
 ) sq
 order by sq.next_contact ASC;
--

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Adrian Klaver wrote:


Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?


Adrian,

The table has 5 columns: person_nbr, contact_date, contact_type, notes, and
next_contact.

I want the query to find all person_nbr whose most recent contact_date has a
next_contact date <= today. I don't need prior contact_dates and their
next_contact dates because some go back several years. I want to know those
I need to contact again based on our most recent contact.

HTH,

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Rob Sargent wrote:


Did you try David J’s suggestion? or maybe


Rob,

Yes.


select person_nbr, max(next_contact) group by person_nbr where
next_contact < now();



A table with person_nbr (pk), next_contact would make this much easier.
Seems to me a person can only have one next-contact? (as opposed to all
future_contact)


The peoples table has person_nbr as the PK. The contacts table has multiple
rows for all contacts with that person_nbr. The contacts table has a
multicolum PK: person_nbr and contact_date as there is only one contact on
any given day for that person.

HTH,

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Rob Sargent wrote:


Yeah, but my quibble is the the table you described up-thread. Your
contact table contains next_contact? I think that column should be
normalized out.


Rob,

Why should I have a separate table with one column: next_contact? The
next_contact date is associated with a person and the date that person was
contacted.

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Adrian Klaver wrote:


So take David Johnston's query:
Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc;


Adrian,

contact_date 
--

 2021-08-17
 2019-05-14
 2019-05-15
 2021-08-17
 2018-04-05
 2021-08-17
 2018-04-05
 2021-07-23
 2019-04-01
 2019-05-10
 2019-03-15
 2021-08-17
 2019-04-16
 2019-05-15
 2021-08-17
 2019-05-29
 2021-08-17
 2019-05-29
 2021-07-23
 2021-08-12
...


and add the next_contact filter:

Select distinct on (person_nbr) * from contacts where next_contact <= 
current_date order by person_nbr, contact_date desc;


Select distinct on (p.person_nbr) * from c.contacts
from people as p, contacts as c
where cnext_contact <= current_date and
p.person_nbr = c.person_nbr
order by p.person_nbr, c.contact_date desc;

$ psql -d bustrac -f test_query.sql
psql:test_query.sql:5: ERROR:  syntax error at or near "from"
LINE 2: from people as p, contacts as c
^

I still am missing quite a bit.

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Thu, 19 Aug 2021, Rob Sargent wrote:


sorry, wasn’t clear: person_nbr, next_contact
On the premise that there can only be one next date.


Rob,

I wasn't sufficiently clear. The contacts table has a row for each
(person_nbr, contact_date). For each row there's also either a next_contact
date or that column is null (e.g., pandemic-related no longer at that
company).

Regards,

Rich

Re: Selecting table row with latest date [RESOLVED]

[Rich Shepard]

On Thu, 19 Aug 2021, David G. Johnston wrote:


Yeah, you wrote two from clauses…


David,

Mea culpa! I did. Got that fixed.

Now, this query:
---
Select distinct on (p.person_nbr) p.person_nbr,
c.contact_date, max(c.next_contact) as next_contac from contacts,
people as p, contacts as c
where c.next_contact <= current_date and
p.person_nbr = c.person_nbr
group by p.person_nbr, c.contact_date
order by p.person_nbr, c.contact_date desc;
---
results in usable information; e.g.,
 person_nbr | contact_date | next_contact 
+--+-

  1 | 2021-07-21   | 2021-07-26
  4 | 2021-07-28   | 2021-08-16
  6 | 2021-07-28   | 2021-08-16
  9 | 2019-03-26   | 2020-11-17
 11 | 2018-04-05   | 2020-11-17
 13 | 2021-07-28   | 2021-08-16

My thanks to all of you,

Rich

Re: Selecting table row with latest date [RESOLVED]

[Rich Shepard]

On Thu, 19 Aug 2021, David G. Johnston wrote:


I thought you said (p.person_nbr, c.contact_date) is already unique?


David,

Yes, that's the PK for the contacts table. I'm still unsure what needs to be
explicitly included in a query. Quite often I leave out a column and
postgres tells me it needs to be included in order by or group by.

Rich

Re: Selecting table row with latest date [RESOLVED]

[Rich Shepard]

On Thu, 19 Aug 2021, David G. Johnston wrote:


Well, in this case I suspect you had made a different mistake which caused
the error message (probably the max(c.next_contact)) but instead of
solving the original problem (removing the max(...)) you decided that two
wrongs (adding or extending a group by) would hopefully make a right
(which it did, technically). But while the query works it is definitely
not conceptually correct.


David,

I'm not at all surprised as I use postgres infrenquently. Once I have
queries producing results I need for my business tracking or client data I
just use them. I'm neither a professional DBA nor database developer and use
a variety of technical applications dependent on project needs.

And I'm quite used to being chastised by those who work primarily with a
single application all day every day and I accept that I'm not an expert.

Regards,

Rich

Re: Selecting table row with latest date

[Rich Shepard]

On Fri, 20 Aug 2021, Francisco Olarte wrote:


Is your next_contact really dependent on the contact record (
person+contact_date? ).


Francisco,

Yes.


I mean, your tables seem CRM like. You stated next_contact for old
contacts is not interesting. It seems next_contact is associated just
by a person, something like this:


Not true. The history of contacts is highly valuable.


  Is the nc for the previous record useful for anything ? ( It seems to be
nor useful for calling, but you may want to do reports to see next-contact
correlation with the contacts on another records )


Yes.


To me, and maybe others, it seems there is only one valid next-contact
date, potentially null, which should go in the person record, and may be
the contacts table should capture the value of next-contact at the time
the contact was made for analysis. This also makes finding contacts to be
made soon easier.


That's because you don't know my business: I'm currently in my twenty-ninth
year as a consultant helping clients avoid and resolve environmental issues
and have long ago found what information I need and use to efficiently run
my business.

Regards,

Rich

Re: The tragedy of SQL

[Rich Shepard]

On Mon, 13 Sep 2021, Guyren Howe wrote:


They are making a decent decision. SQL is a *fucking terrible* language,
which I don’t blame them for not wanting to learn.



SQL is not the problem. Problem are the devs. I love SQL. I hate orms.
The problem with databases is people refuse to treat it as the entity it
is and want to use their beautiful OO system. Problem is databases are
not OO. We need to recognize that and treat databases as databases.


Guyren/Hemil,

As a non-SQL expert who's used postgres since 1997 I've come to believe the
basic issue is that SQL is based on sets, neither procedural or object
oriented. Few people think in sets so they try to fit SQL into what they
know rather than understand the how sets work.

Rich

Re: SQL queries as sets: was The tragedy of SQL

[Rich Shepard]

On Wed, 15 Sep 2021, Steve Litt wrote:


Rich, could you please elaborate on SQL queries being based on sets? I
never thought of it that way, and would like to hear your related
thoughts.


SteveT,

In the 1980s, when there were computer magazines such as Byte and Database
Administrator (among many others), Joe Celko wrote a monthly database column
in one (or more, I forget these details). This lead me to buy and read his
books, including 'SQL for Smarties' and its many editions, 'SQL
Programming', and 'Thinking in Sets: Auxilliary, Temporal, and Virtual
Tables in SQL'.

I reccomend his books to answer your questions and give you a sound
foundation.

Regards,

Rich

Need help understanding error message

[Rich Shepard]

In a database table I have these rows:
# select * from contacts where person_nbr=6;
 person_nbr | contact_date | contact_type | 
 not
es   | next_contact 
+--+--+-

-+--
  6 | 2018-04-05   | Phone| Left message @ 9:39.
 |
  6 | 2019-05-14   | Phone| He can call me if issues do come 
up. |
  6 | 2021-08-17   | Email| Sent message 2.
 | 2021-10-06
  6 | 2021-07-23   | Email| Sent message 1.
 | 2021-07-28
  6 | 2021-07-28   | Email| Sent message 1.
 | 2021-08-16
(5 rows)

When I try to update this table with:
(6,'2021-10-26','Email','message 3','2012-11-16'),
psql reports this error:
psql:insert_into_contacts.sql:31: ERROR:  duplicate key value violates unique constraint 
"activities_pkey"
DETAIL:  Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, Email) 
already exists.

The PK for this table is:
PRIMARY KEY, btree (person_nbr, contact_date, contact_type)
so there should be no conflict as the contact_date in the update is not
already in the table.

I don't understand the error as it's not occurred before when updating this
table.

Insight needed,

Rich

Re: Need help understanding error message

[Rich Shepard]

On Tue, 26 Oct 2021, Rob Sargent wrote:


What do you get when you select * where person_nbr = 6 and contact_date =
‘2021-10-26’ and contact_type = ‘Email’ from activities; ?


Rob,

 person_nbr | contact_date | contact_type | notes | next_contact 
+--+--+---+--

(0 rows)

Rich

Re: Need help understanding error message

[Rich Shepard]

On Tue, 26 Oct 2021, Ron wrote:


Show us the actual UPDATE statement.


Ron,

insert into contacts (person_nbr,contact_date,contact_type,notes,next_contact) 
values
(1,'2021-10-26','Email','message 3','2012-11-16'),
(4,'2021-10-26','Email','message 3','2012-11-16'),
(6,'2021-10-26','Email','message 3','2012-11-16'),
(3,'2021-10-26','Email','message 3','2012-11-16'),
(6,'2021-10-26','Email','message 3','2012-11-16'),
...
);

Re: Need help understanding error message

[Rich Shepard]

On Tue, 26 Oct 2021, Rob Sargent wrote:


Are you doing a batch of updates,


Yes,


... and including/generating a duplicate in there?


No.

Rich

Re: Need help understanding error message [RESOLVED]

[Rich Shepard]

On Tue, 26 Oct 2021, Rob Sargent wrote:


Aren’t lines 3 and 6 duplicates?


Ah, shoot! The second was supposed to be 16 and that's how I saw it when I
scanned the list.

Thanks!

Rich

Re: Design database schemas around a common subset of objects

[Rich Shepard]

On Mon, 8 Nov 2021, Erik Wienhold wrote:


One thing I already know that may differ is how modelled objects are named
in the real world.  We deal with plots of land, hence the geographic data.
Depending on the region, these plots may use different naming schemes
relevant to legal documents.  The core objects would use a generated
surrogate key but each project would have a dedicated relation that maps the
real-world names of those plots to their surrogate keys.  The real-world
names can be normalized into multiple attributes instead of just storing
names as text. This normalization may vary between projects, e.g. different
number of attributes.


Erik,

How about a table 'plot' that holds the real-world name with an abbreviation
(as the PK) and other information relevant to plots? That seems to be what
you're describing above but explained differently. Land plots, like othter
geographic locations, always have surrogate keys because there's no natural
key associated with it. I use site_nbr, samp_nbr, and similar PKs because
there's nothing inherently associated with those attributes. Here in the US
using SSN (social security numbers) for people are a natural key as is the
VIN (vehicle identification number) for ... vehicles.

Rich

Fresh eyeballs needed: input into error

[Rich Shepard]

I'm getting a syntax error with an input into statement and I cannot see the
cause.

A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values 
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);


2: ERROR:  syntax error at end of input
LINE 2: (
 ^
What do I keep missing?

TIA,

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, Adrian Klaver wrote:


I'm guessing some sort of hidden character.


Adrian,

The 'insert into ...' string is a template I've been using for years. It's
worked earlier today, too.


What client are you using to run this?


psql in a linux virtual terminal.


Where is the query string coming from?


I type each row to add to the table by hand.

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, Adrian Klaver wrote:


I'm guessing some sort of hidden character.


Adrian,

Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d  -f
 in a v.t. or entering 'insert into people values (...)' within
the psql shell.

All throw the same error.

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, David G. Johnston wrote:


What version of PostgreSQL?


David,

12.7


Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.


Well, as I wrote, that syntax has worked for years, including earlier this
morning. Perhaps it will work again tomorrow when I log back in to the
workstation.

Thanks,

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, Tom Lane wrote:


so there's nothing wrong with the syntax as-presented. I agree with
Adrian's guess about invisible characters in your input file; perhaps a
control-D or ASCII NUL would produce that symptom.


tom,

It's probably a transient glitch that will go away after I log out.

Thanks,

Rich

Re: Fresh eyeballs needed: input into error [FIXED]

[Rich Shepard]

On Tue, 26 Apr 2022, Rich Shepard wrote:


It's probably a transient glitch that will go away after I log out.


Yep. I logged out and back in just now. The insert script worked as it
always had before and both rows were added to the table.

I've seen these sort of hang-ups before and didn't want to log out, back
in, and recreate the working environment. Had I done so I'd have saved a lot
of time.

Thanks for your inputs, everyone,

Rich

Re: Fresh eyeballs needed: input into error [FIXED]

[Rich Shepard]

On Tue, 26 Apr 2022, Bruce Momjian wrote:


I am curious what OS psql was using that was fixed by a re-login?


Bruce,

This desktop's running Slackware64-14.2.

Regards,

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Wed, 27 Apr 2022, Laurenz Albe wrote:


test=> insert
test-> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  syntax error at or near "insert"
LINE 2: insert into people (person_nbr,lname,fname,job_title,company...
   ^


Laurenz,


From time to time I mis-type a character or make another error and psql

responds with a different prompt and no explicit explanation. I'll then type
C-c and get the default => prompt back. Your example is appreciated and may
very well have been the cause, but I kept trying at the default prompt.

I'll re-read the psql man page to better understand the different prompts.

Thank you,

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Wed, 27 Apr 2022, Rob Sargent wrote:


Have you tried \r instead of C-c?


Rob,

No, I haven't. I'll try it to reset the query buffer the next time I make a
mistake entering a command.

Thanks,

Rich

Re: Fresh eyeballs needed: input into error [FIXED]

[Rich Shepard]

On Wed, 27 Apr 2022, Francisco Olarte wrote:


Where you using cut&paste / selection or something similar? Lately I've
been getting weird errors on paste operations due to bracketed paste,
which are puzzling, It seems to have interactions with readline and other
stuff depending on where the cursor is and some other things. Last I
remembered when pasting some things to guile invisible chars ( paste
brackets ) where intercepted by its REPL leading to bizarre errors. And
sometimes after a relogin things are done a bit different and make problem
disappear.


Francisco,

Yes. I often use the trackball to highlight plain ASCII text, including the
enclosing '...', and paste it to the psql shell command line. Most of the
time it works. When psql presents prompt 2 instead of command success I
re-enter by re-keyboarding. That usually clears up the issue.

Thanks,

Rich

External psql editor

[Rich Shepard]

I do all my postgres work using the psql shell. Editing a command reguires
moving character-by-character and I'd like to use my small text editor (joe)
because it allows more control over line movement.

A web search found a stackexchange thread that suggested adding to
~/.bash_profile the line:
export PSQL_EDITOR=/usr/bin/joe
so I did this yesterday.

Today I've learned that the keyboard chords I use in joe in other
applications aren't working here. For example, C-w should delete the word to
the right of the point (cursor location). It doesn't. Instead, it deletes
from the cursor postion to the head of the line. C-x doesn't move the cursor
one word to the right, but cancels the command.

Is there a way for me to specify use of joe at the psql command line? (I'd
use emacs but that's gross overkill.)

Regards,

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Jan Wieck wrote:


What you are missing is that even though the PSQL_EDITOR env variable is
set, psql itself doesn't emulate that editor's behavior natively. You need
to actually launch the editor (possibly while having a partial query in
the buffer) with the \e command.


Jan,

Ah! That wasn't mentioned in the thread I read.


While in psql, type \e and Enter. You will have the current query buffer
in the editor. You can do this at the end of a partial (not yet semicolon
terminated) query.


Can I set it before entering any command or better yet, when I invoke psql?

Thanks very much,

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Francisco Olarte wrote:


I do a similar thing, but normally edit queries in an editor window and
just use selection or clipboard to paste them into the xterm where I have
psql running. I also used joe a lot ( its key sequences where easy coming
from wordstar(cp/m->msdos) ).


Francisco,

Joe can also be configured to use emacs chords which lets my fingers use the
same keys in both editors. :-)


What do you mean by "here"? IIRC PSQL_EDITOR sets the editor for \e, not
for the psql command line. For that you could try writing a binding for
readline ( which you could also use in bash if you like them ) with the
joe keyseqs, but I fear it's editing model is a nit different.



I've done "PSQL_EDITOR=joe psql" to refresh my memory and it is in
fact as I remembered.

The use of joe AS EDITOR for a single command can be done with the above
method. The use of joe keys for editting the normal psql line would
probably require readline wizardry.

I've read joe has slave shell sessions. Other thing you could try ( I've
done it with emacs shell mode ) is use that, but I fear it only works well
with single line queries. Or look if it has some kind of sql modes (
interactive sql, not sql-script-syntax-highlight ).


Good information. Thanks. I'll dig into joe and see what's possible.

Regards,

Rich

Mail list manager request

[Rich Shepard]

Looking at the postgresql.org web site I could not find the mail list
manager for direct contact so I apologize for writing here.

I use procmail to sort incoming messages to the proper file. Messages from
the mail list come with varying information at the bottom of the headers; it
could be From:, Cc:, cc:, or in two cases List-ID:. Because of the
inconsistency I cannot get a recipe that consistently puts the mail list
messages in the proper file so they end up in my alpine INBOX where I need
to manually redirect them;

Please modify the MLM software to use the List-Id: on all messages that way
my procmail recipe:

:0:
* ^List-Id:.*
database

will put incoming threads and messages where they belong.

Thanks in advance,

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Jan Wieck wrote:


Not that I know of. \e starts the external editor and you have to save and
exit that editor to get back to psql in order to execute it. IMHO the
whole construct has very limited usability.


Jan,

I tried, unsuccessily, to use \e. Entering it while a command is displayed
does nothing. So I'm doing something wrong.

I'll continue using character-by-character movement.

Thanks again,

Rich

Re: Mail list manager request

[Rich Shepard]

On Fri, 29 Apr 2022, Kris Deugau wrote:


At a not-entirely-wild guess, based on replies to your recent couple of
posts, the messages that went astray were the direct mail copies that
never passed through the list in the first place. I use an almost
identical recipe myself (and a matching one for psql-admin) and I don't
recall seeing stray mail since the list platform migration.


Kris,

That does seem to be the case. The most recent one was To: me and the mail
list, From: the sender. So I was not sent to only the mail list.


I catch most of those direct-mail copies with this addition:

:0
* ^TO_pgsql-.*@(lists.)?postgresql.org
$DEFAULT/.postgres/


Added here. Thanks.

Personally, I try to keep mail to any of the lists I read purely on-list 
unless a personal CC has been explicitly asked for.


I have always responded to the list so others interested in the thread can
see all messages.

Regards,

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Jan Wieck wrote:


Did you hit Enter after \e ?


Jan,

Yes. For example, I put a previous command at the prompt to be modified. It
began with 'insert ...' so I added an initial \e to the command. psql told
me that \einsert is not a valid command after I pressed the [Enter] key.

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Reid Thompson wrote:


https://linuxgazette.net/issue14/bashtip.html  may of of use.


Reid,

I've had no issues using joe in any v.t. running an application (e.g.,
alpine) or by itself. Apparently, psql is different.

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Jan Wieck wrote:


It is the other way around, like in
postgres-# select now()\e


Jan,

That does make a difference. Now I'm learning how to end the edit and return
from joe to the psql shell. The [Enter] key wraps the long line; probably
C-x will do the job.

Many thanks,

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, David G. Johnston wrote:


You type "insert", realize you want an editor for this, hit enter
(multi-line mode is psql), type \e, hit enter again, your editor appears
with "insert" already in place from the query buffer. Upon returning you
are given a new buffer with the contents of whatever you typed into the
editor pasted in.


Thank you, David.

Rich

Re: External psql editor

[Rich Shepard]

On Fri, 29 Apr 2022, Rich Shepard wrote:


... probably C-x will do the job.


Actually, it's C-k x, the usual joe save command.

My thanks to all because this new skill is saving me much time and effort.

Regards,

Rich

Re: External psql editor

[Rich Shepard]

On Mon, 2 May 2022, Reid Thompson wrote:


Apparently, psql is different.



I believe that psql also uses readline, so my thought was that maybe these
instructions could enable you to map the 'move' keystrokes that you're
familiar with to be used while on the psql command line. A very quick test
seems to indicate that you can.


Reid,

Nope. No difference. Learning to put the '\e' at the end of the command line
rather than at the head did the job.

All's well.

Thanks,

Rich

Re: External psql editor

[Rich Shepard]

On Mon, 2 May 2022, Tom Lane wrote:


Perhaps your psql is built against libedit rather than readline.


Tom,

Could be I use the SlackBuilds.org build script.

Regards,

Rich

Automatic PK values not added to new rows

[Rich Shepard]

The People table has 965 rows; the table structure is:
   Table "public.people"
Column | Type  | Collation | Nullable | 
 Default

---+---+---+--+-
 person_nbr| integer   |   | not null | 
nextval('people_person_nbr_seq'::reg
class)
 lname | character varying(32) |   | not null | '??'::character 
varying
 fname | character varying(15) |   | not null | '??'::character 
varying
 job_title | character varying(48) |   |  |
 company_nbr   | integer   |   |  |
 loc_nbr   | integer   |   | not null | 1
 loc_phone_ext | character varying(32) |   |  |
 direct_phone  | character varying(15) |   |  |
 direct_fax| character varying(15) |   |  |
 cell_phone| character varying(15) |   |  |
 email | character varying(64) |   |  |
 active| boolean   |   | not null | true
 comment   | text  |   |  | 
Indexes:

"people_pkey" PRIMARY KEY, btree (person_nbr)
Foreign-key constraints:
"people_org_nbr_fkey" FOREIGN KEY (company_nbr) REFERENCES 
companies(company_nbr) ON UPDATE CASC
ADE ON DELETE RESTRICT

I'm trying to insert 15 new rows to that table by inserting all columns
except the first one.. Psql tells me:
psql:insert_into_people.sql:16: ERROR:  duplicate key value violates unique constraint 
"people_pkey"
DETAIL:  Key (person_nbr)=(683) already exists.

person_nbr 683 is not in any row to be inserted.

I was under the impression that the person_nbr for the new rows would start
with 966 but that's apparently not happening.

What have I missed?

Rich

Re: Automatic PK values not added to new rows

[Rich Shepard]

On Wed, 25 May 2022, Adrian Klaver wrote:


Do:
select * from people_person_nbr_seq;
and report back the results.


Adrian,

Huh!
bustrac=# select * from people_person_nbr_seq;
 last_value | log_cnt | is_called 
+-+---

683 |  32 | t
(1 row)

It's out of sync with
 select max(person_nbr) from people;

Is there a way for me to synchronize the two?

Thanks,

Rich

Re: Automatic PK values not added to new rows

[Rich Shepard]

On Wed, 25 May 2022, David G. Johnston wrote:


The value the sequence provides next is wholly independent of everything
except the state of the sequence. It doesn’t care how many rows any table,
even its owner, has. The very existence of the delete command should make
this self-evident.


David J.,

I didn't know that.

Thanks,

Rich

Re: Automatic PK values not added to new rows

[Rich Shepard]

On Wed, 25 May 2022, Adrian Klaver wrote:


What is max(person_nbr)?


bustrac=# select max(person_nbr) from people;
 max 
-

 965
(1 row)

Rich

Re: Automatic PK values not added to new rows [RESOLVED]

[Rich Shepard]

On Wed, 25 May 2022, Adrian Klaver wrote:


From:
https://www.postgresql.org/docs/current/functions-sequence.html
SELECT setval('people_person_nbr_seq', 965);


Adrian,

Thanks. I didn't know where to look in the docs.

Regards,

Rich

Re: Automatic PK values not added to new rows

[Rich Shepard]

On Wed, 25 May 2022, Thomas Kellerer wrote:


If you want to prevent such a situation in the future, you might want to
consider defining those columns as "generated always as identity" instead
of "serial".

Then you'd get an error if you try to bypass the database generated values.


Thanks, Thomas.

Rich

Re: Need a DB layout gui

[Rich Shepard]

On Mon, 24 Jun 2019, David Gauthier wrote:


I've been poking around
https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
to see if there is anything that can read PG metadata in and display it
graphically in a gui. You know, the kind of picture that is 1 block per
table with 1->many arrows connecting up the primary/foreign keys of
different tables. SQL Power Architect looked promising, but fails to do
the linkage for some PK-FK relationships (buggy?) .

Any suggestions on what might do this?


Looks like you want a ER (entity relationship) diagrammer. Take a look at
dbeaver .

Rich

Re: Recomended front ends?

[Rich Shepard]

On Wed, 7 Aug 2019, Igor Korot wrote:


On top of what already been said - make sure that the product you are
about to start working on will have its requirements clear and concise.


This is a critical process that needs to be developed in depth. One
criterion that will guide your choice of UI is whether the database will be
accessed only on the LAN or also remotely. For the former, consider using
Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based
application using Django.

HTH,

Rich

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Stuart McGraw wrote:


I would be a little cautious about Django.



Specifically IIRC it insists that tables have a single-column primary
keys.


Stuart,

I looked seriously at Django and did not encounter that limitation. However,
I did learn that I'm not a web application developer nor do I want to be.
The applications I develop, primarily for my own business needs. use
SQLAlchemy and that allows multi-column primary keys. That's a necessity for
many-to-many tables (or SA classes).

I suspect that Django also allows multi-column primary keys but the syntax
might not be obvious.

Regards,

Rich

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Adrian Klaver wrote:


Unfortunately it does not:
https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys

Given that the issue:
https://code.djangoproject.com/ticket/373
is 14 years old does not inspire confidence that it will change anytime soon.


Adrian,

That's really interesting. I don't see how a framework cannot implement
multi-column PKs.

Many databases I have include tables for samples (geochemical, biological,
physical) where the PK for each row is location, date, parameter. Good thing
I don't like browser user interfaces, eh? :-)

Thanks for the information,

Rich

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Tim Clarke wrote:


We tried Django without any pleasant results.


Tim,

That's unexpected and too bad.


I'd also caution using MS Access, we're desperate to get away from it.
Sharing code has challenges and it is horribly aggressive with caching
unless you use un-bound forms and write all the CRUD interface code
yourself.


Slightly off-topic, but I've not seen anything good about Access. My
understanding is it's a flat-file database intended as a user front end to
Microsoft's relational database product. My experiences with those who use
it have been painful.

Just yesterday I downloaded a very large database of fisheries data from a
federal agency and have started translating it to postgres using the
mdbtools. There's no schema provided, only 32 pages of table columns and
types without descriptions of the column names. No primary keys, no foreign
keys, and only 66 tables were found in the .mdb file while all table names
starting with s through z were not available. There are also many tables
that hold redundant data which should not exist as the contents are easily
generated by SQL queries. It will take me a while to make it a working
relational database.

Rich

Converting Access .mdb to postgres

[Rich Shepard]

I have the need to convert a flat-file Access database to postgres. I've
exported the .mdb tables using mdbtools. There's an accompanying metadata
PDF with column names and data types for each of the 84 tables, but no
description of the tables or column headings. I've asked the agency to
provide that information ... if they have it. No table has primary or
referential keys and, of course, there's no E-R diagram of the schema.

If anyone's done this I'd appreciate learning from your experiences. And I
assume there are no tools to automate all or part of the process so it must
be done manually.

Among the tables are many lookup tables. I don't know whether to leave them
as tables or apply a different structure to them.

Advice, suggestions, and recommendations are all welcome.

TIA,

Rich