Postgresql auto-increment is not working in Ubuntu

Question

I have the following table called work_expenses:

                                                             Table "public.work_expenses"
       Column       |  Type   | Collation | Nullable |                        Default                         | Storage  | Stats target | Description 
--------------------+---------+-----------+----------+--------------------------------------------------------+----------+--------------+-------------
 work_expensesid    | integer |           | not null | nextval('work_expenses_work_expensesid_seq'::regclass) | plain    |              | 
 workid             | integer |           |          |                                                        | plain    |              | 
 employeeid         | integer |           |          |                                                        | plain    |              | 
 date_linkid        | integer |           |          |                                                        | plain    |              | 
 expense_categoryid | integer |           |          |                                                        | plain    |              | 
 cost_incl_gst      | numeric |           |          |                                                        | main     |              | 
 note               | text    |           |          |                                                        | extended |              | 
Indexes:
    "work_expenses_pkey" PRIMARY KEY, btree (work_expensesid)
Foreign-key constraints:
    "work_expenses_date_linkid_fkey" FOREIGN KEY (date_linkid) REFERENCES date_link(date_linkid)
    "work_expenses_employeeid_fkey" FOREIGN KEY (employeeid) REFERENCES employee(employeeid)
    "work_expenses_expense_categoryid_fkey" FOREIGN KEY (expense_categoryid) REFERENCES expense_category(expense_categoryid)
    "work_expenses_workid_fkey" FOREIGN KEY (workid) REFERENCES work(workid)

When I insert data without entering work_expensesid it should auto-increment but it does not. Instead to the following INSERT commmand:

INSERT INTO work_expenses (work_expensesid,workid,employeeid,date_linkid,expense_categoryid,cost_incl_gst,note)
VALUES
        (NULL,1,220,4,5.00,NULL),
        (NULL,1,220,5,33.75,NULL),
        (631,1,218,13,21.50,'trailer load of tree branches')
;

I get an error message as follows:

ERROR:  INSERT has more target columns than expressions
LINE 1: ...mployeeid,date_linkid,expense_categoryid,cost_incl_gst,note)
                                                                  ^

This problem I have with all other tables. Can someone please point me into the right direction on this. What I am trying to achieve is that I do not need to provide a new serial number each time I enter a record going forward. The table currently has got 365 records which were entered by providing a unique work_expensesid. Additional information:

         Sequence "public.work_expenses_work_expensesid_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.work_expenses.work_expensesid

Schema |               Name                |   Type   |  Owner   |    Size    | Description 
--------+-----------------------------------+----------+----------+------------+-------------
 public | work_expenses_work_expensesid_seq | sequence | postgres | 8192 bytes |

crewdb=# select count(*) from work_expenses;
 count 
-------
   365
(1 row)

score 4 · Accepted Answer · 2019-12-05T21:44:02.410

4

INSERT has more target columns than expressions

this simply means you need to remove the column work_expensesid from your insert target list:

INSERT INTO work_expenses 
  (workid,employeeid,date_linkid,expense_categoryid,cost_incl_gst,note)
VALUES
  (NULL, 1,220,4,5.00,NULL),
  (NULL, 1,220,5,33.75,NULL),
  (631, 1,218,13,21.50,'trailer load of tree branches');

Online example

It's recommended to use identity columns for this nowadays:

create table work_expenses
(
  work_expensesid     integer   not null generated always as identity primary key,
  workid              integer,
  employeeid          integer,
  date_linkid         integer,
  expense_categoryid  integer,
  cost_incl_gst       numeric,
  note                text   
)

edited Dec 05 '19 at 21:44

answered Dec 05 '19 at 21:15

1

sorry, counted the columns incorrectly. The solution is then to just remove the reference to the `work_expensesid` column – Dec 05 '19 at 21:43
I did that, see last comment. It still does not work. But thanks anyway:-) – Christian Hick Dec 05 '19 at 21:54
1

Well then you inserted some values manually which didn't advance the sequence. See [here](https://stackoverflow.com/questions/9314382) for an explanation. and [here](https://stackoverflow.com/questions/244243) for a solution. When using `generated always` you can't accidentally provide a value for such a column. – Dec 05 '19 at 22:00
How can I change what you are suggesting on an existing column. I mean "not null generated always as identity primary key". I mean is that safe to do on a table with existing rows? – Christian Hick Dec 05 '19 at 23:13
Buy the way, the solution in the last comment of yours worked. Thanks heaps for your help!!! – Christian Hick Dec 05 '19 at 23:26
@ChristianHick: please ask a new question on how to change a `serial` to an `identity`. It seems there is none here on stackoverflow. – Dec 06 '19 at 07:10

Postgresql auto-increment is not working in Ubuntu

1 Answers1