无法在 postgres 中插入嵌套记录 [英] Unable to insert nested record in postgres

问题描述

我已经成功地在 postgres 中创建了表，但在尝试插入值时遇到了问题.

i had managed to create tables in postgres but encountered issues when trying to insert values.

comands = (
CREATE TYPE student AS ( 
   name TEXT,
   id  INTEGER
)

CREATE TABLE studentclass( 
    date DATE NOT NULL,
    time TIMESTAMPTZ NOT NULL, 
    PRIMARY KEY (date, time), 
    class student
)
)
And in psycog2

command = (
INSERT INTO studentclass (date, time, student) VALUES (%s,%s, ROW(%s,%s)::student)
)


student_rec = ("John", 1)
record_to_insert = ("2020-05-21", "2020-05-21 08:10:00", student_rec)
cursor.execute(commands, record_to_insert)

执行时，错误是不正确的参数，如果我尝试在 INSERT 语句中硬编码学生值，它会通知我有关学生无法识别的列.

When executed, the errors are the incorrect argument and if i tried to hard coded the student value inside the INSERT statement, it will inform me about the unrecognized column for student.

请指教.

推荐答案

一个问题是列名是 class 而不是 student.其次是 psycopg2 将 tuple 适配为 复合类型

One issue is the column name is class not student. Second is psycopg2 does tuple adaption as composite type

所以你可以这样做:

insert_sql = "INSERT INTO studentclass (date, time, class) VALUES (%s,%s,%s)"
student_rec = ("John", 1)
record_to_insert = ("2020-05-21", "2020-05-21 08:10:00", student_rec)
cur.execute(insert_sql, record_to_insert)
con.commit()

select * from studentclass ;
    date    |          time           |  class   
------------+-------------------------+----------
 05/21/2020 | 05/21/2020 08:10:00 PDT | (John,1)

这篇关于无法在 postgres 中插入嵌套记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！