CODEWITHSUNDEEP
postgresqlauto-incrementddl
speed bump
speed bump

Reputation: 451

Postgresql auto-increment is not working in Ubuntu

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)

Upvotes: 1

Views: 192

Answers (1)

user330315
user330315

Reputation:

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   
)

Upvotes: 4

Related Questions