pl/pgsql - How to create a table from another table

Question

I want to create a table random_record that takes in the same columns as another table simulated_records; one of the columns is grade. But I keep getting this error:

ERROR: "random_record.grade" is not a known variable
LINE 45: random_record.grade = c_grade;
^

********** Error **********

ERROR: "random_record.grade" is not a known variable
SQL state: 42601
Character: 1635

FOR i IN 1..6 LOOP

    CREATE TABLE random_record AS 
    SELECT ....

    IF random_record.grade = '-' THEN

    .....

    END IF;


....

END LOOP;

I am not sure if I am properly creating the table.

refer this link: postgresql.org/docs/9.2/static/sql-createtableas.html — Laxmi
– Laxmi, Commented Nov 2, 2016 at 3:48
I think you want a CASE ... WHEN as part of the CREATE TABLE AS ... SELECT ... — Craig Ringer
– Craig Ringer, Commented Nov 2, 2016 at 5:05

Pavel Stehule · Accepted Answer · 2016-11-02 04:17:12Z

1

you created table well, but the table is not a variable, so line
```
IF random_record.grade = '-' THEN 
```
has not any sense. It is hard to identify, what you want, because using table in this context has not any value.
creating in table in cycle has another issue - the statement CREATE TABLE will work only in first loop of cycle. Second loop has to fail, because table exists already.

It is hard help, because this code is messy - it is mixing variables, tables together, and it is not possible. Every object has own dimension, own access methods, and these mechanisms are different.

answered Nov 2, 2016 at 4:17

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pl/pgsql - How to create a table from another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related