PL/pgSQL SELECT INTO

Master PL/pgSQL SELECT INTO — the workhorse for reading single rows. Critical PostgreSQL distinction: plain SELECT INTO silently takes the first row on multi-row results (NO error, unlike MySQL). Use SELECT INTO STRICT for exactly-one-row enforcement. Worked examples cover single values, multiple targets, %ROWTYPE assignments, NO_DATA_FOUND/TOO_MANY_ROWS handling, and the FOUND variable.

The SELECT INTO statement runs a query and stores the result in one or more PL/pgSQL variables. It's the workhorse for reading single-row data from a table inside a procedural block.

Two forms exist:

• SELECT ... INTO target FROM ... — quietly assigns the first row to target. If the query returns zero rows, target becomes NULL. If it returns multiple rows, only the first one is used and no error is raised.
• SELECT ... INTO STRICT target FROM ... — same, but raises an exception when the query doesn't return exactly one row. Zero rows raises NO_DATA_FOUND; multiple rows raises TOO_MANY_ROWS.

The target can also be a single %ROWTYPE variable or RECORD variable, in which case the entire row is assigned at once.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    salary        NUMERIC(10, 2),
    department    VARCHAR(50)
);

INSERT INTO employees (first_name, last_name, salary, department) VALUES
    ('Alice',   'Johnson', 75000, 'Engineering'),
    ('Bob',     'Smith',   62000, 'Engineering'),
    ('Charlie', 'Davis',   88000, 'Sales'),
    ('Dana',    'Wilson',  55000, 'Sales');

The simplest use: pull one column from one row into one variable:

DO $$
DECLARE
    emp_salary NUMERIC(10, 2);
BEGIN
    SELECT salary
    INTO   emp_salary
    FROM   employees
    WHERE  first_name = 'Alice';

    RAISE NOTICE 'Alice''s salary: %', emp_salary;
END $$;

Pull several columns into separate variables in one query — much cheaper than running two SELECTs:

DO $$
DECLARE
    emp_first  employees.first_name%TYPE;
    emp_last   employees.last_name%TYPE;
    emp_salary employees.salary%TYPE;
BEGIN
    SELECT first_name, last_name, salary
    INTO   emp_first, emp_last, emp_salary
    FROM   employees
    WHERE  employee_id = 3;

    RAISE NOTICE 'Employee: % %', emp_first, emp_last;
    RAISE NOTICE 'Salary:   %', emp_salary;
END $$;

Note the use of %TYPE for each variable — they automatically match the column types so a future ALTER TABLE doesn't break this code.

Demonstrating the silent first-row behavior. The query matches multiple employees in the Engineering department, but plain SELECT INTO picks one without warning:

DO $$
DECLARE
    emp_name TEXT;
BEGIN
    -- Two employees in Engineering — but no STRICT, no error
    SELECT first_name
    INTO   emp_name
    FROM   employees
    WHERE  department = 'Engineering';

    RAISE NOTICE 'Got: %', emp_name;
    RAISE NOTICE '(no error raised even though 2 rows matched)';
END $$;

The result is whichever row Postgres returned first — typically determined by storage order, not anything stable. Without an ORDER BY, the "first" row can change between queries.

Adding STRICT enforces exactly-one-row semantics. Multiple rows produce TOO_MANY_ROWS; zero rows produce NO_DATA_FOUND:

DO $$
DECLARE
    emp_name TEXT;
BEGIN
    SELECT first_name
    INTO   STRICT emp_name
    FROM   employees
    WHERE  department = 'Engineering';

    RAISE NOTICE 'Got: %', emp_name;
END $$;

The right pattern with STRICT: pair it with an EXCEPTION block to handle the "no row" or "too many rows" cases explicitly:

DO $$
DECLARE
    emp_name TEXT;
    target_id INTEGER := 999;   -- nonexistent
BEGIN
    BEGIN
        SELECT first_name
        INTO   STRICT emp_name
        FROM   employees
        WHERE  employee_id = target_id;

        RAISE NOTICE 'Found: %', emp_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE NOTICE 'No employee with id %', target_id;
        WHEN TOO_MANY_ROWS THEN
            RAISE NOTICE 'Multiple employees with id % (data integrity issue)', target_id;
    END;

    RAISE NOTICE 'Block continued normally.';
END $$;

Pull an entire row in one shot with SELECT * INTO row_var:

DO $$
DECLARE
    emp employees%ROWTYPE;
BEGIN
    SELECT *
    INTO   STRICT emp
    FROM   employees
    WHERE  employee_id = 1;

    RAISE NOTICE 'Name:       % %',  emp.first_name, emp.last_name;
    RAISE NOTICE 'Salary:     %',    emp.salary;
    RAISE NOTICE 'Department: %',    emp.department;
END $$;

After any data-modifying statement (including SELECT INTO without STRICT), the special boolean variable FOUND indicates whether a row was actually returned. It's the lightweight alternative to STRICT + EXCEPTION when you just want to know "did this match anything":

DO $$
DECLARE
    emp_name TEXT;
BEGIN
    SELECT first_name
    INTO   emp_name
    FROM   employees
    WHERE  employee_id = 999;

    IF NOT FOUND THEN
        RAISE NOTICE 'No row matched.';
    ELSE
        RAISE NOTICE 'Matched: %', emp_name;
    END IF;
END $$;

1. Default to STRICT for unique-key lookups — surfaces data anomalies instead of hiding them.
2. Use %TYPE for target variables — automatic schema-tracking saves edits later.
3. Use %ROWTYPE when reading whole rows — clearer than juggling individual scalars.
4. Wrap STRICT in BEGIN ... EXCEPTION when zero / many rows are expected possibilities — handle them gracefully rather than aborting.
5. Use IF NOT FOUND for the lightweight "did the query find anything" pattern when STRICT + EXCEPTION feels heavyweight.
6. Don't rely on plain SELECT INTO with multi-row results. The "first row" is unspecified without ORDER BY; either add LIMIT 1 with explicit ordering, or use STRICT.
7. Be aware of the MySQL/Oracle difference. Coming from those, the "multiple rows raise an error" mental model doesn't apply to PostgreSQL plain SELECT INTO.

• SELECT INTO reads query results into PL/pgSQL variables. The target can be scalars, a %ROWTYPE, or a RECORD.
• Plain SELECT INTO is forgiving: zero rows produces NULL; multiple rows silently picks the first one (no error).
• SELECT INTO STRICT enforces exactly-one-row: NO_DATA_FOUND on zero rows, TOO_MANY_ROWS on multiple.
• Use STRICT for primary-key / unique-key lookups; use plain SELECT INTO when "no match" is acceptable and you'll check FOUND.
• Pair STRICT with BEGIN ... EXCEPTION handlers when you expect to handle missing rows gracefully.
• The FOUND variable is the lightweight check after a plain SELECT INTO.