Обсуждение: immediate set values

I'd like to insert into a table several pairs of values where the first
value does not change. Rather than numerous INSERT statements, I think
there should be a way to insert from a select with immediate values, but I
just can't figure it out. What I'd like:

INSERT INTO mytable
SELECT 1, *
FROM (1,2,3,4)

...which would result in inserting the pairs

1,1
1,2
1,3
1,4

Is there some clean way of doing this, or do I really need multiple
INSERTs?

--Greg

Something like:

INSERT INTO mytable SELECT 1, * FROM (select 1
                                       union all
                                      select 2
                                       union all
                                      select 3
                                       union all
                                      select 4)

will work, but will run slowly, assuming you'll be inserting more than 4
rows at a time.

You could make a helper table that just contains integers <= n, if n is the
max number of rows you want to insert.

Then

INSERT INTO mytable SELECT 1, i FROM ints WHERE i < 100;

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Gregory Seidman
> Sent: Sunday, May 19, 2002 5:15 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] immediate set values
>
>
> I'd like to insert into a table several pairs of values where the first
> value does not change. Rather than numerous INSERT statements, I think
> there should be a way to insert from a select with immediate values, but I
> just can't figure it out. What I'd like:
>
> INSERT INTO mytable
> SELECT 1, *
> FROM (1,2,3,4)
>
> ...which would result in inserting the pairs
>
> 1,1
> 1,2
> 1,3
> 1,4
>
> Is there some clean way of doing this, or do I really need multiple
> INSERTs?
>
> --Greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Joel Burton sez:
} Something like:
}
} INSERT INTO mytable SELECT 1, * FROM (select 1
}                                        union all
}                                       select 2
}                                        union all
}                                       select 3
}                                        union all
}                                       select 4)
}
} will work, but will run slowly, assuming you'll be inserting more than 4
} rows at a time.
}
} You could make a helper table that just contains integers <= n, if n is the
} max number of rows you want to insert.
}
} Then
}
} INSERT INTO mytable SELECT 1, i FROM ints WHERE i < 100;

Okay, I'm an idiot. The table I'm inserting into has two columns, each of
which references a key in another table. I already have the helper table I
need. Thanks for your help. My query now looks like:

INSERT INTO mytable
SELECT 1, id
FROM referencedtable
WHERE id IN (1,2,3,4)

} - J.
--Greg