Create a database model that shows a user's friends

You should do another JOIN with the user table claiming the friendship relationship.

Example:

SELECT A1.idUser, A1.nombre, A2.idUser id_amigo, 
       A2.nombre nombre_amigo, B.idUser_FK  FROM usuario A1 
       INNER JOIN friendlist B ON A1.idUser = B.idUser
       INNER JOIN usuario A2 ON B.idUser_FK = A2.idUser;

Or, to make it clearer:

SELECT 

    A1.idUser, A1.nombre, 
    A2.idUser id_amigo, A2.nombre nombre_amigo, 

    B.idUser_FK  

    FROM friendlist B

    INNER JOIN usuario A1  ON A1.idUser = B.idUser
    INNER JOIN usuario A2 ON B.idUser_FK = A2.idUser;

The result will always be the same

Result

    iduser  nombre  id_amigo    nombre_amigo    iduser_fk
1   1       Moises  2           Marbella         2
2   1       Moises  3           Noel             3

Or, in a single row the friends of the user x , using aggregation functions (Postgresql), in MySQL would be easier with GROUP_CONCAT:

SELECT 

    A1.idUser, A1.nombre, 
    array_to_string(array_agg (A2.idUser), ', ') id_amigos, 
    array_to_string(array_agg (A2.nombre), ', ') nombre_amigos

    FROM friendlist B

    INNER JOIN usuario A1  ON A1.idUser = B.idUser
    INNER JOIN usuario A2 ON B.idUser_FK = A2.idUser

    GROUP BY A1.idUser;

Result

    iduser    nombre    id_amigos      nombre_amigos
1   1         Moises    2, 3           Marbella, Noel

First your table friendlist is not consistent since you can insert the same friendship several times. To solve it, you must add a primary key. You should also try to define the foreign key during the creation of the table. Then I leave you said for the two tables:

In the user table you can remove the NOT NULL from idUser since a PRIMARY KEY implies that it can not be NULL .

CREATE TABLE usuario
(
     idUser serial PRIMARY KEY,
     nombre TEXT,
     apellidoP TEXT,
     apellidoM TEXT,
     usuario TEXT,
     clave TEXT
);

The friends table must reference both fields to the idUser field in the user table. And we form a PRIMARY KEY compound (when it is from a single field it can go in the same line that you define the field as I did in the previous table).

CREATE TABLE friendlist (               
    idUser INTEGER REFERENCES usuario(idUser),             
    idUser_FK INTEGER REFERENCES usuario(idUser),
    PRIMARY KEY(idUser, idUser_FK)
);    

If you execute the INSERT INTO that you had for friendlist you will see that they continue working, but if you try to insert the repeated data it will give an error. That allows each friendship relationship to be unique and that, for example, you have not repeated the friendship relationship between Moses and Marbella (with your previous code yes you could).

Regarding your query:

What you are doing is getting the friends of Moisés but in the friendlist table, and the only thing that appears in that table is its idUser_FK , which after all is a representative number but does not give us much information.

The solution is to perform another JOIN of the result with the table usuario . That way you can get the name of the friend knowing his iduser_FK obtained from the table friendlist .

Before seeing the consultations we summarize all this.

Step by step:

1. We look for the id of the friends of Moisés in the friendship table (friendlist):

SELECT lista.iduser_fk FROM 
usuario usuario INNER JOIN friendlist lista ON    
usuario.iduser=lista.iduser WHERE usuario.nombre='Moises';

Getting the result:

iduser_fk 
-----------
     2
     3

2.Now we should look for those id in the user table, modifying the query with a INNER JOIN with the table usuario :

SELECT amigo.nombre FROM 
usuario AS usuario INNER JOIN friendlist AS lista ON usuario.iduser=lista.iduser 
INNER JOIN usuario AS amigo ON amigo.iduser=lista.iduser_fk
WHERE usuario.nombre='Moises';

The result is:

  nombre  
----------
 Marbella
 Noel

You must also put the clause WHERE of the result that I have shown you because otherwise it will not filter by the name Moisés, in this even if you do not put WHERE the same result will come out because you only have friends for Moisés. but if you try to add another friendship relationship between two people other than Moses, he will also show it to you.

To check what I'm saying, insert this new friendship between Marbella and Noel:

INSERT INTO friendlist VALUES(2,3);

If you make the query without the WHERE clause in the end, Noel shows you twice, because Noel is a friend of Marbella and Moisés:

SELECT amigo.nombre FROM 
usuario AS usuario INNER JOIN friendlist AS lista ON     
usuario.iduser=lista.iduser 
INNER JOIN usuario AS amigo ON amigo.iduser=lista.iduser_fk;

  nombre  
----------
 Marbella
 Noel
 Noel

I hope it serves you, if you have any questions do not hesitate to send a message:)