Thread: how to insert row with specific oid

Hi, all

Could I insert a row to table with specific row oid. just like insert into tbl values (xx,xx) oid=xx? Is there some
syntaxto do this? 

Best Regards,
Zongtian

On Fri, 2020-06-19 at 15:02 +0800, ZongtianHou wrote:
> Could I insert a row to table with specific row oid. just like insert
> into tbl values (xx,xx) oid=xx? Is there some syntax to do this?

No, you cannot determine the OID that will be inserted.

Using tables WITH OIDS is deprecated, and the feature is gone in v12.

If you want to insert specific values, you shouldn't be using a
system-generated OID but a regular column of type "bigint" or so.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Thank you for the detailed answer!

> On Jun 19, 2020, at 3:13 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 
> On Fri, 2020-06-19 at 15:02 +0800, ZongtianHou wrote:
>> Could I insert a row to table with specific row oid. just like insert
>> into tbl values (xx,xx) oid=xx? Is there some syntax to do this?
> 
> No, you cannot determine the OID that will be inserted.
> 
> Using tables WITH OIDS is deprecated, and the feature is gone in v12.
> 
> If you want to insert specific values, you shouldn't be using a
> system-generated OID but a regular column of type "bigint" or so.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com
> 

ZongtianHou <zongtianhou@icloud.com> writes:
> Could I insert a row to table with specific row oid. just like insert into tbl values (xx,xx) oid=xx? Is there some
syntaxto do this? 

Nope, at least not in PG versions that have special oid columns at all.
(Since v12, if you want an OID column, it's just a regular column.)

            regards, tom lane

Hi, all
I am doing some development based on postgres(8.x.x) and encountered a problem when doing upgrade. Normally, there are
twoways for me to do upgrade. 
    1. when new version without catalog change, I can replace the binary and restart the cluster.
    2. when new version with catalog change like add a column or a row to a system table, I can replace the binary and
turnallow_system_table_mods to all, do all the changes, then restart. 

But, when new version add a new built-in datatype, like json, which will insert a row in pg_type with specific oid, I
cannot find a simple way to do this. I look up the initdb code, it run the postgres program in bootstrap mode, feed it
datafrom postgres.bki file which support oid specification. Simplify the question, How can I do upgrade conveniently if
Ijust need to insert one row in one system table with specific oid? Can I do it in upgrade mode somehow or I need to
runsome upgrade program? 

Best Regards,
Zongtian Hou

On Thu, 2020-07-09 at 17:55 +0800, ZongtianHou wrote:
> I am doing some development based on postgres(8.x.x) and encountered a problem when doing upgrade. Normally, there
aretwo ways for me to do upgrade.
 
>         1. when new version without catalog change, I can replace the binary and restart the cluster.
>         2. when new version with catalog change like add a column or a row to a system table, I can replace
>            the binary and turn allow_system_table_mods to all, do all the changes, then restart.
> 
> But, when new version add a new built-in datatype, like json, which will insert a row in pg_type with specific oid,
> I can not find a simple way to do this. I look up the initdb code, it run the postgres program in bootstrap mode,
> feed it data from postgres.bki file which support oid specification. Simplify the question, How can I do upgrade
> conveniently if I just need to insert one row in one system table with specific oid? Can I do it in upgrade mode
> somehow or I need to run some upgrade program?

From 8.4 on, you can use pg_upgrade to upgrade.

Otherwise, you need pg_dumpall and restore.

Just installing the new software only works for upgrades between 8.4.x and 8.4.y
or 12.x and 12.y.

Yours,
Laurenz Albe

Thank you for this info

> On Jul 9, 2020, at 10:35 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Thu, 2020-07-09 at 17:55 +0800, ZongtianHou wrote:
>> I am doing some development based on postgres(8.x.x) and encountered a problem when doing upgrade. Normally, there
aretwo ways for me to do upgrade. 
>>      1. when new version without catalog change, I can replace the binary and restart the cluster.
>>      2. when new version with catalog change like add a column or a row to a system table, I can replace
>>         the binary and turn allow_system_table_mods to all, do all the changes, then restart.
>>
>> But, when new version add a new built-in datatype, like json, which will insert a row in pg_type with specific oid,
>> I can not find a simple way to do this. I look up the initdb code, it run the postgres program in bootstrap mode,
>> feed it data from postgres.bki file which support oid specification. Simplify the question, How can I do upgrade
>> conveniently if I just need to insert one row in one system table with specific oid? Can I do it in upgrade mode
>> somehow or I need to run some upgrade program?
>
> From 8.4 on, you can use pg_upgrade to upgrade.
>
> Otherwise, you need pg_dumpall and restore.
>
> Just installing the new software only works for upgrades between 8.4.x and 8.4.y
> or 12.x and 12.y.
>
> Yours,
> Laurenz Albe
>