4

Many database engines support auto-incrementing primary keys, and I would like to use this approach in my new DuckDB approach, but I can't figure out how to set it up. For example, in MySQL:

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);
Mark Payne
  • 557
  • 5
  • 12
  • Did you try google, it seems you have to use sequences because there is no auto increment... https://duckdb.org/docs/sql/statements/create_sequence – OracleDev Jul 06 '22 at 11:49
  • Yes, I came across that page, but can't see how to impement it exactly to fill a primary key column. Do you have any suggestions? – Mark Payne Jul 06 '22 at 11:52
  • yes :), create a table, then create the sequence. Then while inserting just use "insert into table persons (personid,...) values (nextval('name_of_your_sequence'), ...)" – OracleDev Jul 06 '22 at 11:56
  • see my answer if this is what you wanted – OracleDev Jul 06 '22 at 12:04

1 Answers1

6

create a table:

CREATE TABLE Persons (
    Personid integer primary key,
    LastName varchar(255) not null,
    FirstName varchar(255),
    Age integer
);

create a sequence:

CREATE SEQUENCE seq_personid START 1;

Insert some data:

INSERT INTO Persons VALUES (nextval('seq_personid'), 'Doe', 'John', 99);
OracleDev
  • 446
  • 3
  • 8
  • 6
    You can use the default too, e.g. `Personid integer primary key default nextval('seq_personid')`, which is closer to the auto increment/serial behaviour of PostgreSQL, MySQL, etc. – Martin Tournoij Nov 02 '22 at 21:00
  • 1
    Yes. For a fuller answer: Alternatively, try this: 1. Create a sequence: ```sql CREATE SEQUENCE seq_personid START 1; ``` 2. Create a table: ```sql CREATE TABLE Persons ( Personid INTEGER PRIMARY KEY DEFAULT NEXTVAL('Publisher_Id_Seq'), LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Age INTEGER ); ``` 3. Insert some data: ```sql INSERT INTO Persons VALUES ('Doe', 'John', 99); ``` – Sualeh Fatehi Feb 04 '23 at 17:22