How to get primary key sequence in Postgres?

Question

I have following table:

-- DDL generated by Postico 1.5.10
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE "Ticket" (
    id bigint PRIMARY KEY,
    "paymentId" text NOT NULL,
    "transactionId" text,
    "dateCreated" timestamp without time zone NOT NULL,
    "dateValidated" timestamp without time zone,
    "sellerPaymentId" text NOT NULL,
    "sellerPaymentProvider" text NOT NULL,
    "userId" bigint NOT NULL,
    "userIdFb" text NOT NULL,
    "userName" text NOT NULL,
    "eventDescription" text NOT NULL,
    "eventTimeId" text,
    "eventId" text NOT NULL,
    "eventName" text NOT NULL,
    "startTime" timestamp without time zone,
    "endTime" timestamp without time zone,
    quantity bigint,
    "unitPrice" text,
    seats jsonb[],
    location text NOT NULL,
    link text,
    "eventTimesSelected" jsonb,
    "otherListsSelected" jsonb,
    "transactionIdBarion1" text,
    "transactionIdBarion2" text
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "pk:Ticket.id" ON "Ticket"(id int8_ops);

When inserting a new row, got this error:

[ ERROR ] PostgreSQLError.server.error._bt_check_unique: POST /startPayment duplicate key value violates unique constraint "pk:Ticket.id" (ErrorMiddleware.swift:26)
[ DEBUG ] Possible causes for PostgreSQLError.server.error._bt_check_unique: Key (id)=(1) already exists. (ErrorMiddleware.swift:26)

How the heck I can reset the primary key sequence? There are many answers on internet, but what is the name of my sequence? :) I do not see any 'name' in my DDL.

I tried fetch sequence name like this:

select currval(pg_get_serial_sequence("Ticket", "id"));

no luck:

ERROR:  column "Ticket" does not exist
LINE 1: select currval(pg_get_serial_sequence("Ticket", "id"));

score 1 · Accepted Answer · edited Jun 23 '20 at 08:34

1

pg_get_serial_sequence() expects string values, not identifiers. Your problems stem from the fact that you used those dreaded quoted identifiers when creating the table which is strongly discouraged.

You need to pass the double quotes inside single quotes:

select currval(pg_get_serial_sequence('"Ticket"', '"id"'));

You should reconsider the usage of quoted identifiers to avoid problems like that in the future.

How the heck I can reset the primary key sequence

edited Jun 23 '20 at 08:34

Laurenz Albe

209,280
17
206
263

answered Jun 23 '20 at 07:22

`usage of quoted identifiers`: anyway IDE generates the database automatically, it is out of my scope :( – János Jun 23 '20 at 08:57
I tried this: `select currval(pg_get_serial_sequence('"Ticket"', 'id'));` and got response: `ERROR: currval of sequence "Ticket_id_seq" is not yet defined in this session` `id` is a primary key, isn't it a sequence? – János Jun 23 '20 at 08:58
1

@János: you can only call `currval()` if you called `nextval()` before that: https://www.postgresql.org/docs/current/functions-sequence.html – Jun 23 '20 at 09:11

How to get primary key sequence in Postgres?

1 Answers1