9

I have the following table:

CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);

When I try to restore it using following command:

psql -U postgres -h localhost -d word -f word.sql 

it gives me this error:

multiple primary keys for table "word" are not allowed

How can I use multiple primary keys in postgres?

21

how can i use multiple primary keys in postgres ?

You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one.

You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for a table.

However, the code you show doesn't produce the error you mentioned:

$ psql -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
CREATE TABLE
ALTER TABLE
ALTER TABLE
$

At a guess you've actually already defined this table and you're ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:

ERROR:  relation "word" already exists
ALTER TABLE
ERROR:  multiple primary keys for table "word" are not allowed

The real error here is the first one, of course.

I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:

$ psql -v ON_ERROR_STOP=1 -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__

ERROR:  relation "word" already exists
$

See how it stops at the first error?

(It'd be the default but that'd break backward compatibility).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.