Total number of matches played by player
First this is a horrible idea,

team_a int REFERENCES players (pid),


This will make everyone hate you. If there is no difference between a team and a player -- use the same name. Pick or have a difference.


  I want to make a query so i can also be able to count the total matches played by player id.


You need only query your matches table.

SELECT team, count(*)
FROM (
  SELECT team_a AS team
  FROM matches
  UNION ALL
    SELECT team_b AS team
    FROM matches
) AS t
GROUP BY team;


That is to say, these relationships are bidirectional but the schema isn't. If team foo plays team bar you can write that two separate ways. That is to say both of these are valid,

TeamA | TeamB | Wuteva
foo   | bar   | baz
bar   | foo   | quz


Update

In the future, try to put all of the things you want to do in the first question, but I've updated this to include the matches won

SELECT pid, count(*) AS played, count(is_won) FILTER (WHERE is_won)
FROM (
  SELECT pid_a AS pid, pid_a = winner AS is_won
  FROM matches
  UNION ALL
    SELECT pid_b AS pid, pid_b = winner AS is_won
    FROM matches
) AS t
GROUP BY pid;


As a separate note, good job on changing the names.