Postgres FK declaration references customer name rather than type?

Question

I don't use Postgres much (I used to use other DB's) so recently I was viewing a db in pgAdmin and saw an odd schema syntax which I can't find anything on the documentation and want to know a bit about it:

So I see this syntax:

CREATE TABLE table1
(
  table1_id serial NOT NULL,
  table2_fk_id t2_id NOT NULL, -- what is this line?
  ...
  CONSTRAINT fk_table2_chm FOREIGN KEY (table2_fk_id)
      REFERENCES table2 (t2_id_col) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
);

What is that marked line above? It seems like the foreign key col rather than specifying int/int8 it is specifying a custom name t2_id?

Whereas in table2 pk column which is being referenced above is of type serial and is labeled t2_id_col.

Is that some exotic syntax? Or something related to pgAdmin?

Did you add `**` or is that in the original code? Because that would just be a syntax error ... — Erwin Brandstetter, Jul 14 '14 at 17:24
no sorry that was just to highlight the line, I have removed it to avoid confusion — iQ., Jul 14 '14 at 17:24
If it a user defined type, I can not find a definition for it and wouldn't make sense as foreign key it references is a serial in table2 — iQ., Jul 14 '14 at 17:25
The foreign key constraint would still work if `t2_id` is a domain with `int` as base type. And please fix the misleading example in the question. — Erwin Brandstetter, Jul 14 '14 at 17:28
Yes spot on, your right, found the domain definition for it using an integer — iQ., Jul 14 '14 at 17:31
It's not exotic, just unfamiliar to you. `CREATE DOMAIN` has been part of SQL standards since at least SQL-92, and every major SQL dbms except MySQL supports it. — Mike Sherrill 'Cat Recall', Jul 14 '14 at 17:48
IIUC microsoft still refuses to support domains, driving customers into UDT-hell: http://stackoverflow.com/q/1577136/905902 Oracle seems to have them. — wildplasser, Jul 14 '14 at 19:44

score 2 · Accepted Answer · edited May 23 '17 at 12:05

To tell for sure, run:

SELECT *
FROM   pg_type
WHERE  typname = 't2_id'

If typtype is d it's a domain, created with CREATE DOMAIN.
Or in pgAdmin just check the list of domains in the "Object browser".

And yes, this is somewhat related to pgAdmin, since it does you the courtesy of displaying a serial column as such. serial is not an actual type, the underlying type is integer. So your domain type must be based on integer as well to allow the foreign key, which requires matching base types. Details:
Safely and cleanly rename tables that use serial primary key columns in Postgres?

Postgres FK declaration references customer name rather than type?

1 Answers1