PostgreSQL: Using SERIAL column and initializing table with subquery

Question

http://www.sqlfiddle.com/#!17/5c665/1

I've made products and pub_products tables and trying to copy data from pub_products to products.

products:

+--------------+-----------------------+
| Column       | Type                  |
|--------------+-----------------------+
| product_id   | character(3)          |
| product_name | character varying(20) |
| inventory    | integer               |
| price        | integer               |
| manufacturer | character varying(20) |
+--------------+-----------------------+

pub_products:

+---------------+-----------------------+
| Column        | Type                  |                                                            
|---------------+-----------------------+
| hproduct_id   | integer               | <-- serial
| hproduct_name | character varying(20) |
| hinventory    | integer               |
| hprice        | integer               |
+---------------+-----------------------+

I want to select product_name, inventory, and price columns from products table and insert them to pub_products table.

INSERT INTO pub_products (hproduct_id, hproduct_name, hinventory, hprice)
SELECT product_name, inventory, price
FROM products
WHERE manufacturer = 'Factory3';

But DBMS says "INSERT has more target columns than expressions". Why is this happen? As I know, SERIAL column(hproduct_id) of PostgreSQL is auto-increment column, so I didn't give any initial value. How do I give default values to SERIAL column?

user330315 · Accepted Answer · 2019-04-30 08:46:59Z

3

Remove that column from the column list of the target table, then Postgres will apply the default value:

INSERT INTO pub_products (hproduct_name, hinventory, hprice)
SELECT product_name, inventory, price
FROM products
WHERE manufacturer = 'Factory3';

answered Apr 30, 2019 at 8:46

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Using SERIAL column and initializing table with subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related