Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(  id int primary key generated by default as identity
                  , id2 serial);

insert into test(id)values(default)returning *;

CREATE TABLE




id
id2


1
1



INSERT 0 1


insert into test(id,id2)
values(  nextval(pg_get_serial_sequence('test','id'))
       , nextval(pg_get_serial_sequence('test','id2')))
returning *;


id
id2


2
2



INSERT 0 1


--some of the things psql's `\d` meta-command does:
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (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) AS attcollation,
  a.attidentity,
  a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'public.test'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = 'public.test'::regclass AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;


attname
format_type
pg_get_expr
attnotnull
attcollation
attidentity
attgenerated


id
integer
null
t
null
d


id2
integer
nextval('test_id2_seq'::regclass)
t
null





SELECT 2




relname
indisprimary
indisunique
indisclustered
indisvalid
pg_get_indexdef
pg_get_constraintdef
contype
condeferrable
condeferred
indisreplident
reltablespace


test_pkey
t
t
f
t
CREATE UNIQUE INDEX test_pkey ON test USING btree (id)
PRIMARY KEY (id)
p
f
f
f
0



SELECT 1


SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;



SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = 'public.test'::regclass;



SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (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) AS attcollation,
  a.attidentity,
  a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'public.test'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;



SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = 'public.test'::regclass AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;



SELECT pol.polname, pol.polpermissive,
  CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,
  pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
  pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
  CASE pol.polcmd
    WHEN 'r' THEN 'SELECT'
    WHEN 'a' THEN 'INSERT'
    WHEN 'w' THEN 'UPDATE'
    WHEN 'd' THEN 'DELETE'
    END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = 'public.test'::regclass ORDER BY 1;



SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS nsp, stxname,
pg_catalog.pg_get_statisticsobjdef_columns(oid) AS columns,
  'd' = any(stxkind) AS ndist_enabled,
  'f' = any(stxkind) AS deps_enabled,
  'm' = any(stxkind) AS mcv_enabled,
stxstattarget
FROM pg_catalog.pg_statistic_ext
WHERE stxrelid = 'public.test'::regclass
ORDER BY nsp, stxname;



SELECT pubname
     , NULL
     , NULL
FROM pg_catalog.pg_publication p
     JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid
     JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid
WHERE pc.oid ='public.test'::regclass and pg_catalog.pg_relation_is_publishable('public.test'::regclass)
UNION
SELECT pubname
     , pg_get_expr(pr.prqual, c.oid)
     , (CASE WHEN pr.prattrs IS NOT NULL THEN
         (SELECT string_agg(attname, ', ')
           FROM pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,
                pg_catalog.pg_attribute
          WHERE attrelid = pr.prrelid AND attnum = prattrs[s])
        ELSE NULL END) FROM pg_catalog.pg_publication p
     JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
     JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid
WHERE pr.prrelid = 'public.test'::regclass
UNION
SELECT pubname
     , NULL
     , NULL
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('public.test'::regclass)
ORDER BY 1;



SELECT c.oid::pg_catalog.regclass
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhparent AND i.inhrelid = 'public.test'::regclass
  AND c.relkind != 'p' AND c.relkind != 'I'
ORDER BY inhseqno;



SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = 'public.test'::regclass
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;



oid
nspname
relname


8608585
public
test



SELECT 1




relchecks
relkind
relhasindex
relhasrules
relhastriggers
relrowsecurity
relforcerowsecurity
relhasoids
relispartition
?column?
reltablespace
reloftype
relpersistence
relreplident
amname


0
r
t
f
f
f
f
f
f

0

p
d
heap



SELECT 1




attname
format_type
pg_get_expr
attnotnull
attcollation
attidentity
attgenerated


id
integer
null
t
null
d


id2
integer
nextval('test_id2_seq'::regclass)
t
null





SELECT 2




relname
indisprimary
indisunique
indisclustered
indisvalid
pg_get_indexdef
pg_get_constraintdef
contype
condeferrable
condeferred
indisreplident
reltablespace


test_pkey
t
t
f
t
CREATE UNIQUE INDEX test_pkey ON test USING btree (id)
PRIMARY KEY (id)
p
f
f
f
0



SELECT 1




polname
polpermissive
array_to_string
pg_get_expr
pg_get_expr
cmd



SELECT 0




oid
stxrelid
nsp
stxname
columns
ndist_enabled
deps_enabled
mcv_enabled
stxstattarget



SELECT 0




pubname
?column?
?column?



SELECT 0




oid



SELECT 0




oid
relkind
inhdetachpending
pg_get_expr



SELECT 0
attname	format_type	pg_get_expr	attnotnull	attcollation	attidentity	attgenerated
id	integer	null	t	null	d
id2	integer	nextval('test_id2_seq'::regclass)	t	null