# updating a database
```
#for dev databases
psql -U postgres -c "create database clone with template lobomj"
#todo: https://serverfault.com/q/627169
#OR from production to local https://dba.stackexchange.com/q/82161
dropdb -U postgres clone
createdb -U postgres clone
pg_dump "port=<port> host=<host> user=<user> dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-full" -d lobomj -Fc | pg_restore -U postgres -d clone
psql -U postgres -f initialize.sql -v name=diffme
pg_dump -U postgres clone --schema-only -f clone.sql
pg_dump -U postgres diffme --schema-only -f diffme.sql
#diff with your favourite tool
#write queries to alter database to update.sql
psql -U postgres -d clone -f update.sql
#run tests
#update dev
psql -U postgres -d lobomj -f update.sql
#OR propagate local changes to production
psql "port=<port> host=<host> user=<user> dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-full" -d lobomj -f update.sql
dropdb -U postgres clone
dropdb -U postgres diffme
```

drop database lobomj;
create database lobomj;
\c lobomj

--https://stackoverflow.com/q/32582600/
\set name :name
select case
	when :'name' = ':name' then 'robomj'
	else :'name'
end as "name" \gset
drop database :name;
create database :name;
\c :name