Inserting into a table with a serial column using psycopg2

Question

I have two simple tables in PostgreSQL that I created through pgAdmin4:

Table1:

CREATE TABLE public.table1
(
    id serial,
    name text,
    PRIMARY KEY (id)
)

Table2:

CREATE TABLE public.table2
(
    fk_id integer,
    name text,
    PRIMARY KEY (name),
    CONSTRAINT fk FOREIGN KEY (fk_id)
        REFERENCES public.table1 (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

After creating this second table with a foreign key referencing the first table the data type id from table1 changes to integer automatically:

And if I try to change manually the type to serial it isn't found.

And also using psycopg2 this insert works:

connection = psycopg2.connect(user = "user",
                              password = "pass",
                              host = "localhost",
                              port = "5432",
                              database = "db")

cursor = connection.cursor()

postgres_insert_query = """INSERT INTO table1 (id,name) VALUES (%s,%s)"""
record_to_insert = (1,'sometext')
cursor.execute(postgres_insert_query, record_to_insert)

connection.commit()

But this one fails:

postgres_insert_query = """INSERT INTO table1 (name) VALUES (%s)"""
record_to_insert = ('sometext')

Printing this error: "not all arguments converted during string formatting"

postgresql.org/docs/current/… Read the section about serial types. TL;DR serial is not a real type, just a shorthand way of defining an integer column with a sequence. — 404
You expect what to increment? If you defined the table with only one column, inserting anything will be only into that id column, which means it won't use the sequence, so nothing will increment. — 404
But I expected it to autoincrement when I insert a new value: "INSERT INTO table1 (name) VALUES ('somtext')" <- This fails. And this works -> "INSERT INTO table1 (id,name) VALUES (1,"sometext")" — David1212k
That error has nothing to do with Postgres, because the plain SQL works: rextester.com/FBVW93753 — a_horse_with_no_name
The insert failed because you didn't create a tuple. Try record_to_insert = ('sometext',) — Jeremy

David1212k David1212k · Accepted Answer · 2019-10-21T10:55:09

I just missed a comma as commented @Jeremy

record_to_insert = ('sometext',)

Inserting into a table with a serial column using psycopg2

1 Answers