PostgreSQL- insert result of query into exisiting table, auto-increment id-CodePudding

I have created an empty table with the following SQL statement. My understanding (based on this tutorial: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-serial/) was that SERIAL PRIMARY KEY will automatically provide an auto-incremented id for every new row:

CREATE TABLE "shema".my_table
(
    id SERIAL PRIMARY KEY,
    transaction text NOT NULL,
    service_privider text NOT NULL,
    customer_id text NOT NULL,
    value numeric NOT NULL        
)
WITH (
    OIDS = FALSE
);

ALTER TABLE "shema".my_table
    OWNER to admin;

Now I am querying another tables and would like to save the result of that query into my_table. The result of the query outputs following schema:

transaction 
service_provider 
customer_id 
value

meaning the schema of my_table minus id. when I try to execute:

INSERT into my table
Select {here is the query}

Then I am getting an error that column "id" is of type integer but expression is of type text. I interpret it that the sql query is looking for id column and cannot find it. How can I insert data into my_table without explicitly stating id number but have this id auto-generated for every row?

CodePudding user response：

Always mention the columns you want to INSERT:

INSERT INTO schemaname.my_table("transaction", service_privider, customer_id, value)
SELECT ?, ?, ?, ?;

If you don't, your code will break now or somewhere in the future.

By the way, transaction is a reserved word, try to use a better column name.