Insert data into an empty table from another table

1.I have a table nodes with node_id(PK),node_name(name),connstr(text),last_snap_id(integer) and this table has 1 row fill with 1,local,dbname = postgres,0

2.I have a table indexes_list with node_id(PK),indexrelid(PK),schemaname,indexrelname which is empty

3.I have to collect the data from pg_stat_user_indexes the columns are indexrelid,schemaname,indexrelname

Questions: How i do fetch data from pg_stat_user_indexes to load into my indexes_list table and the same time and if i use 2 select statement in one i get error.

Answer

Welcome to SO.

First you need to create a SEQUENCE or alternative create the column node_id with the type serial..

CREATE SEQUENCE seq_node_id START WITH 1;

.. and then with a INSERT INTO … (SELECT * …) populate your node table

INSERT INTO nodes (node_id,indexrelid,schemaname,indexrelname)
SELECT nextval('seq_node_id'),indexrelid,schemaname,indexrelname
FROM pg_stat_user_indexes;

If node_id is of type serial, you can simply omit it in the INSERT

INSERT INTO nodes (indexrelid,schemaname,indexrelname)
SELECT indexrelid,schemaname,indexrelname
FROM pg_stat_user_indexes;

EDIT:

These CREATE TABLE and INSERT statements should give you some clarity:

CREATE TABLE nodes2 (
  node_id serial, indexrelid text, schemaname text, indexrelname text
);

INSERT INTO nodes2 (indexrelid,schemaname,indexrelname)
SELECT indexrelid,schemaname,indexrelname
FROM pg_stat_user_indexes;