srusanna.blogg.se

Left join information_nstraint_column_usage ccu on pgc.conname = ccu. 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 Join pg_class cls on pgc.conrelid = cls.oid

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) To get the tables present in the database, we can use the psql meta-command named dt that lists out all the tables of the connected database. Min_id | integer | | not null | | plain | |Ībout | character varying | | not null | | extended | | In this command, you use the postgres user to. The -U flag stands for the user and -W option requires you to provide the password. First, connect to PostgreSQL using the psql tool. Showing tables from PostgreSQL using psql.

Name | character varying | | not null | | extended | | This should be a table in the import schema PostgreSQL does not support the SHOW TABLES statement directly but provides you with an alternative. 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.