How can I use a postgres variable in my SELECT statement?

Question

I'm storing a value in a variable featureId and then trying to use that value in my SELECT statement, but postgres seems to be taking the name literally and looking for a column called "featureid". I'm getting an error "ERROR: column "featureid" does not exist LINE 4: featureId,"

My code is below. How can I use the value of the variable in my SELECT statement?

SELECT id INTO featureId FROM tableA WHERE NAME = 'some value';

INSERT INTO tableB (client_id, feature_id, does_have)
  SELECT
  id,
  featureId,
  TRUE
FROM tableA

@stickybit there isn't one. I'm just running this piece by piece in the editor at the moment — intA
– intA, Commented May 17, 2019 at 21:01

sticky bit · Accepted Answer · 2019-05-17 21:14:49Z

4

Without a declared variable your SELECT INTO is the version of SELECT INTO that creates a table. To see it for yourself try:

SELECT id
       INTO featureid
       FROM tablea
       WHERE name = 'some value';

SELECT *
       FROM featureid;

For assigning the value to a variable the variable must be declared. You can use an anonymous DO block.

DO
$$
DECLARE
  featureid tablea.id%TYPE;
BEGIN
  SELECT id
         INTO featureid
         FROM tablea
         WHERE name = 'some value';

  INSERT INTO tableb
              (client_id,
               feature_id,
               does_have)
              SELECT id,
                     featureid,
                     true
                     FROM tablea;
END;
$$
LANGUAGE plpgsql;

answered May 17, 2019 at 21:14

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Comments

CodeRonin · Accepted Answer · 2019-05-17 21:26:02Z

There are few errors on what you're tryng to do:

sql is declarative language so you're asking what to do not how to do and this is for this reason that you cannot store variables and some statements like declare and begin-end should be used in trigger and not in a simple query.
you are executing two statements: select and insert into and they are executed one after the other, so once again you cannot store a variable.
insert into, insert a single record but potentially you're tryng to retrieve more data with your select statement (if NAME is not unique)

if 'some-value' is a known constant and NAME is unique just insert that value in the where clause of the insert into. If you're tryng to insert more data take a look on bulk insert syntax of postgres: bulk insert

Collectives™ on Stack Overflow

How can I use a postgres variable in my SELECT statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related