I am attempting to copy some tables in my Postgres database using like_options
(https://www.postgresql.org/docs/9.5/static/sql-createtable.html), which states that using INCLUDING ALL
is shorthand for copying CONSTRAINTS as well (info for queries found here and looking up FKs here
INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.
However, I am not seeing the expected FK-constraint from the original table after I run the copy - assuming that because the new table is empty I cannot add a FK-constraint referencing a column in another table?
create table lobby (LIKE command_center INCLUDING ALL);
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND (tc.table_name='command_center' OR tc.table_name='lobby');