Select concat(kcu.table_name, kcu.column_name) Left join information_nstraint_column_usage ccu on pgc.conname = ccu.constraint_nameĪnd concat(table_name, column_name) not in( Join information_schema.key_column_usage kcu on kcu.constraint_name = pgc.conname You will never see tables in other databases, these tables aren't visible.
: list all tables in the current database regardless your searchpath. \dt: list all tables in the current database using your searchpath. Let’s execute the query given below to show all the tables, including system relations: SELECT FROM pgcatalog.
\cJoin pg_class cls on pgc.conrelid = cls.oid Please note the following commands: \list or \l: list all databases. Join pg_namespace nsp on nsp.oid = pgc.connamespace It will retrieve all columns and their relationship as well: select *FROM (Ĭase when (pgc.contype = 'f') then kcu.column_name else ccu.column_name end as column_name,Ĭase when (pgc.contype = 'f') then ccu.table_name else (null) end as reference_table,Ĭase when (pgc.contype = 'f') then ccu.column_name else (null) end as reference_col,Ĭase when (pgc.contype = 'p') then 'yes' else 'no' end as auto_inc,Ĭase when (pgc.contype = 'p') then 'no' else 'yes' end as is_nullable, The caveat here is that you do not get the names of all the constraints this way. TABLE "profile" CONSTRAINT "profile_id_stream_fkey" FOREIGN KEY (id_stream) REFERENCES stream(id) "stream_name_key" UNIQUE CONSTRAINT, btree (name) Min_id | integer | | not null | | plain | |Ībout | character varying | | not null | | extended | | Name | character varying | | not null | | extended | | This should be a table in the import schema An example: # \d+ observations.streamĬolumn | Type | Collation | Nullable | Default | Storage | Stats target | Description d+ also informs on the NOT NULL constraints, something that is not present in the pg_catalog.pg_constraint table. In the psql command line this information is in the table sheet, obtained with the \d+ command.
0 kommentar(er)
