CODEWITHSUNDEEP
postgresql
alive
alive

Reputation: 29

PostgreSQL INSERT INTO production table FROM staging table

I created two tables using Django models and the scripts look something like this

I am using PostgreSQL 10

production table:

CREATE TABLE public.foods_food(
    id integer NOT NULL DEFAULT nextval('foods_food_id_seq'::regclass),
    code character varying(100) COLLATE pg_catalog."default",
    product_name character varying(255) COLLATE pg_catalog."default",
    brands character varying(255) COLLATE pg_catalog."default",
    quantity character varying(255) COLLATE pg_catalog."default",
    last_modified_datetime timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL
)

staging table:

CREATE TABLE public.foods_temp(
    id integer NOT NULL DEFAULT nextval('foods_temp_id_seq'::regclass),
    code character varying(100) COLLATE pg_catalog."default",
    product_name character varying(255) COLLATE pg_catalog."default",
)

I copied a CSV file to the staging table and than I tried to copy the columns from the staging table to the production table using the following query.

INSERT INTO foods_food
   SELECT * FROM foods_temp;

But I got this error.

ERROR:  null value in column "created_at" violates not-null constraint

I can set the created_at column to accept null in order to make it work but I want the created_at values to be auto populated when entries are inserted.

Is there other way to copy columns to the production table and automatically insert the timestamp?

Upvotes: 1

Views: 1657

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247410

Then you need to set default values:

ALTER TABLE public.foods_food ALTER last_modified_datetime
   SET DEFAULT current_timestamp;
ALTER TABLE public.foods_food ALTER created_at
   SET DEFAULT current_timestamp;

Upvotes: 2

Related Questions