postgresql: INSERT INTO statement with select query

Question

I have to insert some data in my table of existing client_id column , so i am using select with insert

INSERT into 'my_table' (column1, client_id, column3) VALUES (val1,select distinct client_id from 'my_table', val3)

I need client_id from the same table my_table and i need client_ids in insert statement.

SELECT DISTINCT client_id FROM my_table gives me 113 client_id so i want to insert some row for each 113 client using the above approach.

I did this query

INSERT INTO client_notification_preferences (client_id, object_type , frequency,created_at,updated_at) SELECT DISTINCT client_id, 'ClientShipment',1, CURRENT_TIMESTAMP , CURRENT_TIMESTAMP FROM client_notification_preferences;

but this gives me this error

 create_table "client_notification_preferences", id: :uuid, default: "uuid_generate_v4()", force: :cascade do |t|
t.uuid     "client_id"
t.string   "object_type"
t.integer  "frequency"
t.datetime "created_at",  null: false
t.datetime "updated_at",  null: false

end

Show us the table definition for client_notification_preferences. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 31, 2018 at 6:40
when I insert any row, id is automatically inserted so how can it violate primary key constraint — summu
– summu, Commented Oct 31, 2018 at 6:43
Well, you have some unique constraint which is being violated. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 31, 2018 at 6:46

SAEED REZAEI · Accepted Answer · 2018-10-31 06:25:40Z

6

if val1 and val3 are variables and client_id is a field from my_table you can use from below code.

 INSERT into 'my_table' (column1, client_id, column3) select distinct val1, 
    client_id,val3 from 'my_table'

for example

INSERT into 'my_table' (column1, client_id, column3) select distinct 1, client_id,2 from 'my_table'

edited Oct 31, 2018 at 6:25

answered Oct 31, 2018 at 6:17

SAEED REZAEI

5503 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

summu Over a year ago

i only need client_id from select statement, and select distinct client_id from 'my_table' will give me 113 client_ids together , but i need one client at a time for inserting data

SAEED REZAEI Over a year ago

do you need last client_Id?

summu Over a year ago

i need all client_id

Tim Biegeleisen Over a year ago

What is val1 ?

SAEED REZAEI Over a year ago

'val1' is a value

Collectives™ on Stack Overflow

postgresql: INSERT INTO statement with select query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related