CODEWITHSUNDEEP
sqlpostgresql
nburk
nburk

Reputation: 22731

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

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)
);

Upvotes: 1

Views: 64

Answers (1)

user330315
user330315

Reputation:

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";

Upvotes: 1

Related Questions