I was trying to create a join table using the following statement:

CREATE TABLE directors_and_films(
id serial PRIMARY KEY,
directors_id int REFERENCES directors.id
films_id int REFERENCES films.id,
);

This causes Postgres to respond with:

ERROR: schema "films" does not exist

When I change it to:

CREATE TABLE directors_films (
  id serial PRIMARY KEY,
  director_id integer REFERENCES directors (id),
  film_id integer REFERENCES films (id)
);

The code executes fine.

My question is what is the difference between accessing a column using () as opposed to a period? What are the differences between these two in SQL generally?

CodePudding user response：

Postgres does indeed support functional notation and attribute notation for column references. So this works for a table tbl with a column col:

SELECT col(tbl) FROM tbl;

The manual:

but this usage is deprecated since it's easy to get confused

See:

• Store common query as column?

But that has no bearing on the case at hand. The short syntax for FK constraints in a CREATE TABLE statement requires parentheses around the referenced column. (The column constraint like in your example, can only reference a single column, obviously.) Attribute notation like you tried (directors.id) is a syntax error in this spot.
That's all there is to this. The manual:

REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]