Database Administrators Asked on December 26, 2021
I’ve created a few new UDTs in PostgreSQL. However, now I have two problems:
Unfortunately, I couldn’t find anything on that in the PostgreSQL documentation.
Using the anwser of gsiems, I've archived the goal to mimic the 'CREATE TYPE' in PgAdmin & PgBackup
WITH types AS (
SELECT n.nspname,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
CASE
WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
ELSE CAST ( t.typlen AS pg_catalog.text )
END AS obj_type,
coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
),
cols AS (
SELECT n.nspname::text AS schema_name,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
a.attname::text AS column_name,
pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
a.attnotnull AS is_required,
a.attnum AS ordinal_position,
pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_type t
ON a.attrelid = t.typrelid
JOIN pg_catalog.pg_namespace n
ON ( n.oid = t.typnamespace )
JOIN types
ON ( types.nspname = n.nspname
AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
WHERE a.attnum > 0
AND NOT a.attisdropped
)
SELECT 'CREATE TYPE ' || cols.schema_name || '.' || cols.obj_name || E' AS (n ' ||
pg_catalog.array_to_string (ARRAY(
SELECT cols.column_name || ' ' || cols.data_type AS col_num_typ
FROM cols
WHERE cols.obj_name='my_user_datatype'
ORDER BY cols.schema_name,
cols.obj_name,
cols.ordinal_position ), E',n '
) || E'n);'
AS cre_typ
FROM cols
WHERE cols.obj_name='my_user_datatype'
LIMIT 1
and run it under psql with this commands to have only the SQL code tagat
.
Ok in PG 9.6. Say me if it's running with yours instances. Obviously, it need to be optimised and tested.
Answered by P. Qualis on December 26, 2021
With psql
:
dT
show list of user-defined types.dT+ <type_name>
show given user-defined type, with details.dT <type_name>
show given user-defined type, without details.Answered by user218867 on December 26, 2021
This is a very simple alternative, but enough for simple use cases. When the column's type is UDT, we use the udt_name
column from information_schema.columns
table.
select column_name, case
when (data_type = 'USER-DEFINED') then udt_name
else data_type
end as data_type
from information_schema.columns
where table_schema = 'altimetria' and table_name = 'cnivel';
Result (geometry
is a UDT):
column_name | data_type
--------------+------------------
ogc_fid | integer
wkb_geometry | geometry
id_cnivel | bigint
cod_cart | bigint
cod_fam | bigint
cod_sub | integer
z | double precision
shape_len | double precision
Answered by jgrocha on December 26, 2021
Try execute this code:
SELECT
pg_type.typname,
pg_enum.enumlabel
FROM
pg_type
JOIN
pg_enum ON pg_enum.enumtypid = pg_type.oid;
Answered by Santos L. Victor on December 26, 2021
The default clients have functionality to cover that:
Old default GUI (up to Postgres 10).
Make sure types are enabled in the object browser: Options - Browser - Display.
New default GUI.
Make sure types are enabled in the object browser: File - Preferences - Browser - Nodes.
To the left you see user-defined types in the chosen schema. (Question 1)
The SQL pane
to the right has the reverse engineered SQL script for the selected type. (Question 2)
More details in the other panes, like Dependents
etc.
The standard interactive console.
dT
to get a list of user-defined types.d type_name
to get the column definition list for the given type.
d[S+] [ pattern ]
For each relation (table, view, materialized view, index, sequence, or foreign table) or composite type matching the pattern, show all columns, their types, [...]
Bold emphasis mine. The command works for composite types as well since at least Postgres 9.1.
And:
dT[S+] [ pattern ]
Lists data types. If pattern is specified, only types whose names match the pattern are listed. If
+
is appended to the command name, each type is listed with its internal name and size, its allowed values if it is anenum
type, and its associated permissions. By default, only user-created objects are shown; supply a pattern or theS
modifier to include system objects.
Answered by Erwin Brandstetter on December 26, 2021
Does this get you started?
SELECT n.nspname AS schema,
pg_catalog.format_type ( t.oid, NULL ) AS name,
t.typname AS internal_name,
CASE
WHEN t.typrelid != 0
THEN CAST ( 'tuple' AS pg_catalog.text )
WHEN t.typlen < 0
THEN CAST ( 'var' AS pg_catalog.text )
ELSE CAST ( t.typlen AS pg_catalog.text )
END AS size,
pg_catalog.array_to_string (
ARRAY( SELECT e.enumlabel
FROM pg_catalog.pg_enum e
WHERE e.enumtypid = t.oid
ORDER BY e.oid ), E'n'
) AS elements,
pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
FROM pg_catalog.pg_type t
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid
)
)
AND NOT EXISTS
( SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid
)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_type_is_visible ( t.oid )
ORDER BY 1, 2;
In psql you can set ECHO_HIDDEN on
to make psql show you the queries
used to generate the output of the d...
commands. I've found these queries
to be very useful as a starting point when digging metadata out of databases.
Update: 2019-12-16
For composite types, the columns metadata can be determined using something like the following:
WITH types AS (
SELECT n.nspname,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
CASE
WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
ELSE CAST ( t.typlen AS pg_catalog.text )
END AS obj_type,
coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
),
cols AS (
SELECT n.nspname::text AS schema_name,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
a.attname::text AS column_name,
pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
a.attnotnull AS is_required,
a.attnum AS ordinal_position,
pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_type t
ON a.attrelid = t.typrelid
JOIN pg_catalog.pg_namespace n
ON ( n.oid = t.typnamespace )
JOIN types
ON ( types.nspname = n.nspname
AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
WHERE a.attnum > 0
AND NOT a.attisdropped
)
SELECT cols.schema_name,
cols.obj_name,
cols.column_name,
cols.data_type,
cols.ordinal_position,
cols.is_required,
coalesce ( cols.description, '' ) AS description
FROM cols
ORDER BY cols.schema_name,
cols.obj_name,
cols.ordinal_position ;
Answered by gsiems on December 26, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP