CODEWITHSUNDEEP
postgresql
srprsd
srprsd

Reputation: 3

PostgreSQL id column not defined

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:

enter image description here

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

SELECT * from table_name WHERE ID = 1;

Upvotes: 0

Views: 1137

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657122

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:

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246858

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.

Upvotes: 0

Related Questions