How to insert data from one table into another as PostgreSQL array?

Question

I have the following tables:

CREATE TABLE "User" (
    id integer DEFAULT nextval('"User_id_seq"'::regclass) PRIMARY KEY,
    name text NOT NULL DEFAULT ''::text,
    coinflips boolean[]
);
CREATE TABLE "User_coinflips_COPY" (
    "nodeId" integer,
    position integer,
    value boolean,
    id integer DEFAULT nextval('"User_coinflips_COPY_id_seq"'::regclass) PRIMARY KEY
);

I'm no looking for the SQL statement that grabs the value entry from each row in User_coinflips and inserts it as an array into the coinflips column on User.

Any help would be appreciated!

Update

Not sure if it's important but I just realized a minor mistake in my table definitions above, I replace User_coinflips with User_coinflips_COPY since that accurately describes my schema. Just for context, before it looked like this:

CREATE TABLE "User_coinflips" (
    "nodeId" integer REFERENCES "User"(id) ON DELETE CASCADE,
    position integer,
    value boolean NOT NULL,
    CONSTRAINT "User_coinflips_pkey" PRIMARY KEY ("nodeId", position)
);

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Dec 18, 2019 at 13:03
Ha thanks, wasn't aware of this convention indeed! (In this case I'm working with an auto-generated schema though, so I wasn't able to influence the column name). — nburk
– nburk, Commented Dec 18, 2019 at 13:05

user330315 · Accepted Answer · 2019-12-18 13:05:31Z

1

You are looking for an UPDATE, rather then insert.

Use a derived table with the aggregated values to join against in the UPDATE statement:

update "User"
  set conflips = t.flips
from (
   select "nodeId", array_agg(value order by position) as flips
   from "User_coinflips"
   group by "nodeId"
) t
where t."nodeId" = "User"."nodeId";

answered Dec 18, 2019 at 13:05

user330315

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

Collectives™ on Stack Overflow

How to insert data from one table into another as PostgreSQL array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related