[MM3-users] Re: Could not locate column in row for column 'user.id'

28 Jul 2020 · *mailman=# select count(*


      Thank you Mark for your response.
I tried for the command to know if any entry in the "address" table for
which 'id' is not an integer.
But unfortunately, I could not get any command or idea how to find that.
But out of my extra talent, I tried the below commands which, I thought,
may print the records which contain a non integer id value.
*mailman=# select count(*) from address; count------- 82387(1 row)mailman=#
select id,email from address where id NOT BETWEEN 1 AND 84203; id |
email----+-------(0 rows)mailman=# select id,email from address where id
NOT BETWEEN 1 AND 84202;  id   |
email-------+----------------------------- 84203 |
cons3.birmingham@mea.gov.in <cons3.birmingham@mea.gov.in>*
*1 and 84203 are the minimum and maximum values of the id:*
Please see the above outputs and suggest if anything possible or please let
me know the command what should I run to find a non integer id value.
I also doubt this error as if my REST API is contacting postgresql in a
delayed manner. If possible, can you say what optimal setting I can do at
Postgresql so that faster responses would be given to REST API.
The above things are only my doubts. Please correct me if I am wrong.
On Tue, Jul 28, 2020 at 7:31 AM Mark Sapiro <mark@msapiro.net> wrote:
...
On 7/27/20 2:48 AM, Shashikanth Komandoor wrote:
...
Hi Mark,
      Thank you for your response. And sorry for not giving you
complete information as I am not sue what to share with you.
      I am using Postgresql-11.7 as my backend database. The below is
the structure of the address table in* mailman *database:
mailman=# \d address;
Table "public.address"
Column     |            Type             | Collation | Nullable |
Default
----------------+-----------------------------+-----------+----------+-------------------------------------
...
id             | integer                     |           | not null |
nextval('address_id_seq'::regclass)
email          | character varying           |           |          |
_original      | character varying           |           |          |
display_name   | character varying           |           |          |
verified_on    | timestamp without time zone |           |          |
registered_on  | timestamp without time zone |           |          |
user_id        | integer                     |           |          |
preferences_id | integer                     |           |          |
Indexes:
"address_pkey" PRIMARY KEY, btree (id)
"ix_address_email" UNIQUE, btree (email)
"ix_address_preferences_id" btree (preferences_id)
"ix_address_user_id" btree (user_id)
Foreign-key constraints:
"address_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES
preferences(id)
"address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
Referenced by:
TABLE ""user"" CONSTRAINT "_preferred_address" FOREIGN KEY
(_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL
TABLE "autoresponserecord" CONSTRAINT
"autoresponserecord_address_id_fkey" FOREIGN KEY (address_id) REFERENCES
address(id)
TABLE "member" CONSTRAINT "member_address_id_fkey" FOREIGN KEY
(address_id) REFERENCES address(id)
TABLE "onelastdigest" CONSTRAINT "onelastdigest_address_id_fkey"
FOREIGN KEY (address_id) REFERENCES address(id)
    The below is the structure of *user* table:
mailman=# \d user;
Table "public.user"
Column         |            Type             | Collation |
Nullable
|             Default
...
id                    | integer                     |           | not
null
| nextval('user_id_seq'::regclass)
display_name          | character varying           |           |
|
password              | character varying           |           |
|
_user_id              | uuid                        |           |
|
_created_on           | timestamp without time zone |           |
|
is_server_owner       | boolean                     |           |
|
_preferred_address_id | integer                     |           |
|
preferences_id        | integer                     |           |
|
Indexes:
"user_pkey" PRIMARY KEY, btree (id)
"ix_user__user_id" btree (_user_id)
"ix_user_preferences_id" btree (preferences_id)
Foreign-key constraints:
"_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES
address(id) ON DELETE SET NULL
"user_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES
preferences(id)
Referenced by:
TABLE "address" CONSTRAINT "address_user_id_fkey" FOREIGN KEY
(user_id)
REFERENCES "user"(id)
TABLE "domain_owner" CONSTRAINT "domain_owner_user_id_fkey" FOREIGN
KEY
(user_id) REFERENCES "user"(id)
TABLE "member" CONSTRAINT "member_user_id_fkey" FOREIGN KEY (user_id)
REFERENCES "user"(id)
mailman=# \dn
List of schemas
Name  |  Owner
--------+----------
public | postgres
(1 row)
mailman=# select * from user;
user
postgres
(1 row)
*The above command output is not showing any fields. But if use the
-----------------------+-----------------------------+-----------+----------+----------------------------------
table
...
as public.user (schema.tablename) with select command, it is showing the
data. But without schema name this table is not showing those fields. Not
sure if this the expected behavior or not.*
This is expected. psql gets confused because it has its own user table.
The above table definitions look fine.
Is there any entry in the address table for which id is not an integer?
--
Mark Sapiro <mark@msapiro.net>        The highway is for gamblers,
San Francisco Bay Area, California    better use your sense - B. Dylan

Mailman-users mailing list -- mailman-users@mailman3.org
To unsubscribe send an email to mailman-users-leave@mailman3.org
https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
--
Thanks & Regards,
Shashi Kanth.K
9052671936

[MM3-users] Re: Could not locate column in row for column 'user.id'

Shashikanth Komandoor

mailman=# select * from user; user