前言

最近公司的項(xiàng)目遇到多次這樣的需求：需要將原表符合條件的記錄復(fù)制到新表，但原表的主鍵字段在新表中需轉(zhuǎn)為普通字段。這篇博客主要介紹如何為新表設(shè)置自增主鍵字段。還有一個(gè)是如何利用窗口函數(shù)將表中某幾個(gè)字段值重復(fù)的記錄只保留一條有效，其余重復(fù)記錄作廢或者刪除，這個(gè)需求也是最近經(jīng)常遇到

設(shè)置自增主鍵

• 我們經(jīng)常碰到這樣的場(chǎng)景，通過(guò)create table as select xxx from table where xx需要將原表符合條件的記錄復(fù)制到新表，值得注意的是這樣新創(chuàng)建的表，并沒有將表結(jié)構(gòu)復(fù)制到新表，原表的主鍵字段在新表中是一個(gè)普通字段
• 首先，先確保當(dāng)前表中沒有主鍵以及當(dāng)前要設(shè)置主鍵字段的字段類型。
• 第一步，要在pg數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)序列，序列是PostgreSQL實(shí)現(xiàn)自增功能的核心數(shù)據(jù)庫(kù)對(duì)象，用處就是字段值可以設(shè)置為由已創(chuàng)建的序列提供，每次序列可以自增生成一個(gè)唯一的整數(shù)，我們可以設(shè)置序列的初始值和步長(zhǎng)

	-- 序列名稱最好以表名_id_sq
CREATE SEQUENCE [IF NOT EXISTS] sequence_name
    [ INCREMENT BY increment ]
    [ START WITH start ]
    [ ... ] -- 其他參數(shù)如 MINVALUE, MAXVALUE, CACHE, CYCLE

因此，我們首先要知道當(dāng)前要設(shè)置為主鍵字段的最大值，如果當(dāng)前字段的最大值為100，那么序列的初始值就要設(shè)為101，步長(zhǎng)默認(rèn)是1，一般不需要設(shè)置步長(zhǎng)

-- 先查出當(dāng)前字段的最大值
select max(id) from table_name
-- 創(chuàng)建序列，指定初始值和步長(zhǎng)
CREATE SEQUENCE table_name_id_seq
START WITH 100
INCREMENT BY 1;

當(dāng)然，如果序列創(chuàng)建錯(cuò)了，也可以直接修改已存在序列地初始值和步長(zhǎng)

-- 修改序列初始值為500
ALTER SEQUENCE table_name_id_seq RESTART WITH 500;
-- 修改序列步長(zhǎng)為5
ALTER SEQUENCE table_name_id_seq INCREMENT BY 5;

下一步，就是指定字段的數(shù)值來(lái)源為已創(chuàng)建的序列，由序列提供字段數(shù)值

ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq ');

最后，為字段添加主鍵約束即可

ALTER TABLE table_name ADD PRIMARY KEY (id);

去重

我們經(jīng)常碰到的數(shù)據(jù)庫(kù)需求就是根據(jù)某幾個(gè)字段，對(duì)重復(fù)記錄去重，只保留一條，其余作廢(valid=0)或者刪除，在pg數(shù)據(jù)庫(kù)，一般我們通過(guò)窗口函數(shù)ROW_NUMBER()實(shí)現(xiàn)這個(gè)功能

UPDATE table_name
SET valid = '0'
WHERE ctid IN (
    SELECT ctid
    FROM (
        SELECT
            ctid,
            ROW_NUMBER() OVER (PARTITION BY field1, field2, field3 ORDER BY ctid) AS rn
        FROM table_name
    ) AS ranked_rows
    WHERE rn > 1  -- 將過(guò)濾條件放在正確的外層
);

這段sql核心是通過(guò)窗口函數(shù)ROW_NUMBER()將同組（重復(fù)記錄集合）的記錄編號(hào)，然后保留編號(hào)最小的一個(gè)，編號(hào)>1的記錄作廢來(lái)實(shí)現(xiàn)去重的目的

UPDATE table_name
SET valid = '0'
WHERE ctid IN (
    SELECT ctid
    FROM (
        SELECT
            ctid,
            ROW_NUMBER() OVER (PARTITION BY field1, field2, field3 ORDER BY ctid) AS rn
        FROM table_name
    ) AS ranked_rows
    WHERE rn > 1  -- 將過(guò)濾條件放在正確的外層
);

ROW_NUMBER() OVER (...) AS rn??: 這行代碼是核心。ROW_NUMBER()是一個(gè)窗口函數(shù)，它會(huì)為每一行分配一個(gè)唯一的序號(hào)

PARTITION BY field1, field2, field3??: PARTITION BY子句定義了如何將數(shù)據(jù)分組。這里表示??將 field1, field2, field3這三個(gè)字段完全相同的記錄歸為同一組??。窗口函數(shù)會(huì)在每個(gè)組內(nèi)獨(dú)立進(jìn)行編號(hào)

ORDER BY ctid??: 這指定了在每個(gè)分組內(nèi)，記錄按 ctid排序。ctid是 PostgreSQL 系統(tǒng)列，代表行的物理位置（文件號(hào)和頁(yè)內(nèi)的元組索引），可以理解為每行的唯一物理標(biāo)識(shí)。按此排序意味著??在同一分組內(nèi)，先插入的行（理論上ctid更?。?huì)獲得更小的行號(hào)

表復(fù)制

僅復(fù)制表結(jié)構(gòu)（不復(fù)制數(shù)據(jù)）

-- 基本用法，復(fù)制列定義（包括NOT NULL約束）
CREATE TABLE new_table (LIKE old_table);

-- 擴(kuò)展用法，復(fù)制包括索引、約束等更多對(duì)象
CREATE TABLE new_table (LIKE old_table INCLUDING ALL);

復(fù)制表結(jié)構(gòu)及其所有數(shù)據(jù)

記得要注意，表的約束、索引等并沒有復(fù)制，需要手動(dòng)重新添加

-- 標(biāo)準(zhǔn)語(yǔ)法
CREATE TABLE new_table AS SELECT * FROM old_table;

-- 簡(jiǎn)化語(yǔ)法，效果相同
CREATE TABLE new_table AS TABLE old_table;

以上就是設(shè)置PostgreSQL表字段為自增主鍵的方法的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL表字段為自增主鍵的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論