How to use a foreign key?
However, I'm not sure what would be the best practice to use identifier as a foreign key. Should I use as reference the value of the foreign key like this


A couple of points before hand.


There is no need to mark a PRIMARY KEY NOT NULL, and DO NOT create a UNIQUE index on it. That'll just slow things down. It's already implemented with a ``UNIQUE` index.
Reconsider bigserial, do you really need it?
If you do not use id, but instead fund_id, or id_fund or the like, you can use the USING (fund_id) clause which is


easier to type
less error-prone

PostgreSQL provides for a shorter more terse syntax to refer to primary keys..

-- it'll guess primary key
fund_identifier  type  REFERENCES table;



That said, I think your questions is if your primary table uses bigserial how does your foreign table declare the column that refers to it.. Like this, using bigint

fund_identifier  bigint  REFERENCES table;


So your schema should look like

CREATE TABLE funds (
    id_fund    bigserial PRIMARY KEY,
    identifier text      UNIQUE NOT NULL
);

CREATE TABLE security_papers (
  id_security_paper    bigserial  NOT NULL
  id_fund              bigint     REFERENCES funds
  as_of_date           date,
);


Then

SELECT * FROM security_papers
JOIN funds
  USING (id_fund);