In YSQL you can display the structure of a table, including indexes and constraints, by issuing the \d table_name meta-command.
yugabyte=# CREATE SCHEMA s1;
CREATE SCHEMA
yugabyte=# CREATE SCHEMA s2;
CREATE SCHEMA
yugabyte=# CREATE TABLE s1.fact (c1 INT PRIMARY KEY, c2 VARCHAR NOT NULL, c3 JSONB, c4 INT);
CREATE TABLE
yugabyte=# CREATE INDEX fact_c2 ON s1.fact(c2);
CREATE INDEX
yugabyte=# CREATE TABLE s2.dim (c1 INT PRIMARY KEY, c2 VARCHAR NOT NULL);
CREATE TABLE
yugabyte=# ALTER TABLE s1.fact ADD CONSTRAINT fact_c4_fk FOREIGN KEY (c4) REFERENCES s2.dim(c1);
ALTER TABLE
yugabyte=# \d s1.fact;
Table "s1.fact"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | character varying | | not null |
c3 | jsonb | | |
c4 | integer | | |
Indexes:
"fact_pkey" PRIMARY KEY, lsm (c1 HASH)
"fact_c2" lsm (c2 HASH)
Foreign-key constraints:
"fact_c4_fk" FOREIGN KEY (c4) REFERENCES s2.dim(c1)
yugabyte=# \d s2.dim;
Table "s2.dim"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | character varying | | not null |
Indexes:
"dim_pkey" PRIMARY KEY, lsm (c1 HASH)
Referenced by:
TABLE "s1.fact" CONSTRAINT "fact_c4_fk" FOREIGN KEY (c4) REFERENCES s2.dim(c1)
There isn’t a built-in function that extracts all that information to form a CREATE TABLE statement for us, so let’s create our own!
Since YSQL uses the Postgres API, we can query its system tables to build our DDL statement for a given table.
Below is the SQL to create a function called EXPORT_TABLE, and below that are several example executions.
CREATE OR REPLACE FUNCTION public.export_table (p_schemaname TEXT, p_tablename TEXT)
RETURNS TEXT
AS $$
SELECT string_agg(ddl, E'\n')
FROM (
(
SELECT CASE WHEN a.attnum = 1 THEN 'CREATE TABLE ' || n.nspname || '.' || c.relname || ' (' || E'\n' ELSE '' END ||
' ' || a.attname || ' ' ||
pg_catalog.format_type(a.atttypid, a.atttypmod) ||
CASE WHEN a.attnotnull = 't' THEN ' NOT NULL' ELSE '' END ||
COALESCE((SELECT c.collname
FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation), '') ||
CASE WHEN a.attidentity = 'a' THEN ' GENERATED ALWAYS AS IDENTITY'
WHEN a.attidentity = 'd' THEN ' GENERATED BY DEFAULT AS IDENTITY'
ELSE ''
END ||
CASE WHEN LEAD(a.attnum) OVER (PARTITION BY n.nspname, c.relname ORDER BY a.attnum) IS NULL THEN ')' ELSE ',' END ddl
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c
ON a.attrelid = c.oid
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relname = p_tablename
AND n.nspname = p_schemaname
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
)
UNION ALL
(
SELECT ' TABLEGROUP ' || grpname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
CROSS JOIN pg_catalog.yb_table_properties(c.relfilenode) p
LEFT JOIN pg_catalog.pg_yb_tablegroup gr
ON gr.oid = p.tablegroup_oid
WHERE c.relname = p_tablename
AND n.nspname = p_schemaname
)
UNION ALL
(
SELECT ' TABLESPACE ' || t.spcname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tablespace t
ON t.spcowner = c.relowner
WHERE c.reltablespace <> 0
AND c.relname = p_tablename
AND n.nspname = p_schemaname
)
UNION ALL
(
SELECT ';'
)
UNION ALL
(
SELECT CASE WHEN indisprimary = 't' THEN 'ALTER TABLE ' || n.nspname || '.' || c.relname || ' ADD CONSTRAINT ' || c2.relname ||
' PRIMARY KEY ' || SUBSTR(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), POSITION('lsm' IN pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+4)
ELSE pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) || COALESCE(' TABLESPACE ' || pi.tablespace, '')
END || ';' ddl
FROM pg_catalog.pg_index i
JOIN pg_catalog.pg_class c
ON c.oid = i.indrelid
JOIN pg_catalog.pg_class c2
ON c2.oid = i.indexrelid
JOIN pg_catalog.pg_class class
ON c.oid = class.oid
JOIN pg_catalog.pg_namespace n
ON n.oid = class.relnamespace
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
LEFT JOIN pg_catalog.pg_indexes pi
ON pi.schemaname = n.nspname
AND pi.tablename = class.relname
AND pi.indexname = c2.relname
WHERE class.relname = p_tablename
AND n.nspname = p_schemaname
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
)
UNION ALL
(
SELECT 'ALTER TABLE ' || conrelid::regclass || ' ADD CONSTRAINT ' ||
conname || ' ' || pg_get_constraintdef(oid) || ';' ddl
FROM pg_constraint
WHERE contype IN ('f')
AND conrelid::regclass = (p_schemaname || '.' || p_tablename)::regclass
ORDER BY conrelid::regclass::text, contype DESC
)
) AS foo;
$$ LANGUAGE SQL;
yugabyte=# \a
Output format is unaligned.
yugabyte=# \t
Tuples only is on.
yugabyte=# SELECT export_table('s1', 'fact');
CREATE TABLE s1.fact (
c1 integer NOT NULL,
c2 character varying NOT NULL,
c3 jsonb,
c4 integer)
;
ALTER TABLE s1.fact ADD CONSTRAINT fact_pkey PRIMARY KEY (c1 HASH);
CREATE INDEX fact_c2 ON s1.fact USING lsm (c2 HASH);
ALTER TABLE s1.fact ADD CONSTRAINT fact_c4_fk FOREIGN KEY (c4) REFERENCES s2.dim(c1);
yugabyte=# SELECT export_table('s2', 'dim');
CREATE TABLE s2.dim (
c1 integer NOT NULL,
c2 character varying NOT NULL)
;
ALTER TABLE s2.dim ADD CONSTRAINT dim_pkey PRIMARY KEY (c1 HASH);
Have Fun!