我正在嘗試將自動增量主鍵添加到從熊貓資料框轉換的 sql 表中。
將資料幀轉換為sql并添加alter table命令以添加自動增量主鍵的代碼如下
test.to_sql('test_sql',schema='test_schema',con=conn,if_exists='replace',index=False)
with conn:
conn.execute("ALTER TABLE test_schema.test_sql ADD XPK_test_sql id SERIAL PRIMARY KEY;")
我收到以下錯誤：
ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "SERIAL"
我檢查了語法，它似乎是正確的。我哪里錯了？還有其他方法嗎？
uj5u.com熱心網友回復：
我不太確定您要做什么：該列是否已經存在并且您想將其設為主鍵，或者您想創建一個新的主鍵列？
將主鍵約束添加到現有列
這些是創建約束的語法檔案：
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
...
where action is one of:
...
ADD table_constraint [ NOT VALID ]
...
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
...
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
所以你需要寫一些類似的東西
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
column_name將表的（現有）列添加table_name為主鍵，并將新約束命名為constraint_name。
添加新的主鍵列
檔案在同一頁面上：
ADD COLUMN [ IF NOT EXISTS ]
This form adds a new column to the table, using the same syntax as CREATE TABLE.
IMO的CREATE TABLE 檔案可讀性不強，但相關部分如下：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
...
where column_constraint is:
...
PRIMARY KEY index_parameters
...
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
...
因此，這是您要查找的語法：
ALTER TABLE table_name ADD COLUMN column_name serial PRIMARY KEY
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/463923.html