1

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.

virsnu parameswaran
  • 11
  • 5

1 Answers1

0

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;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • My nodes table node_id column consist('nodes_node_id_seq) and i still get error with your queries and it says that nodes doesnt have relations – virsnu parameswaran Apr 01 '20 at 08:46
  • @virsnuparameswaran so please add the CREATE TABLE statement of your table to the question. Since you already seem to have a sequence attached to your table, the second option I posted should work. – Jim Jones Apr 01 '20 at 09:00
  • @virsnuparameswaran I just edited my answer with a few suggestions – Jim Jones Apr 01 '20 at 09:03
  • I have edited my question for a better understanding. – virsnu parameswaran Apr 01 '20 at 10:19
  • @virsnuparameswaran what is the problem with the code I added in my last edit? – Jim Jones Apr 01 '20 at 10:21
  • It worked fine, but i'm still confuse with my other tables,btw thanks for helping i get a better picture now.Thanks – virsnu parameswaran Apr 01 '20 at 10:35
  • @virsnuparameswaran glad it helped. Basically you can insert the result from a table to another one using this syntax from the last edit. You only have to keep in mind that the amount of columns from insert and select must be equal. cheers. – Jim Jones Apr 01 '20 at 10:40