PostgreSQL: Using SERIAL column and initializing table with subquery

0
votes

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?

1
postgresql

1 Answers

3
votes

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';