0

What am I missing?

screenshot of both results

I've connected on console to a DB I have populated with data, using a socket connection. I've validated that the table in question has the rows I expect. When I try and run a SELECT statement in psql the response comes back with a summary of the table columns and "(0 rows)" but when I query the table in any other tool (rails console, TablePlus) I see all of the data I expect. TablePlus/rails are also configured to connect via socket. I've made sure to log in to the same user account for all three.

Here is how I am connecting via psql: psql connection info

And here is my Tableplus connection setup:

TablePlus connection info

dbugger
  • 15,868
  • 9
  • 31
  • 33
machina.ex.canon
  • 15
  • 5
  • 1
    You're most likely connected to different databases or using different search paths for the different queries. Without the exact connection information, hard to help you – Frank Heikens Apr 05 '23 at 13:58
  • I kind of wondered if that was somehow the case; I'm not sure how I'd have identical tables if I'm in different DBs. I'll add the connection strings to the queries in just a minute, thank you! – machina.ex.canon Apr 05 '23 at 14:00
  • I've updated the question with summaries of the connections I've used. In the console example I'm connecting as the user `aaron` and in the Tableplus case as the user `postgres`, but I have also tried `su postgres` in console and connected as that user and see the same results when I do that. – machina.ex.canon Apr 05 '23 at 14:08

1 Answers1

0

Ok, thanks very much to Frank Heikens' answer above, I eventually noticed that Tableplus doesn't explicitly clarify how it's connected by default, and it lists 127.0.0.1 as the connection it is using rather than a socket address. I tried adding -h 127.0.0.1 in the console when connecting with psql and was indeed able to view the data I expected. It looks like I forgot that I installed Postgres locally way back and then later migrated to connecting to a docker instance, apparently via a network connection rather than a socket file shared to my hard drive like I had thought.

So, to sumamrize: I was indeed connecting to different DBs. In the first case using psql I was connecting to a Postgres instance running natively on Ubuntu, and with TablePlus/ruby on rails connecting to a Docker Postgres instance via local networking.

machina.ex.canon
  • 15
  • 5
  • You might want to confirm this by looking at the [listen_addresses](https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS) settings in the respective `postgresql.conf` files. – Adrian Klaver Apr 05 '23 at 21:43