PostgreSQL id column not defined

Question

I am new in PostgreSQL and I am working with this database.

I got a file which I imported, and I am trying to get rows with a certain ID. But the ID is not defined, as you can see it in this picture:

so how do I access this ID? I want to use an SQL command like this:

SELECT * from table_name WHERE ID = 1;

Answer 1

If any order of rows is ok for you, just add a row number according to the current arbitrary sort order:

CREATE SEQUENCE tbl_tbl_id_seq; 
ALTER TABLE tbl ADD COLUMN tbl_id integer DEFAULT nextval('tbl_tbl_id_seq');

The new default value is filled in automatically in the process. You might want to run VACUUM FULL ANALYZE tbl to remove bloat and update statistics for the query planner afterwards. And possibly make the column your new PRIMARY KEY ...

To make it a fully fledged serial column:

ALTER SEQUENCE tbl_tbl_id_seq OWNED BY tbl.tbl_id;

See:

• Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Answer 2

What you see are just row numbers that pgAdmin displays, they are not really stored in the database.

If you want an artificial numeric primary key for the table, you'll have to create it explicitly.

For example:

CREATE TABLE mydata (
   id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   obec text NOT NULL,
   datum timestamp with time zone NOT NULL,
   ...
);

Then to copy the data from a CSV file, you would run

COPY mydata (obec, datum, ...) FROM '/path/to/csvfile' (FORMAT 'csv');

Then the id column is automatically filled.