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" 
"http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Label</key>
        <string>org.postgresql.postgres</string>
        <key>ProgramArguments</key>
        <array>
                <string>/Library/PostgreSQL/9.1/bin/postmaster</string>
                <string>-D</string>
                <string>/Library/PostgreSQL/9.1/data</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>UserName</key>
        <string>_postgres</string>
</dict>
</plist>

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 ↑