PostgreSQL conditional select throwing error

Question

I have a PostgreSQL database hosted on Heroku which is throwing me this error that I can't wrap my head around.

CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, age SMALLINT, right_handed BOOLEAN)
SELECT 1 id FROM people WHERE age IN (1) AND right_handed IN (1)

It gives me the error:

Error in query: ERROR: syntax error at or near "IN"

Don't know how to proceed, any help would be greatly appreciated.

There is no autoincrement in Postgres. postgresql.org/docs/current/sql-createtable.html — user330315
– user330315, Commented Feb 13, 2022 at 21:58
@a_horse_with_no_name have I then misinterpreted point 18 on this docs file? cs50.readthedocs.io/heroku Do you maybe know what they mean by that? — User
– User, Commented Feb 13, 2022 at 22:06
What they mean is they did not proof read their docs. As has been said there is no autoicrement keyword in Postgres. The SO link they include is for sqlite. — Adrian Klaver
– Adrian Klaver, Commented Feb 13, 2022 at 22:11

Robert Long · Accepted Answer · 2022-02-13 22:21:59Z

1

AUTOINCREMENT is not a valid option for CREATE TABLE in Postgres

You can use SERIAL or BIGSERIAL:

ALTER TABLE myTable ADD COLUMN myColumn BIGSERIAL PRIMARY KEY;

edited Feb 13, 2022 at 22:21

answered Feb 13, 2022 at 22:12

Robert Long

7,6476 gold badges37 silver badges76 bronze badges

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

6 Comments

User Over a year ago

Clear! What would be the alternative? Or would PRIMARY KEY suffice to also auto increment?

Robert Long Over a year ago

Apologies, you can use SERIAL or BIGSERIAL :)

user330315 Over a year ago

The use of serial is discouraged. Better use identity columns

Adrian Klaver Over a year ago

No, a PRIMARY KEY is just the column or columns that constitute the primary key for a table. AUTOINCREMENT is a process for making the value of column increment automatically. The two are exclusive of each other, though AUTOINCREMENT is often used to supply values for a PK. It is also possible to autoincrement a column that is not a PK.

Robert Long Over a year ago

@AdrianKlaver OK, maybe I've missed your point but the question is about usage of AUTOINCREMENT in Postgres

|

Collectives™ on Stack Overflow

PostgreSQL conditional select throwing error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related