There are various ways, depending on the use case. For your example:
INSERT INTO public.people(name, states_id)
VALUES ('John', (SELECT states_id from public.states WHERE name = 'CA'));
Or:
INSERT INTO public.people(name, states_id)
SELECT 'John', states_id
FROM public.states
WHERE name = 'CA';
Assuming public.states.name to be unique - there should be a constraint in the table definition.
And a column public.people.states_id, obviously.
You can use a query like below to get all tables with dependencies;
WITH fkeys AS (
SELECT
c.conrelid AS table_id,
c_fromtablens.nspname AS schemaname,
c_fromtable.relname AS tablename,
c.confrelid AS parent_id,
c_totablens.nspname AS parent_schemaname,
c_totable.relname AS parent_tablename
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
JOIN pg_class c_fromtable ON c_fromtable.oid = c.conrelid
JOIN pg_namespace c_fromtablens ON c_fromtablens.oid = c_fromtable.relnamespace
JOIN pg_class c_totable ON c_totable.oid = c.confrelid
JOIN pg_namespace c_totablens ON c_totablens.oid = c_totable.relnamespace
WHERE
c.contype = 'f'
)
SELECT
t.schemaname,
t.tablename,
fkeys.parent_schemaname,
fkeys.parent_tablename
FROM pg_tables t
LEFT JOIN fkeys ON t.schemaname = fkeys.schemaname AND
t.tablename = fkeys.tablename
WHERE
t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
3 NULLS FIRST,
4 NULLS FIRST
Moreover, the query below provides you foreign key details. It provides relation types by using primary key definition. Note that it does not check unique constrains.
WITH fkey AS (
SELECT
n.nspname AS fkey_schema_name,
c.conname AS fkey_name,
c_fromtablens.nspname AS table_schema_name,
c_fromtable.relname AS table_name,
c_totablens.nspname AS foreign_table_schema_name,
c_totable.relname AS foreign_table_name,
c.conrelid,
unnest(c.conkey) AS fkey_field_num,
c.confrelid,
unnest(c.confkey) AS fkey_foreign_field_num
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
JOIN pg_class c_fromtable ON c_fromtable.oid = c.conrelid
JOIN pg_namespace c_fromtablens ON c_fromtablens.oid = c_fromtable.relnamespace
JOIN pg_class c_totable ON c_totable.oid = c.confrelid
JOIN pg_namespace c_totablens ON c_totablens.oid = c_totable.relnamespace
WHERE
c.contype = 'f'
),
pkey AS (
SELECT
c.conrelid,
unnest(c.conkey) AS conkey
FROM pg_constraint c
WHERE
c.contype = 'p'
)
SELECT
fkey.fkey_schema_name,
fkey.fkey_name,
fkey.table_schema_name,
fkey.table_name,
fkey.foreign_table_schema_name,
fkey.foreign_table_name,
a.attname AS field_name,
a_f.attname AS foreign_field_name,
CASE
WHEN pkey.conrelid IS NULL AND pkeyf.conrelid IS NULL THEN 'N-N'
WHEN pkey.conrelid IS NOT NULL AND pkeyf.conrelid IS NOT NULL THEN '1-1'
WHEN pkey.conrelid IS NULL AND pkeyf.conrelid IS NOT NULL THEN 'N-1'
END AS relation_type
FROM fkey
JOIN pg_attribute a ON a.attrelid = fkey.conrelid AND a.attnum = fkey.fkey_field_num
JOIN pg_attribute a_f ON a_f.attrelid = fkey.confrelid AND a_f.attnum = fkey.fkey_foreign_field_num
LEFT JOIN pkey ON pkey.conrelid = a.attrelid AND pkey.conkey = a.attnum
LEFT JOIN pkey pkeyf ON pkeyf.conrelid = a_f.attrelid AND pkeyf.conkey = a_f.attnum
Best Answer
To do what you need to do, you should be able to create a view on your secure server and reference that as the table in your less secure server, like this:
On the secure server:
On the less secure server:
It's not your ideal scenario, but from what you're describing it should work. Hope that helps. =)