Menu
I have a CSV file with two columns: city and zipcode. I want to be able to copy this file into a PostgreSQL table using the copy command and at the same time auto generate the id value.
The table has the following columns: id, city, and zipcode.
My CSV file has only: city and zipcode.
504
In PostgreSQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement. You use the ALTER TABLE statement in PostgreSQL to add or drop a primary key.
By simply setting our id column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our id column with a unique, primary key value for every INSERT .
COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.
PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.
When you add a primary key to a table, PostgreSQL creates a unique B-tree index on the column or a group of columns used to define the primary key. Normally, we add the primary key to a table when we define the table’s structure using CREATE TABLE statement. CREATE TABLE TABLE ( column_1 data_type PRIMARY KEY , column_2 data_type, … );
In PostgreSQL, a primary key constraint uniquely identifies rows. One or more columns can be defined as a primary key in a table. Primary key is a combination of NOT NULL constraint and UNIQUE constraint, meaning, the column or group of columns that are defined as the primary key enforce column to have not-null and unique values.
Defining auto-generated primary keys. There are four ways to define a column with automatically generated values: Using the DEFAULT clause. You can use this method with sequences and UUIDs. Here are some examples: CREATE TABLE has_integer_pkey ( id bigint DEFAULT nextval('integer_id_seq') PRIMARY KEY, ...
For COPY FROM, each field in the file is inserted, in order, into the specified column. Table columns not specified in the COPY FROM column list will receive their default values. COPY with a file name instructs the PostgreSQL server to directly read from or write to a file.
The COPY command should do that all by itself if your table uses a serial column for the id:
If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns.
So you should be able to say:
copy table_name(city, zipcode) from ... and the id will be generated as usual. If you don't have a serial column for id (or a manually attached sequence), then you could hook up a sequence by hand, do your COPY, and then detach the sequence.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
