Tag Archives: postgres

Jan 02 2020
IT Stuff

Import SQL file using pgAdmin

I have a docker Postgres image and I want to import the data from another Postgres db. The first thing I have done is to create a pg_dump on the remote server and then I have tried to import it. The problem is that output generated is simple SQL file and, if I import this file on pgAdmin I get an error:

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

psql is not installed on my Mac because I am running it as a docker image and the file exported is using COPY to import values instead of INSERT.
The solution I have found is to export the db using –column-inserts flag:

$ pg_dump --column-inserts -U user db_test > db_test.2020-01-02_insert.sql

–column-inserts will dump as insert commands with column names.

Tagged
May 04 2012
Leave a comment
IT Stuff

Quit from psql command-line utility

To quit from Postgres psql command-line utility use:

health_coach=# \q

and press enter

Tagged
Feb 03 2012
Leave a comment
IT Stuff

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());
Tagged , ,
Jan 18 2012
Leave a comment
IT Stuff

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
Tagged ,