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"));

Laurenz Albe · Accepted Answer · 2020-06-23 08:34:07Z

2

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, 2020 at 8:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Jun 23, 2020 at 7:22

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

János Over a year ago

usage of quoted identifiers: anyway IDE generates the database automatically, it is out of my scope :(

János Over a year ago

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?

user330315 Over a year ago

@János: you can only call currval() if you called nextval() before that: postgresql.org/docs/current/functions-sequence.html

Collectives™ on Stack Overflow

How to get primary key sequence in Postgres?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related