Postgresql – Insert into int8range from columns of another table

postgresqlpostgresql-9.4range-types

I have two tables in PostgreSQL:

Table indexces_grid:

+---------------+------------+
| Column Name   | Type       |
+---------------+------------+
| indexid       | bigint     |
| iprange       | int8range  |
+---------------+------------+

Table citydetails:

+---------------+-----------+
| Column Name   | Type      |
+---------------+-----------+
| ipfrom        | bigint    |
| ipto          | bigint    |
| cityname      | varchar   |
+---------------+-----------+

I want to insert the ipfrom and ipto into the single column iprange of the indexces_grid table.

Best Answer

It's so simple:

INSERT INTO indexces_grid ("Row2")
SELECT int8range(ipfrom, ipto, '[]')
  FROM citydetails;

The documentation linked above tells you all.

Note that one has to use the three-parameter form of the range constructor function (here int8range()) to include the upper bound, too. Using the two-parameter form, the upper bound would be non-inclusive.

I've created an SQLFiddle to show this at work.

Related Solutions

PostgreSQL – How to Insert Values into a Table from a Select Query

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

dbfiddle here

PostgreSQL – Insert Multiple Rows with Default Values

Using generate_series() and ctes. Tested in rextester.com:

create table t
( tid serial primary key,
  i int default 0,
  name text default 'Jack'
) ;


with ins as
  (insert into t (i, name)               -- all the columns except any serial
   values (default, default)
   returning i, name
  )
insert into t 
  (i, name)
select 
  ins.i, ins.name
from 
  ins cross join generate_series(1, 9);  -- one less than you need

For the case when there is only one column and it's a serial, I see no way to use the default. Using the generate_series is straight-forward:

insert into course
  (course_id)
select
  nextval('course_course_id_seq')
from
  generate_series(1, 10);

If there are other, more "peculiar" default values, like a UUID function or the non-standard clock_timestamp(), the statement will have to be adjusted accordingly, like the serial case.

Best Answer

Related Solutions

PostgreSQL – How to Insert Values into a Table from a Select Query

PostgreSQL – Insert Multiple Rows with Default Values

Related Question