Create postgresql database entry using bash script

Question

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?

There's a way to know: does removing all the backslashes help? — bereal
– bereal, Commented Mar 6, 2015 at 10:35

Aaron · Accepted Answer · 2015-03-06 15:21:10Z

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

Michael Jaros · Accepted Answer · 2015-03-06 10:09:28Z

1

$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.

edited Mar 6, 2015 at 10:09

answered Mar 6, 2015 at 10:03

Michael Jaros

4,6991 gold badge27 silver badges41 bronze badges

Comments

Pavel Stehule · Accepted Answer · 2015-03-06 11:53:40Z

1

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"

answered Mar 6, 2015 at 11:53

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Collectives™ on Stack Overflow

Create postgresql database entry using bash script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related