CODEWITHSUNDEEP
bashpsql
YihanBao
YihanBao

Reputation: 563

query return null in bash for psql

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

Upvotes: 0

Views: 296

Answers (1)

Barmar
Barmar

Reputation: 780798

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)")
    )

Upvotes: 1

Related Questions