CODEWITHSUNDEEP
postgresql
Emixam23
Emixam23

Reputation: 3964

Whats the proper way to activate autoincrementation on PostgreSQL primary key?

Starting on using PostgreSQL, I am currently having a situation where my primary gets updated only from the code, but not frm script I am running at init...

I have the 3 following SQL file:

// 1.
GRANT ALL PRIVILEGES ON DATABASE testdb TO testapi;

// 2.
CREATE TABLE IF NOT EXISTS cities (
    id SERIAL PRIMARY KEY UNIQUE,
    name VARCHAR ( 50 ) UNIQUE NOT NULL
);

CREATE SEQUENCE IF NOT EXISTS cities_seq_id;
SELECT setval('cities_seq_id', (SELECT max(id)+1 FROM cities), false);
ALTER TABLE cities ALTER COLUMN id SET DEFAULT nextval('cities_seq_id');

// 3.
INSERT INTO cities (name)
VALUES ('Barcelona'),
       ('Seville'),
       ('Madrid'),
       ('Valencia'),
       ('Andorra la Vella'),
       ('Malaga');

Starting postgreSQL like so:

windows: docker run --name postgres -v %cd%\init\database:/docker-entrypoint-initdb.d -p 5432:5432 -e POSTGRES_USER=testapi -e POSTGRES_PASSWORD=packngo -e POSTGRES_DB=testdb -d postgres:14.0
----
linux: docker run --name postgres -v $(pwd)/init/database:/docker-entrypoint-initdb.d -p 5432:5432 -e POSTGRES_USER=testapi -e POSTGRES_PASSWORD=packngo -e POSTGRES_DB=testdb -d postgres:14.0

Why am I getting the following (when calling my rest API of course, but result turns out to be the same when I do a SELECT, it's just feel easier to understand):

[
    {
        "id": 0,
        "name": "Barcelona"
    },
    {
        "id": 0,
        "name": "Seville"
    },
    {
        "id": 0,
        "name": "Madrid"
    },
    {
        "id": 0,
        "name": "Valencia"
    },
    {
        "id": 0,
        "name": "Andorra la Vella"
    },
    {
        "id": 0,
        "name": "Malaga"
    }
]

I really don't understand.. I tried to update my SQL file to insert ID along names but no success.. any idea?

Thanks

Upvotes: 0

Views: 1397

Answers (2)

Schwern
Schwern

Reputation: 165546

I was unable to reproduce your result, there must be something wrong with how you're querying your data.

That said, you can make your schema much simpler.

CREATE TABLE IF NOT EXISTS cities (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);
  • Foreign keys should not be serial. They do not auto-increment. Use integer to refer to a serial primary key.
  • price certainly should not be serial. It is presumably an integer or numeric.
  • Storing dates as varchar(27) is very strange. If it's a list of dates, use a date array date[]. If it's range of dates use daterange.
CREATE TABLE IF NOT EXISTS trips (
    id SERIAL PRIMARY KEY,
    destination_id INTEGER NOT NULL REFERENCES cities,
    origin_id INTEGER NOT NULL REFERENCES cities,
    dates DATERANGE NOT NULL,
    price INTEGER NOT NULL,
    UNIQUE (destination_id, origin_id)
);

Upvotes: 1

Kaus2b
Kaus2b

Reputation: 845

I think newer PG versions has something like this:

CREATE TABLE my_table (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR ( 50 ) UNIQUE NOT NULL
);

Upvotes: 1

Related Questions