CODEWITHSUNDEEP
bashpostgresqlshelldebian
Jimmy
Jimmy

Reputation: 12487

Create postgresql database entry using bash script

Below is a section from my bash script, which should create a frontend user:

DBUSER="testing"
DBNAME="testing"
ADMIN_NAME="A"
ADMIN_LNAME="B"
ADMIN_EMAIL="[email protected]"
ADMIN_PASSWORD="'password'"

PGPASSWORD=$DBPASS psql -U $DBUSER -d $DBNAME -h 127.0.0.1 -c "INSERT INTO users_user VALUES (1, \'$ADMIN_NAME\', \'$ADMIN_LNAME\', \'$ADMIN_EMAIL\', \'$ADMPASS\', \'true\', \'zxc\')"

The result I get it this:

ERROR:  syntax error at or near "\"                                                                                              
LINE 1: INSERT INTO users_user VALUES (1, \'A\', \'B\', \'...

Can anyone tell me where I am going wrong please?

Upvotes: 1

Views: 1553

Answers (3)

Aaron
Aaron

Reputation: 31

Based on your code you need to removed the backslash before the single quotes. Also unless it is intentional you need to remove the single quotes from the ADMIN_PASSWORD variable.

I would also recommend referencing the bash variables as described Michael Jaros. Though you don't seem to have anything in your code that would require it, it is a good habit to get into, helps make the variable uses more visible and can prevent issues in the future where that syntax would be required. So based on these recommendations your code ends up being:

DBUSER="testing"
DBNAME="testing"
ADMIN_NAME="A"
ADMIN_LNAME="B"
ADMIN_EMAIL="[email protected]"
ADMIN_PASSWORD="password"

PGPASSWORD=${DBPASS} psql -U ${DBUSER} -d ${DBNAME} -h 127.0.0.1 -c "INSERT INTO users_user VALUES (1, '${ADMIN_NAME}', '${ADMIN_LNAME}', '${ADMIN_EMAIL}', '${ADMPASS}', 'true', 'zxc')"

Upvotes: 3

Pavel Stehule
Pavel Stehule

Reputation: 45750

Another safe and readable sql scripting - use a psql client side variables:

# create table foo(email text, pass text);

EMAIL="xx@xx"
PASS="jsjsj"

echo "insert into foo values (:'email',:'pass')" \
     | PGPASSWORD=$DBPASS psql postgres -v email="$EMAIL" -v pass="$PASS"

Upvotes: 1

Michael Jaros
Michael Jaros

Reputation: 4681

  • $ADMPASS is probably not defined, because you also have $ADMIN_PASSWORD

  • Inside double quotes, be sure to wrap your variable names in curly braces like this:

    ${ADMIN_NAME}

    Otherwise Bash will not always be able to separate them from succeeding characters.

    The complete line then would look like this:

    PGPASSWORD="$DBPASS" psql -U "$DBUSER" -d "$DBNAME" -h 127.0.0.1 -c "INSERT INTO users_user VALUES (1, \'${ADMIN_NAME}\', \'${ADMIN_LNAME}\', \'${ADMIN_EMAIL}\', \'${ADMIN_PASSWORD}\', \'true\', \'zxc\')"

    Note that I have also quoted the other variable substitutions which is a best practice to avoid problems with whitespace and special characters.

Upvotes: 1

Related Questions