query return null in bash for psql

Question

I am tring to get the count for the records in a certain table in bash scripts but the $num is null when returned (should be a number). And the query is correct when I directly run in pgadmin that I can get the number of rows. Any one know what is wrong?

declare -a ROW=($(psql \
        -X \
        -U $DB_USER \
        -h $DB_HOST \
        -d $DB_NAME \
        -p $DB_PORT \
        --set ON_ERROR_STOP=on \
        --no-align \
        -t \
        --field-separator ' ' \
        --quiet \
        -c "SELECT count(*) as num
            FROM table_test)")
    )
    
    echo "num_error: $num  here"
    if [[ $num == 0 ]]; then
        echo "no error occur within the past 1 hour"
    elif [[ $num == '' ]]; then
        echo "return nothing"
    else echo "$num"
    fi

You assign the output to ROW, but then you check $num... — Benjamin W.
– Benjamin W., Commented Feb 23, 2021 at 20:00

Barmar · Accepted Answer · 2021-02-23 20:17:27Z

1

SQL aliases don't become shell variables, so using AS num in the query will not set $num in the shell.

The output of the query is being put in the ROW array, so you can get the value you want from ${ROW[0]}. There's also no need to use an array if the query just returns a single value. So you could do:

num=$(psql \
        -X \
        -U $DB_USER \
        -h $DB_HOST \
        -d $DB_NAME \
        -p $DB_PORT \
        --set ON_ERROR_STOP=on \
        --no-align \
        -t \
        --field-separator ' ' \
        --quiet \
        -c "SELECT count(*)
            FROM table_test)")
    )

answered Feb 23, 2021 at 20:17

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Comment

YihanBao Over a year ago

Hi Barmar, thanks, I actually test this code and also

declare -a ROW=($(psql \ -X \ -U $DB_USER \ -h $DB_HOST \ -d $DB_NAME \ -p $DB_PORT \ --set ON_ERROR_STOP=on \ --no-align \ -t \ --field-separator ' ' \ --quiet \ -c "SELECT count(*) as num FROM table_test)") ) num_error=${ROW[0]} echo "numtest_error: $num_error here"

it is still return me empty answer...

Collectives™ on Stack Overflow

query return null in bash for psql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related