The root of the problem is
INSERT without target column list
- which is a popular way to shoot yourself in the foot. Only use this syntax shortcut if you know exactly what you are doing.
The manual on
The target column names can be listed in any order. If no list of
column names is given at all, the default is all the columns of the
table in their declared order; or the first N column names, if
there are only N columns supplied by the
VALUES clause or
query. The values supplied by the
VALUES clause or query are associated with the explicit or implicit column list left-to-right.
The cure: list target columns explicitly.
INSERT INTO cities (cityname, state) -- target columns!
VALUES ('San Francisco','CA');
This is assuming that
cityid can be NULL or has a column default.
Typically, the table should look like this:
CREATE TABLE city -- personal advice: use singular terms for table names
city_id serial PRIMARY KEY
, cityname text NOT NULL
, state text NOT NULL -- REFERENCES state(state_id)
Ideally, you also have a table
state listing all possible states and a
FOREIGN KEY reference to it.