Andrea Girardi - It's my blog!

Tag: postgres

Quit from psql command-line utility

To quit from Postgres psql command-line utility use:

health_coach=# \q

and press enter

Use of LIKE clause in sql prepared statement

Suppose you have a where condition with like clause:

AND ( UPPER(C.ndg) LIKE UPPER('%test%') OR UPPER(C.ndg_name) LIKE UPPER('%test%') )

on Java you should write something like that:

AND ( UPPER(C.ndg) LIKE UPPER('%?%') OR UPPER(C.ndg_name) LIKE UPPER('%?%') )

but, when you fill the paramter with SimpleJdbcTemplate(), this exception will be raised:

PreparedStatementCallback; SQL []; The column index is out of range: 1, number of columns: 0.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

This happens because with prepared statement you need to remove the ‘ but, now, how can I write the condition with ‘%’ char?

Simple, you need to sorround the paramter with ‘%’ like that:

sqlString += " and ( UPPER(C.ndg) like UPPER(?) or UPPER(C.ndg_name) like UPPER(?) ) ";
getJdbcTemplate().query(sqlString, new Object[] {"%" + searchForm.getNdg() + "%", "%" + searchForm.getNdg() + "%"}, new ViewCGRowMapper());

Run Postgres 9.1 on MacOS Lion as Daemon

To start a new Postgres version as Daemon you need to follow these commands. For first, unload current version (if available):

sudo launchctl unload /Library/LaunchDaemons/org.postgresql.dbms.plist

Edit the org.postgresql.dbms.plist (if not available, create a new one):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" 
<plist version="1.0">

Load new configuration:

sudo launchctl load /Library/LaunchDaemons/org.postgresql.dbms.plist

Before reboot your Mac you need to check if the folder data has the right permission:

sudo su - postgres
chmod 700 /Library/PostgreSQL/9.1/data

Reboot your Mac.

To manually start (and stop Postgres on MacOS), login as postgres and:

pg_ctl -D /Library/PostgreSQL/9.1/data -l logfile start
pg_ctl -D /Library/PostgreSQL/9.1/data -l logfile stop

Copyright © 2017 Andrea Girardi – It's my blog!

Theme by Anders NorenUp ↑