Postgres-如何用其序列删除行？
我在Postgres中创建了一个名为“产品”的名为“产品”的表：
CREATE TABLE PRODUCTS (
PRODUCT_ID serial PRIMARY KEY,
NAME VARCHAR ( 100 ) NOT NULL );
如果我删除了下一个产品的行，
DELETE FROM PRODUCTS WHERE PRODUCT_ID = 2
即使已经删除了product_id：2，也将具有ID：3，我想用它删除数据序列因此使下一个插入具有ID：2。
I created a table called Product with a serial ID in Postgres:
CREATE TABLE PRODUCTS (
PRODUCT_ID serial PRIMARY KEY,
NAME VARCHAR ( 100 ) NOT NULL );
If I deleted a row with
DELETE FROM PRODUCTS WHERE PRODUCT_ID = 2
The next product to insert will have the id: 3 even if the Product_Id: 2 was already deleted, I'd like to delete the data with it's sequence so the next insert has the id: 2.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这通常是一个坏主意。 ID通常不是您需要填补空白的地方。
但是，如果您只想手动执行此操作，则可以使用手工插入新的行，然后将不使用序列。
您可以使用Postgres函数（例如SETVAL）重置序列，但是您真的不应该这样做，除非在某些数据库恢复案例中。
只要习惯ID不会是顺序的事实。那是常态。如果您有时间追逐序列编号，则可能会做一些有效的事情。
如果您确实需要一些不更改类别（例如类别）的顺序ID，请在插入所有数据后导出和重新创建表，并在导入时省略ID列。但是请记住，ID通常是由其他表使用的，因此您可能必须在每次需要恢复ID时更新所有数据库。
It's a generally bad idea. IDs are usually not the place where you need to fill the gaps.
However, if you are only looking to do this manually, then you can insert the new row with including the ID value by hand, then sequence will not be used.
You can reset the sequence with Postgres functions (eg. setval) , but you really shouldn't do it except in some database recovery cases maybe.
Just get used to the fact that IDs will not be sequential. That's the norm. If you have time going around chasing sequence numbering, you might be doing something productive instead.
If you do want sequential IDs for some non changing things like categories, just export and recreate table after you've inserted all the data, omitting ID column when importing. But remember that IDs are usually used by other tables, so you might have to update all your database every time you want to nicely renumber your IDs.