MIME-Version: 1.0
From: plsqlvids01 plsqlvids01 <plsqlvids01@gmail.com>
Date: Mon, 19 Aug 2024 02:08:48 -0400
Message-ID: <CAFsfSrs+_7Aie27FPB+HnH0vEeT3h=5ZJj7k=OWDYTGksmYPiw@mail.gmail.com>
Subject: use of postgres reg* datatypes in user tables?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000c5b6b0620032507"
Archived-At: <https://www.postgresql.org/message-id/CAFsfSrs%2B_7Aie27FPB%2BHnH0vEeT3h%3D5ZJj7k%3DOWDYTGksmYPiw%40mail.gmail.com>
Precedence: bulk

--0000000000000c5b6b0620032507
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

What is the use of postgres regclass datatype in user tables? When would
one want to use them when regular data types are available? If any tables
have these data types, since pg_upgrade would fail, what data types should
those be converted to?

```
postgres=3D> select version();
                                                 version

---------------------------------------------------------------------------=
-------------------------------
 PostgreSQL 14.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

\dT pg_catalog.reg*
                        List of data types
   Schema   =E2=94=82     Name      =E2=94=82             Description
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90
 pg_catalog =E2=94=82 regclass      =E2=94=82 registered class
 pg_catalog =E2=94=82 regcollation  =E2=94=82 registered collation
 pg_catalog =E2=94=82 regconfig     =E2=94=82 registered text search config=
uration
 pg_catalog =E2=94=82 regdictionary =E2=94=82 registered text search dictio=
nary
 pg_catalog =E2=94=82 regnamespace  =E2=94=82 registered namespace
 pg_catalog =E2=94=82 regoper       =E2=94=82 registered operator
 pg_catalog =E2=94=82 regoperator   =E2=94=82 registered operator (with arg=
s)
 pg_catalog =E2=94=82 regproc       =E2=94=82 registered procedure
 pg_catalog =E2=94=82 regprocedure  =E2=94=82 registered procedure (with ar=
gs)
 pg_catalog =E2=94=82 regrole       =E2=94=82 registered role
 pg_catalog =E2=94=82 regtype       =E2=94=82 registered type
(11 rows)

postgres=3D> create table t1 (c1 int, c2 regclass);
CREATE TABLE
postgres=3D> \d t1;
               Table "public.t1"
 Column |   Type   | Collation | Nullable | Default
--------+----------+-----------+----------+---------
 c1     | integer  |           |          |
 c2     | regclass |           |          |

postgres=3D> insert into t1 values(1,1);
INSERT 0 1
postgres=3D> insert into t1 values(2,'2');
INSERT 0 1
postgres=3D> insert into t1 values(3,'3'::int);
INSERT 0 1
postgres=3D> insert into t1 values(4,'4'::text);
ERROR:  relation "4" does not exist
postgres=3D> select * from t1;
 c1 | c2
----+----
  1 | 1
  2 | 2
  3 | 3
(3 rows)
```
What kind of data does these data types store - numbers or text? In above
example am able to insert it as number as well as character '2'?

--0000000000000c5b6b0620032507
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9Imx0ciI+V2hhdCBpcyB0aGUgdXNlIG9mIHBvc3RncmVzIHJlZ2NsYXNzIGRhdGF0
eXBlIGluIHVzZXIgdGFibGVzPyBXaGVuIHdvdWxkIG9uZSB3YW50IHRvIHVzZSB0aGVtIHdoZW4g
cmVndWxhciBkYXRhIHR5cGVzIGFyZSBhdmFpbGFibGU/IElmIGFueSB0YWJsZXMgaGF2ZSB0aGVz
ZSBkYXRhIHR5cGVzLCBzaW5jZSBwZ191cGdyYWRlIHdvdWxkIGZhaWwsIHdoYXQgZGF0YSB0eXBl
cyBzaG91bGQgdGhvc2UgYmUgY29udmVydGVkIHRvPzxicj48YnI+YGBgPGJyPnBvc3RncmVzPSZn
dDsgc2VsZWN0IHZlcnNpb24oKTs8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqB2ZXJzaW9uIMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgIMKgPGJyPi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS08YnI+wqBQb3N0Z3JlU1FMIDE0LjExIG9uIHg4Nl82NC1wYy1saW51eC1nbnUsIGNvbXBp
bGVkIGJ5IGdjYyAoR0NDKSA3LjMuMSAyMDE4MDcxMiAoUmVkIEhhdCA3LjMuMS0xMiksIDY0LWJp
dDxicj4oMSByb3cpPGJyPjxicj5cZFQgcGdfY2F0YWxvZy5yZWcqPGJyPsKgIMKgIMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIExpc3Qgb2YgZGF0YSB0eXBlczxicj7CoCDCoFNjaGVtYSDC
oCDilIIgwqAgwqAgTmFtZSDCoCDCoCDCoOKUgiDCoCDCoCDCoCDCoCDCoCDCoCBEZXNjcmlwdGlv
biDCoCDCoCDCoCDCoCDCoCDCoCDCoDxicj7ilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDi
lZDilZDilarilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilari
lZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDi
lZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZDilZA8
YnI+wqBwZ19jYXRhbG9nIOKUgiByZWdjbGFzcyDCoCDCoCDCoOKUgiByZWdpc3RlcmVkIGNsYXNz
PGJyPsKgcGdfY2F0YWxvZyDilIIgcmVnY29sbGF0aW9uIMKg4pSCIHJlZ2lzdGVyZWQgY29sbGF0
aW9uPGJyPsKgcGdfY2F0YWxvZyDilIIgcmVnY29uZmlnIMKgIMKgIOKUgiByZWdpc3RlcmVkIHRl
eHQgc2VhcmNoIGNvbmZpZ3VyYXRpb248YnI+wqBwZ19jYXRhbG9nIOKUgiByZWdkaWN0aW9uYXJ5
IOKUgiByZWdpc3RlcmVkIHRleHQgc2VhcmNoIGRpY3Rpb25hcnk8YnI+wqBwZ19jYXRhbG9nIOKU
giByZWduYW1lc3BhY2UgwqDilIIgcmVnaXN0ZXJlZCBuYW1lc3BhY2U8YnI+wqBwZ19jYXRhbG9n
IOKUgiByZWdvcGVyIMKgIMKgIMKgIOKUgiByZWdpc3RlcmVkIG9wZXJhdG9yPGJyPsKgcGdfY2F0
YWxvZyDilIIgcmVnb3BlcmF0b3IgwqAg4pSCIHJlZ2lzdGVyZWQgb3BlcmF0b3IgKHdpdGggYXJn
cyk8YnI+wqBwZ19jYXRhbG9nIOKUgiByZWdwcm9jIMKgIMKgIMKgIOKUgiByZWdpc3RlcmVkIHBy
b2NlZHVyZTxicj7CoHBnX2NhdGFsb2cg4pSCIHJlZ3Byb2NlZHVyZSDCoOKUgiByZWdpc3RlcmVk
IHByb2NlZHVyZSAod2l0aCBhcmdzKTxicj7CoHBnX2NhdGFsb2cg4pSCIHJlZ3JvbGUgwqAgwqAg
wqAg4pSCIHJlZ2lzdGVyZWQgcm9sZTxicj7CoHBnX2NhdGFsb2cg4pSCIHJlZ3R5cGUgwqAgwqAg
wqAg4pSCIHJlZ2lzdGVyZWQgdHlwZTxicj4oMTEgcm93cyk8YnI+PGJyPnBvc3RncmVzPSZndDsg
Y3JlYXRlIHRhYmxlIHQxIChjMSBpbnQsIGMyIHJlZ2NsYXNzKTs8YnI+Q1JFQVRFIFRBQkxFPGJy
PnBvc3RncmVzPSZndDsgXGQgdDE7PGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgVGFibGUgJnF1
b3Q7cHVibGljLnQxJnF1b3Q7PGJyPsKgQ29sdW1uIHwgwqAgVHlwZSDCoCB8IENvbGxhdGlvbiB8
IE51bGxhYmxlIHwgRGVmYXVsdCA8YnI+LS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLSst
LS0tLS0tLS0tKy0tLS0tLS0tLTxicj7CoGMxIMKgIMKgIHwgaW50ZWdlciDCoHwgwqAgwqAgwqAg
wqAgwqAgfCDCoCDCoCDCoCDCoCDCoHwgPGJyPsKgYzIgwqAgwqAgfCByZWdjbGFzcyB8IMKgIMKg
IMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqB8IDxicj48YnI+cG9zdGdyZXM9Jmd0OyBpbnNlcnQg
aW50byB0MSB2YWx1ZXMoMSwxKTs8YnI+SU5TRVJUIDAgMTxicj5wb3N0Z3Jlcz0mZ3Q7IGluc2Vy
dCBpbnRvIHQxIHZhbHVlcygyLCYjMzk7MiYjMzk7KTs8YnI+SU5TRVJUIDAgMTxicj5wb3N0Z3Jl
cz0mZ3Q7IGluc2VydCBpbnRvIHQxIHZhbHVlcygzLCYjMzk7MyYjMzk7OjppbnQpOzxicj5JTlNF
UlQgMCAxPGJyPnBvc3RncmVzPSZndDsgaW5zZXJ0IGludG8gdDEgdmFsdWVzKDQsJiMzOTs0JiMz
OTs6OnRleHQpOzxicj5FUlJPUjogwqByZWxhdGlvbiAmcXVvdDs0JnF1b3Q7IGRvZXMgbm90IGV4
aXN0PGJyPnBvc3RncmVzPSZndDsgc2VsZWN0ICogZnJvbSB0MTs8YnI+wqBjMSB8IGMyIDxicj4t
LS0tKy0tLS08YnI+wqAgMSB8IDE8YnI+wqAgMiB8IDI8YnI+wqAgMyB8IDM8YnI+KDMgcm93cyk8
YnI+YGBgPGJyPldoYXQga2luZCBvZiBkYXRhIGRvZXMgdGhlc2UgZGF0YSB0eXBlcyBzdG9yZSAt
IG51bWJlcnMgb3IgdGV4dD8gSW4gYWJvdmUgZXhhbXBsZSBhbSBhYmxlIHRvIGluc2VydCBpdCBh
cyBudW1iZXIgYXMgd2VsbCBhcyBjaGFyYWN0ZXIgJiMzOTsyJiMzOTs/PGJyPjwvZGl2Pg0K
--0000000000000c5b6b0620032507--