What is the difference between referencing a column using "()" and "."?

Question

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?

If I was creating a composite table (not sure what the technical term is) and I said ```FROM directors_and_films JOIN directors ON directors_and_films.director_id = directors.id``` why would this work but the example above does not? — , Oct 28 '21 at 01:21
in this scenario you have already mentioned `table1 JOIN table2` then in the `ON` clause its the alias of those tables. Completely different usages. This is usually done like so: `FROM directors_and_films dfs JOIN directors ds ON dfs.director_id = ds.id` — Kaus2b, Oct 28 '21 at 01:30

Erwin Brandstetter · Accepted Answer · 2021-10-28T01:48:13.780

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 ]

What is the difference between referencing a column using "()" and "."?

1 Answers1