Postgresql修改序列产生器

06 Apr 2022

1 minute reading time

#问题

在我日常使用 PostgreSQL 数据库过程中,会遇到一个问题:我在重新创建一个数据库表时,往往会导入已有的数据,这样会导致新增表数据时,由于 id 采用了自增,会从1开始生成,然后由于已有数据的缘故, 所以会导致 id 重复报错。

#解决

查看了 stackoverflow 的一些回答,发现了一个解决方案:采用 ALTER SEQUENCE 语句进行修改。

#ALTER SEQUENCE

ALTER SEQUENCE — 更改序列生成器的定义

语法:

ALTER SEQUENCE [ IF EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART [ [ WITH ] restart ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

参数:

name
要变更的序列名称。

IF EXISTS
如果序列不存在,不抛出错误。(这种情况会发出通知)

data_type
可选的子句AS data_type改变序列的数据类型。有效的类型是smallint、integer和bigint。

改变数据类型会自动改变序列的最小值和最大值,前提是以前的最小值和最大值是旧数据类型的最小值或最大值(换句话说,如果序列是用NO MINVALUE或NO MAXVALUE创建的,隐含地或明确地)。否则,最小值和最大值将被保留,除非作为同一命令的一部分给出新的值。如果最小值和最大值不适合新的数据类型,将产生一个错误。

increment
子句INCREMENT BY increment是可选的。一个正值将形成升序,一个负值将形成降序。如果没有指定,将保持旧的增量值。

minvalue
NO MINVALUE
可选的子句MINVALUE minvalue决定了一个序列可以产生的最小值。如果没有指定MINVALUE,将分别使用默认的1和升序和降序的数据类型的最小值。如果两个选项都没有指定,将保持当前的最小值。

maxvalue
NO MAXVALUE
可选的子句MAXVALUE maxvalue决定了序列的最大值。如果没有指定MAXVALUE,将分别使用数据类型的最大值和升序和降序的-1的默认值。如果两个选项都没有指定,将保持当前的最大值。

start
可选的子句START WITH start改变序列的记录起始值。这对当前的序列值没有影响;它只是设置了未来ALTER SEQUENCE RESTART命令将使用的值。

restart
可选子句RESTART [ WITH restart ] 改变序列的当前值。这与调用is_called = false的setval函数类似:指定的值将由下次调用nextval返回。写入没有重启值的RESTART等同于提供由CREATE SEQUENCE记录的或由ALTER SEQUENCE START WITH最后设置的起始值。

与setval调用相反,序列上的RESTART操作是事务性的,并阻止并发的事务从同一序列中获取数字。如果这不是想要的操作模式,应该使用setval。

cache
条款CACHE缓存使序列号被预先分配并存储在内存中,以加快访问速度。最小值为1(一次只能生成一个值,即没有缓存)。如果没有指定,将保持旧的缓存值。

CYCLE
可选的CYCLE关键词可以用来使序列在升序或降序分别达到最大值或最小值时进行环绕。如果达到极限,下一个生成的数字将分别是最小值或最大值。

NO CYCLE
如果指定了可选的NO CYCLE关键字,在序列达到最大值后对nextval的任何调用将返回一个错误。如果没有指定CYCLE或NO CYCLE,将保持旧的循环行为。

OWNED BY table_name.column_name
OWNED BY NONE
OWNED BY选项使序列与一个特定的表列相关联,这样,如果该列(或其整个表)被丢弃,序列也将被自动丢弃。如果指定了这个选项,这个关联将取代以前为序列指定的任何关联。指定的表必须有相同的所有者,并且与序列在同一模式下。指定 OWNED BY NONE 会删除任何现有的关联,使序列成为 "独立的"。

new_owner
序列的新所有者的用户名。

new_name
序列的新名称。

new_schema
序列的新schema。

#例子

ALTER SEQUENCE pages_id_seq RESTART with 14688;

pages新的序列自增id 将从14688开始

#参考



/sw-load.js?v=e5ae5a1ed170f4499ac6292e7164b68528c51f6d6518cd75a49e6a6b737831d5728da21fc14dcbc7a91328e53858c6ff7195cc3fc8b25f0feeaef2af151d6686 /fireball.gif?v=569e393374f2af74d6c575090904aaf51e641e5eb5ea89ae7c7de01f7293abc165b3a7e8685690a8b951c778603fec98ae6822ff2f7ea86a536776966cb65d5d /favicon.ico?v=beac62000b1965d4e036575d58ef681f6d4c35c6b7ccafb1e286f99bebd3ca5f30f51dd9dcfb4b832132891ff814b18f1e040c08fc7a49be064016fab53c26b3 /favicon-16x16.png?v=5a9fcce4aea1dfb145b39a296c90c3fd0cac49dd7a83999a2bdd2a0ee4e6950f3a1b1f1fe14522b2fcb4cad75734f2a4e84fd964b56217748b9778a2e1697ff7 /favicon-32x32.png?v=35be3e52467cebe716e17b163f587373cd6c52d1993e868caae46dbfc53ba6955ca6ea2c77119ab6d8e535f2cdde502bcf7fe60984f83d2cdb36ee6b92ee37cb /icon-192x192.png?v=3820c1b1e6d755d2b7c2a04a65f0f1feef793b297f7ee995947137ccd8f73ec304457f6ce1df987a9a0a13ed7dacd203225505b832ccd2318b530ae53a55cebc /icon-512x512.png?v=de62ae905479fd813300d286ed1d2fe6bb6f6292623a5d918691642f6dd09a68943c69ed2a95a1820076919e69ff4fda668bb79e610ebc1d3200fedd7f634443 /apple-touch-icon.png?v=5d32464a608cc4b6e656e7be5bba48360b472b399ab82ebf4bbf4a93bb964e26f7bb0a1897ebbbdaf11444e7a93215f04f1c7fe8c08df5dddeeddbf97f93e149 /main.css?v=8ab3ba2ea49a89d1cee56b62a947c88597785d0842586199965b60438f9430279525aef9d01320077a32656df3e7435d48ccd7228cfc9bcd6a97ff0a4cc79358 /nerd-fonts.css?v=4213ecfcacca379b433c0fd135281c627c074e42d243cca41777df5738649704db63d448a19b13f80dfc9337485d8a1eb1a4b77cf2fa9d1fe2d3b6768c66e7bb /unstyle.css?v=b14bd48a2efbd463d973763aa3184c69aa02164c0891acacc9eab49ddd275f98f0050b4c31d2093e4671e7abe04f9459a041f0064384a90d97b8ff21b6824825 /langs.css?v=12474958ee314a9fde4704e1f5a032dc632d41f9461faca326ac284297766c4ceb07b45fec7fbc09fa72b0f21dcc64f0c31e64fc2e5e838b1d30f5fe540afd78 /syntax-theme-light.css?v=ccdddc2d2d88953c6d7d0376777b8409028ef625a7321dfa41619547b4f5eddbe89aa95ff5e7e2620da0ea13fbabebe2fd544620bc7e81e3294776b3425df48a /syntax-theme-dark.css?v=dfede4879841e4a58e5fc71115aa5f5b82e206d85eb771ff4e5a40a1d82621570aad2458f637365ae4370d9a1cf5070edc9765f7c2d4506e12e2ba3c6081ffd5 /sw-style.css?v=352cab856807e725351d62a9cae9dc445a675ab7e0bb0d3b12440b08dd574526c62827a5f4af706f7ad74df996a7f71f2c2a306fc1b188e1560007f0d4eda4fc /posts/page/2/ /posts/page/3/ /posts/page/4/ /posts/page/5/ /posts/page/6/ /posts/page/7/ /posts/page/8/ /posts/page/9/ /posts/page/10/ /posts/page/11/ /posts/page/12/ /posts/page/13/ /posts/page/14/ /posts/page/15/ /posts/page/16/ /categories/ /tags/ /tags/413/ /tags/a-li-yun/ /tags/a-li-yun-oss/ /tags/acme-sh/ /tags/adsense/ /tags/aes/ /tags/ai/ /tags/aliyun/ /tags/an-quan/ /tags/apisix/ /tags/archive-zip/ /tags/atop/ /tags/authing/ /tags/bei-fen/ /tags/ben-di-hua/ /tags/bian-ma/ /tags/bing-fa-bian-cheng/ /tags/bot/ /tags/buf/ /tags/casbin/ /tags/cdn/ /tags/ce-lue-mo-shi/ /tags/cert-manager/ /tags/certificatemanager/ /tags/chrome/ /tags/ci/ /tags/clarity/ /tags/clean-cache/ /tags/cody/ /tags/colab/ /tags/conc/ /tags/concurrency/ /tags/configmaps/ /tags/consul/ /tags/containerd/ /tags/coverage/ /tags/coze/ /tags/cpu/ /tags/crash/ /tags/crawler/ /tags/crypto/ /tags/cte/ /tags/cuo-wu-chu-li/ /tags/cve-2021-22205/ /tags/data-visualization/ /tags/database/ /tags/datax/ /tags/date/ /tags/decode/ /tags/dms/ /tags/dns/ /tags/dns-authorization/ /tags/docker/ /tags/duo-lu-fu-yong/ /tags/duo-ping-tai-bo-ke-fa-bu-gong-ju/ /tags/easeprobe/ /tags/email/ /tags/embed/ /tags/ent/ /tags/errgroup/ /tags/error/ /tags/external/ /tags/fang-wen-kong-zhi/ /tags/fen-bu-shi-lian-lu-zhui-zong/ /tags/ffmpeg/ /tags/finalizers/ /tags/fly-io/ /tags/fsck/ /tags/fu-wu-fan-she-xie-yi/ /tags/fu-zai-jun-heng/ /tags/gcloud/ /tags/gin/ /tags/github/ /tags/github-pages/ /tags/gitlab/ /tags/go/ /tags/golang/ /tags/gonew/ /tags/gong-ju/ /tags/google/ /tags/google-analytics/ /tags/google-api/ /tags/google-cloud/ /tags/google-oauth2/ /tags/govulncheck/ /tags/grafana/ /tags/grpc/ /tags/gzip/ /tags/health-check/ /tags/helm/ /tags/hosts/ /tags/http/ /tags/https/ /tags/hugo/ /tags/humanize/ /tags/i18n/ /tags/image-compress/ /tags/imap/ /tags/init/ /tags/jian-kang-jian-cha/ /tags/jian-kang-tan-zhen/ /tags/jian-kong/ /tags/json/ /tags/k8s/ /tags/katana/ /tags/ke-shi-hua/ /tags/kratos/ /tags/kubernetes/ /tags/lan-jie-qi/ /tags/lint/ /tags/linter/ /tags/linux/ /tags/liu-lan-qi/ /tags/load-balancing/ /tags/log/ /tags/loki/ /tags/lua/ /tags/magika/ /tags/mapping/ /tags/markdown/ /tags/memory/ /tags/mergo/ /tags/metabase/ /tags/microsoft/ /tags/minio/ /tags/mo-hu-ce-shi/ /tags/monitor/ /tags/nei-cun/ /tags/nginx/ /tags/nginx-ingress/ /tags/node-js/ /tags/novelai/ /tags/oauth2/ /tags/once/ /tags/opentelemetry/ /tags/opentracing/ /tags/openwrite/ /tags/os/ /tags/paas/ /tags/performance/ /tags/playwright/ /tags/playwright-go/ /tags/plugin/ /tags/png/ /tags/pngcrush/ /tags/pngquant/ /tags/postgresql/ /tags/profiling/ /tags/prometheus/ /tags/protobuf/ /tags/proxy/ /tags/psutil/ /tags/pyroscope/ /tags/rancher/ /tags/rand/ /tags/redis/ /tags/ren-gong-zhi-neng/ /tags/ren-zheng/ /tags/retry/ /tags/reverse-proxy/ /tags/rong-qi/ /tags/rueidis/ /tags/sealos/ /tags/security/ /tags/server-reflection/ /tags/serverless/ /tags/service/ /tags/she-ji-mo-shi/ /tags/shi-jian-chu-li/ /tags/shu-ju-fen-xi/ /tags/singleflight/ /tags/slug/ /tags/soap/ /tags/spider/ /tags/sql/ /tags/sqlc/ /tags/stable-diffusion/ /tags/storage/ /tags/superset/ /tags/swap/ /tags/sync/ /tags/tcp-udp/ /tags/template/ /tags/test/ /tags/text/ /tags/tianji/ /tags/time/ /tags/tls/ /tags/tong-xin-mo-shi/ /tags/trace/ /tags/trace-viewer/ /tags/traefik/ /tags/tu-pian/ /tags/ubuntu/ /tags/uri/ /tags/v0-dev/ /tags/video/ /tags/visualization/ /tags/visualstudio/ /tags/wang-luo/ /tags/wang-ye-tan-ce/ /tags/wasi/ /tags/wasm/ /tags/wire/ /tags/wireshark/ /tags/wsdl/ /tags/wsl/ /tags/xiao-wen-ti/ /tags/xie-cheng/ /tags/xun-huan/ /tags/you-jian/ /tags/yu-ming/ /tags/yuan-shu-ju/ /tags/zheng-shu/ /tags/zhong-jian-jian/ /tags/zhua-bao/ /tags/zhuang-tai-ma/ /posts/go-recivie-email/ /posts/go-soap-desc/ /posts/docker-desktop-proxy/ /posts/hugo-deploy-github/ /posts/v0dev/ /posts/markdown-preview-enhanced/ /posts/go-humanize-introduce/ /posts/wsl-error1/ /posts/cody/ /posts/katana/ /posts/tianji/ /posts/magika/ /posts/cozebot/ /posts/go-generate-slug/ /posts/metabase/ /posts/gogenerategaid/ /posts/docker-init-command/ /posts/grpc-client-load-balancing/ /posts/grpcqing-qiu-zhong-shi/ /posts/alliyunrdserr/ /posts/kubernetes-resource-reservation/ /posts/atop/ /posts/kubernetes-externalname/ /posts/go-refresh-cdn/ /posts/apisix-enabled-gzip/ /posts/ru-he-shou-ji-xi-tong-dang-ji-hou-de-nei-cun-zhuan-chu-xin-xi/ /posts/conversion-of-chinese-characters-into-pinyin/ /posts/mergo-desc/ /posts/conc-better-structured-concurrency-for-go/ /posts/kubernetes-externaltrafficpolicy/ /posts/clarity-learn/ /posts/postgresql-cte-expressions/ /posts/go-design-patterns-strategy/ /posts/go1-22-new-for-loop/ /posts/apisix-proxy-grpc-service/ /posts/golou-dong-guan-li-gong-ju-govulncheck/ /posts/go-wdsl/ /posts/nginx-ingress-httpqing-qiu-413wen-ti-ji-jie-jue-fang-fa/ /posts/playwright-gojian-jie/ /posts/google-api-go-clientdiao-yong-googleadsensebao-gao-jie-kou-shi-bai-de-wen-ti-ji-jie-jue-fang-an/ /posts/gonewjian-jie/ /posts/ent-sql-modifier/ /posts/golang-embedjian-dan-jie-shao/ /posts/nginxfan-xiang-dai-li-cuo-wu-de-wen-ti-ji-jie-jue-fang-fa/ /posts/shi-yong-cert-managershen-qing-mian-fei-zheng-shu/ /posts/go-i18n/ /posts/rueidisjian-jie/ /posts/postgresqlzen-me-jie-jue-division-by-zerowen-ti/ /posts/kubernetes-healthcheck/ /posts/ranchercattleclusteragentcouldnotresolvehost/ /posts/sealos-version-compare-error/ /posts/kubernetes-podxiu-gai-hostswen-jian/ /posts/apisixhu-lue-urida-xiao-xie/ /posts/sqlcchu-ti-yan/ /posts/apisix-dockerbu-shu-zhong-ding-xiang-wen-ti/ /posts/go-wasi/ /posts/goshi-xian-jian-dan-fan-xiang-dai-li/ /posts/certificatemanagershi-yong-dnsshou-quan-shen-qing-zheng-shu/ /posts/googlecloudqing-chu-cdnhuan-cun/ /posts/apisixshu-ju-bei-fen/ /posts/apisixru-he-tian-jia-zi-ding-yi-cha-jian/ /posts/apisixgen-ju-qing-qiu-hostfang-wen-bu-tong-lu-jing/ /posts/shi-yong-acmezi-dong-geng-xin-apisix-sslzheng-shu/ /posts/containerdben-di-diao-shi-huan-jing-da-jian/ /posts/containerdjian-dan-an-zhuang-he-ke-hu-duan-shi-yong/ /posts/helmjie-shao-ji-shi-yong/ /posts/ubuntu20-04she-zhi-dns/ /posts/postgresqlde-jsonlei-xing/ /posts/shi-yong-apisixdai-li-postgresqlfu-wu/ /posts/dataxshu-ju-tong-bu-zhong-yu-dao-de-wen-ti/ /posts/goshi-jian-chu-li-ku-carbon/ /posts/supersetjian-dan-shi-yong/ /posts/shi-yong-fly-iobu-shu-miniodui-xiang-cun-chu-fu-wu/ /posts/fly-iobu-shu-goying-yong/ /posts/pngya-suo-gong-ju/ /posts/apisixshi-xian-nginxde-proxy-hide-headercan-shu/ /posts/qian-hou-duan-shi-yong-aesjia-mi-chuan-shu-shu-ju/ /posts/fly-iochu-ti-yan/ /posts/aihui-hua-chu-ti-yan/ /posts/kubernetes-configmaps-subpath-no-reload/ /posts/easeprobejian-dan-jie-shao-shi-yong/ /posts/grpczhong-jian-jian/ /posts/gitlab-cve-2021-22205/ /posts/grpcqing-qiu-zhua-bao/ /posts/grpc-server-reflection/ /posts/prometheus-operato/ /posts/gojin-xing-liu-lan-qi-wang-ye-jie-tu/ /posts/gopsutiljie-shao/ /posts/ji-yi-ci-a-li-yun-ossbao-cuo-de-jie-jue/ /posts/grpcjian-kang-tan-zhen/ /posts/grpcjian-kang-jian-cha/ /posts/gofa-song-you-jian/ /posts/goxie-cheng-bi-bao-de-wen-ti/ /posts/goya-suo-pngtu-xiang-da-xiao/ /posts/gochu-li-zipjie-ya-luan-ma-wen-ti/ /posts/singleflightjie-shao/ /posts/goding-shi-jian-kong-httpszheng-shu/ /posts/gobing-fa-sync-oncejie-xi/ /posts/gojie-qu-shi-pin-mou-yi-zheng-tu-pian/ /posts/go-errgroup/ /posts/ying-yong-nei-cun-sheng-gao-yuan-yin-pai-cha/ /posts/duo-ping-tai-bo-ke-fa-bu-gong-ju-openwriteshi-yong/ /posts/bufchu-ru-men-2/ /posts/bufchu-ru-men-1/ /posts/gotong-ji-dai-ma-ce-shi-fu-gai-lu/ /posts/kswapd0-consumes-a-lot-of-cpu/ /posts/kswapd0xiao-hao-da-liang-cpu/ /posts/postgresqlxiu-gai-xu-lie-chan-sheng-qi-de-can-shu/ /posts/kratosye-wu-zhuang-tai-ma-he-httpzhuang-tai-ma-fen-chi/ /posts/apisixshi-yong-authingjin-xing-ren-zheng-deng-lu/ /posts/google-oauth2shi-jian/ /posts/gomo-hu-ce-shi/ /posts/pyroscope-chi-xu-fen-ping-tai/ /posts/fsck/ /posts/grpcdan-xiang-an-quan-lian-jie/ /posts/log-and-trace/ /posts/k8s-finalizers/ /posts/casbinxue-xi-1/ /posts/goru-he-shi-yong-si-you-cang-ku-mo-kuai/ /posts/grpcduo-lu-fu-yong/ /posts/trace-in-sql/ /posts/grpcyuan-shu-ju/ /posts/golangduo-ban-ben-gong-cun/ /posts/grpccuo-wu-chu-li/ /posts/grpclan-jie-qi/ /posts/grpctong-xin-mo-shi/ /posts/gochang-jian-linter/ /posts/wasmingo/ /posts/containerdpei-zhi-si-you-cang-ku/ /posts/rong-qi-chu-xian-shi-jian-yi-chang-wen-ti-ji-jie-jue-fang-fa/ /posts/consulxue-xi/ /posts/kubernetesan-zhuang-apisix/ /posts/she-zhi-rancherfu-wu-qi-de-ben-di-kubernetesji-qun/ /posts/shi-yong-sealosbu-shu-kubernetesji-qun/ /posts/kratoszi-ding-yi-handlerfunc-mei-you-qing-qiu-ri-zhi-de-wen-ti-ji-jie-jue/ /posts/rsstest/ /posts/golangsui-ji-timesleepchu-xian-de-wen-ti/ /posts/ru-he-zai-ginzhong-cha-kan-prometheuszhi-biao/ /posts/shi-yong-prometheusshou-ji-miniozhi-biao/ /posts/fen-bu-shi-lian-lu-zhui-zong-chu-tan-2/ /posts/fen-bu-shi-lian-lu-zhui-zong-chu-tan/ /posts/bian-li-maplie-biao-de-golangmo-ban/ /posts/golang-templates/ /posts/wireru-men/ /posts/gofan-xing-chu-tan/ /posts/functional-options/ /posts/docker-grafanaqi-dong-shi-bai/ /posts/nginxda-jian-jing-tai-tu-pian-zi-yuan-fu-wu-qi/ /posts/traefikru-men-shi-yong/ /posts/go-modulezhi-nan-he-chang-jian-wen-ti/ /posts/gitlab-cigou-jian-dockerjing-xiang/ /posts/github-pagezi-ding-yi-yu-ming/ /posts/gocuo-wu-shi-jian/ /posts/shi-yong-redisshi-xian-dui-lie/ /posts/my-first-post/ /atom.xml /posts/ c1tyh4ll.png?v=e6bb8cdead47e48c0deba1e0a3016070984b5f7271166a72638f9ec5a6ef2d2eb8012e8e4cb64f4f3b6574c6d708bf2ae660d04b8b59a6de675ce4d4d62dd4c3 bk-prk.png?v=b00246fb5faeab35a588f347224b00d53083a9d5f4ae8cd87c0c2e0432bf7f348c6a6ab6f4e4edaf5ddbf13ee34b24946dd0af4cd7db6f4f334599f38917ac9e bk-prk.png?v=b00246fb5faeab35a588f347224b00d53083a9d5f4ae8cd87c0c2e0432bf7f348c6a6ab6f4e4edaf5ddbf13ee34b24946dd0af4cd7db6f4f334599f38917ac9e /icon-192x192.png?v=3820c1b1e6d755d2b7c2a04a65f0f1feef793b297f7ee995947137ccd8f73ec304457f6ce1df987a9a0a13ed7dacd203225505b832ccd2318b530ae53a55cebc /sitemap.xml /search_index.en.json /search.js /elasticlunr.min.js?v=d106ab529e29f6be48a948124723fcf411e06b8e4ea4477b551f256d190991fe3ca7f121714ef8d9f594a4aa680f2bbd37a5d8004abfbf3ea6eb3d4ea259ec0f">