2

I'm trying to insert some data into SQL database, and the problem is that I'm really green on this. So the MAIN problem is that How can I sort all the items in table? I have 3 main things: ID, CARNUM, TIME. But in this 'Insertion' I have to type the id manually. How can I make that the system would create a numeric id numeration automatically? Here's the insertion code:

   postgres_insert_query = """ INSERT INTO Vartotojai (ID, CARNUM, TIME) VALUES (%s,%s,%s)"""
   record_to_insert = (id, car_numb, Reg_Tikslus_Laikas)
   cursor.execute(postgres_insert_query, record_to_insert)

   connection.commit()
   count = cursor.rowcount
       print (count, "Record inserted successfully into mobile table")

pgadmin sort

pgadmin table

Doug Richardson
  • 10,483
  • 6
  • 51
  • 77
DzITC
  • 869
  • 1
  • 9
  • 23

1 Answers1

1

You could change the datatype of ID to serial, which is an auto incrementing integer. Meaning that you don't have to manually enter an ID when inserting into the database.

Read more about datatype serial: source

  • So I need to create the table again? How do I need to change the ID to Serial? – DzITC Sep 03 '19 at 17:46
  • You only have to change the datatype of ID to serial, you can do this by altering your table. You can try something like `ALTER TABLE Vartotojai ALTER COLUMN ID TYPE SERIAL`. –  Sep 03 '19 at 17:49
  • ERROR: type "serial" does not exist SQL state: 42704 – DzITC Sep 03 '19 at 17:52
  • In this case you can try to create a sequence as explained in [this](https://stackoverflow.com/a/16474780/11918649) post. –  Sep 03 '19 at 17:55
  • I did the ID To bigserial, but now: Input car number:GAG123 Match Failed to insert record into mobile table duplicate key value violates unique constraint "vartotojai_pkey" DETAIL: Key (id)=(1) already exists. – DzITC Sep 03 '19 at 18:24
  • postgres_insert_query = """ INSERT INTO vartotojai (ID, CARNUM, TIME) VALUES (%s,%s,%s)""" record_to_insert = (1, car_numb, Reg_Tikslus_Laikas) – DzITC Sep 03 '19 at 18:24
  • 2 things. First change the insert to omit the ID, having altered the table it will automatically assign it when it is not in the insert. If in the insert it'll use the value supplied. Second, find the highest current value for ID and adjust the sequence value to be greater. – Belayer Sep 03 '19 at 18:41
  • EDIT* The serial as I researched now is called bigint. – DzITC Sep 03 '19 at 20:30