Java Solutions: PostgreSQL

Showing posts with label PostgreSQL. Show all posts

Wednesday, July 1, 2020

PostgreSQL: add an interval of days represented by a variable to a date

We know that if you want to add 4 days to a date, you do the following.

<your date> + interval '4 days'

However, if you have

int x = 4;

It will not work by:

<your date> + interval 'x days'

The way to make it work is:

<your date> + x * (interval '1 day')

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Tuesday, June 30, 2020

PostgreSQL: ROUND makes the value 0.5 returned from a calculation to 0 instead of 1 - Solved

In PostgreSQL, things can look odd as the following queries show.

postgres=> select round(3/cast(6 as float));
round
-------
0

postgres=> select 3/cast(6 as float);
?column?
----------
0.5

postgres=> select round(0.5);
round
-------
1

To make round(3/cast(6 as float)) to return 1 instead of 0, you need to put another layer of parenthesis to it.

postgres=> select round((3/cast(6 as numeric)));
round
-------
1

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Tuesday, June 2, 2020

PostgreSQL: Get the TimeStamp of the beginning of a year, a month, or a day

To get the timestamp of the beginning of a year:

//current year
select date_trunc('YEAR', CURRENT_TIMESTAMP);

//any year
select date_trunc('YEAR', CURRENT_TIMESTAMP - INTERVAL '4 year'); or
select date_trunc('YEAR', CURRENT_TIMESTAMP + INTERVAL '3 year'); or
select TIMESTAMP '2019-01-01';

To get the timestamp of the beginning of a month:

//current month
select date_trunc('MONTH', CURRENT_TIMESTAMP);

//any month
select date_trunc('MONTH', CURRENT_TIMESTAMP - INTERVAL '9 month'); or
date_trunc('MONTH', CURRENT_TIMESTAMP + INTERVAL '2 month'); or
select TIMESTAMP '2019-08-01';

To get the timestamp of the beginning of a day:

//current day
select date_trunc('DAY', CURRENT_TIMESTAMP);

//last day of the month
select date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 month - 1 day';

//last day of the month of a given date
  select date_trunc('MONTH', TIMESTAMP '2018-09-01) + INTERVAL '1 month - 1 day';

//last day of the year
  select date_trunc('YEAR', CURRENT_DATE) + INTERVAL '1 year - 1 day';

//last day of the year of a given date
  select date_trunc('YEAR', TIMESTAMP '2020-05-03') + INTERVAL '1 year - 1 day';

//any day
select date_trunc('DAY', CURRENT_TIMESTAMP - INTERVAL '12 day'); or
select date_trunc('DAY', CURRENT_TIMESTAMP + INTERVAL '22 day'); or
select TIMESTAMP '2019-08-25'; or
select date_trunc('DAY', CURRENT_TIMESTAMP + INTERVAL '2 month + 5 day');
select date_trunc('DAY', CURRENT_TIMESTAMP - INTERVAL '3 year - 2 month + 12 day');

Be aware when you minus an INTERVAL, the "- INTERVAL '3 year - 2 month - 12 day'" reduces three years from the current_timestamp, adds 2 months to it, and minuses twelve days from it.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

PostgreSQL: Get the TimeStamp of today, yesterday, tomorrow, and a number of days ago or later

To get the current timestamp:

select current_timestamp;
or
select now();

To get the timestamp of the beginning of today:

select current_date::timestamp;
or
select 'today'::timestamp;

To get the timestamps of yesterday and tomorrow:

select 'yesterday':timestamp, 'tomorrow'::timestamp;
or
select (current_date - INTERVAL '1 day')::timestamp, (now0 + INTERVAL '1 day')::timestamp;

To get the timestamps of n days ago:

select (current_date - INTERVAL 'n day')::timestamp;

To get the timestamp of m days later:

select (current_date + INTERVAL 'm day')::timestamp;
select (current_date + INTERVAL '1 month - 3 day')::timestamp

Also, see PostgreSQL: Get the TimeStamp of the beginning of a year, a month, or a day

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Wednesday, May 13, 2020

PostgreSQL: Monitoring the status of your queries currently running in the database

The pg_stat_activity view has one row per server process, showing information related to the current activity of that process.

pg_stat_activity view

Column	Type	Description
`datid`	`oid`	OID of the database this backend is connected to
`datname`	`name`	Name of the database this backend is connected to
`pid`	`integer`	Process ID of this backend
`usesysid`	`oid`	OID of the user logged into this backend
`usename`	`name`	Name of the user logged into this backend
`application_name`	`text`	Name of the application that is connected to this backend
`client_addr`	`inet`	IP address of the client connected to this backend. If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum.
`client_hostname`	`text`	Host name of the connected client, as reported by a reverse DNS lookup of `client_addr`. This field will only be non-null for IP connections, and only when log_hostname is enabled.
`client_port`	`integer`	TCP port number that the client is using for communication with this backend, or `-1` if a Unix socket is used
`backend_start`	`timestamp with time zone`	Time when this process was started, i.e., when the client connected to the server
`xact_start`	`timestamp with time zone`	Time when this process' current transaction was started, or null if no transaction is active. If the current query is the first of its transaction, this column is equal to the `query_start` column.
`query_start`	`timestamp with time zone`	Time when the currently active query was started, or if `state` is not `active`, when the last query was started
`state_change`	`timestamp with time zone`	Time when the `state` was last changed
`waiting`	`boolean`	True if this backend is currently waiting on a lock
`state`	`text`	Current overall state of this backend. Possible values are: `active`: The backend is executing a query. `idle`: The backend is waiting for a new client command. `idle in transaction`: The backend is in a transaction, but is not currently executing a query. `idle in transaction (aborted)`: This state is similar to `idle in transaction`, except one of the statements in the transaction caused an error. `fastpath function call`: The backend is executing a fast-path function. `disabled`: This state is reported if track_activities is disabled in this backend.
`query`	`text`	Text of this backend's most recent query. If `state` is `active` this field shows the currently executing query. In all other states, it shows the last query that was executed.

For example, you can use the following query to see the state, text, and running period of your statement.

SELECT pid, usename, query, state, age(clock_timestamp(), query_start)
FROM pg_stat_activity
WHERE usename = '<your database login>';

If you want to see if your query is holding or waiting on any locks, you can run the query below.

SELECT a.pid, a.state, L.mode, L.locktype, L.granted
FROM pg_stat_activity a
INNER JOIN pg_locks L on a.pid = L.pid
WHERE a.usename = '<your database login>'
order by L.granted desc;

References:

1. Monitoring Database Activity

2.Useful PostgreSQL Queries For Monitoring Purpose

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Thursday, April 9, 2020

org.postgresql.util.PSQLException: Connection refused

You ran your application to connect to your Postgresql database and received the following exception.

org.postgresql.util.PSQLException: Connection to 129.178.32.11:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:285)

After you have ensured that the hostname and port are correct and the exception remains you need to modify the postgresql.conf file.

Uncomment and change listen_address='localhost' to listen_address='*'.

Then check the pg_hba.conf file to ensure your host is allowed to connect to the database by changing IPv4 and IPv6 local connections to: host all all all trust

And restart your Postgresql database.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Wednesday, July 10, 2019

Solved - org.postgresql.util.PSQLException: ERROR: could not set permissions on directory

I had the following error when I was using my postgresql database.

org.postgresql.util.PSQLException: ERROR: could not set permissions on directory "/var/lib/pgsql/9.5/tablespace/postgresql": Operation not permitted
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2023)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:421)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318)
at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:294)

It turned out that no matter which user is logged in the posgresql database, the database server uses the posgres to set the permission on directories when creating table space.

User postgres should be the owner of the directory that you set for the table space.

----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Solved - ClassNotFoundException: org.postgresql.util.PSQLException

When I was launching my program, I got the following error.

Exception in thread "main" java.lang.NoClassDefFoundError: org/postgresql/util/PSQLException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2615)
at java.lang.Class.getMethod0(Class.java:2856)
at java.lang.Class.getMethod(Class.java:1668)
at sun.launcher.LauncherHelper.getMainMethod(LauncherHelper.java:494)
at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:486)
Caused by: java.lang.ClassNotFoundException: org.postgresql.util.PSQLException
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)

It was fixed by adding the database driver (postgresql-9.4.1209.jre7.jar) to the execution path.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Friday, December 16, 2016

SQL: UPSERT syntax and examples

UPSERT is part of the data manipulation language (DML) that allows one to use a single statement to atomically either INSERT a row, or on the basis of the row already existing, UPDATE that row instead, while safely giving little to no further thought to concurrency. The "essential property of UPSERT" is that one of those two outcomes must be guaranteed, regardless of concurrent activity.

PostgreSQL:

Starting PostgreSQL 9.5, UPSERT becomes part of its DML.

Use the ON CONFLICT clause:

For example, you have a store table and you want to set the store name to 'Unforgettable Fruits' where the store id is 559. You can use the following SQL to guarantee the result regardless if store 559 already exists.

INSERT INTO store (id, name) values (559, 'Unforgettable Fruits')
ON CONFLICT (id)
DO UPDATE SET name = 'Unforgettable Fruits';

Use the WITH clause:

Syntax:

WITH upsert AS
(UPDATE <table name>
SET <column name> = <value>
WHERE <condition> RETURNING *)
INSERT into <table name> (<columns separated by comma>)
SELECT <values separated by comma>
WHERE not exists (SELECT * from upsert);

With the above store example:

WITH upsert AS
(update store set name = 'Unforgettable Fruits' where id=559 returning *) insert into store (id, name) select 559, 'Unforgettable Fruits' where not exists (select * from upsert);

Oracle:

The above SQL starting with 'WITH upsert AS' does not work in Oracle. However Oracle can use the MERGE clause to do the same job. MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard.

Syntax:

MERGE into <table name>
USING
<source table/view/result of sub-query>
ON
<match condition>
WHEN MATCHED THEN
<update clause>
<delete clause>
WHEN NOT MATCHED THEN
<insert clause>

Using the above store example.

merge into store using (select id, name from store_temp b) on (a.id = b.id) when matched then update set a.name = b.name when not matched then insert (a.id, a.name) values (b.id, b.name);

The MERGE clause is also supported in DB2 and MS SQL.

MySQL:

The ON DUPLICATE KEY UPDATE clause: Checks if a primary key or unique index is violated by an insert, it will then do an update.

Syntax:

INSERT into <table name> (<columns separated by comma>) values (<values separated by comma>)
ON DUPLICATE KEY <update clause>

Using the above store example, which has the id as the primary key:

INSERT into store (id, name) values (559, 'Unforgettable Fruits')
ON DUPLICATE KEY UPDATE name='Unforgettable Fruits';

Of course, you can always do a select first to check if the record exists, then decide if you need to update or insert. You can also do an update first, if it returns zero, then insert.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Syntax:

Friday, November 11, 2016

Solved - PostgreSQL: add days or hours to date

PostgreSQL does not have the DateAdd function. It uses the INTERVAL type to add days and hours to a date.

1. Add days to a date.

select <the date> + cast(<the days> || ' days' as INTERVAL) AS nDate from <your table>;
Or
select <the date> + (<the days> * interval '1 day') AS nDate from <your table>;

2. Add hours to a date

select <the date> + cast(<the hours> || ' hours' as INTERVAL) AS nDate from <your table>;
Or
select <the date> + (<the hours> * interval '1 hour') AS nDate from <your table>;

---------------------------------------------------------------------------------------------------

Thursday, October 6, 2016

postgresql alter table hangs

When the SQL, "Alter table Fruit add Column Location VarChar(30))", is executed at the psql terminal, it hangs.

It turns out that there is a lock on a table. The SQL below helps find all the locks in your database.

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Then use the SQL below to kill the blocking process.

select pg_terminate_backend(<blocking_pid>);

Reference:

Lock Monitoring

---------------------------------------------------------------------------------------------------

Wednesday, September 28, 2016

[Solved] Use a different port or change the port of postgresql-9.5 on CentOS 6

1. Modify the /var/lib/pgsql/9.5/data/postgresql.conf.

  Uncomment and change listen_addresses = 'localhost' to listen_addresses = '*'.
  Uncomment and change port=5432 to post=<your port>

2. Create or modify the /etc/sysconfig/pgsql/postgresql-9.5 file.

Add the following to the file.
  PGPORT=<your port>

export PGPORT

3. Start the database server.

su - postgres

/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data -l logfile start

4. Launch the psql prompt

psql -p <your port>

---------------------------------------------------------------------------------------------------

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The system used here is PostgreSQL 9.5 on CentOS 6.6.

As the postgres user, you type psql and it gives you such an error.

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

First, check if your database server is running with the following commands

  ps -ef | grep postgres

Which would return something like the following if the server is running.

root 6616 4849 0 14:13 pts/2 00:00:00 su - postgres
postgres 6617 6616 0 14:13 pts/2 00:00:00 -bash
postgres 7219 1 0 15:42 pts/2 00:00:00 /usr/pgsql-9.5/bin/postgres
postgres 7220 7219 0 15:42 ? 00:00:00 postgres: logger process
postgres 7222 7219 0 15:42 ? 00:00:00 postgres: checkpointer process
postgres 7223 7219 0 15:42 ? 00:00:00 postgres: writer process
postgres 7224 7219 0 15:42 ? 00:00:00 postgres: wal writer process
postgres 7225 7219 0 15:42 ? 00:00:00 postgres: autovacuum launcher process
postgres 7226 7219 0 15:42 ? 00:00:00 postgres: stats collector process
postgres 7239 6617 0 15:44 pts/2 00:00:00 ps -ef
postgres 7240 6617 0 15:44 pts/2 00:00:00 grep postgres

If it is not running, use the command below to start it.

  /usr/pgsql-9.5/bin/pg_ctl start

If you are using a different port other than the default 5432, use this command to start psql.

psql -p <port>

---------------------------------------------------------------------------------------------------

Thursday, May 19, 2016

ERROR JRCCommunicationAdapter - detected an exception: Logon Error: FATAL: role "user" does not exist

I had this error when were running Crystal reports after we switched from using Oracle to PostgreSQL. Below is the original code.

ReportClientDocument reportClientDoc = new ReportClientDocument();
reportClientDoc.open(<reportName.rpt>, 0);

Tables tables = databaseController.getDatabase().getTables();
for (int i = 0; i < tables.size(); i++) {
  ITable table = tables.getTable(i);
  IConnectionInfo connectionInfo = table.getConnectionInfo();

  PropertyBag propertyBag = new PropertyBag();

  propertyBag.put("URI", "!oracle.jdbc.OracleDriver!jdbc:oracle:thin:{userid}/{password}@" + host + ":" + port + ":" + databaseName);

//propertyBag.put("Use JDBC", "true");
  propertyBag.put("Database DLL", "crdb_jdbc.dll");

  connectionInfo.setAttributes(propertyBag);
connectionInfo.setUserName(<database userName>);
connectionInfo.setPassword(<database password>);

  table.setConnectionInfo(connectionInfo);

//Throws error here.It somehow does not use the username and password
//set in the connectionInfo to logon. Instead, it uses the application login user id.
  databaseController.setTableLocation(table, tables.getTable(i));
}

To fix this, use the code below to replace the code above in blue background.

StringBuilder url = new StringBuilder();
url.append("jdbc:postgresql://").append(host).append(":")
.append(port).append("/").append(databaseName);

StringBuilder uri = new StringBuilder();
uri.append("!org.postgresql.Driver!").append(url.toString())
.append("!user={userid}!password={password}");
propertyBag.put("JDBC Connection String", uri.toString());
propertyBag.put("Connection URL", url.toString());
propertyBag.put("Database Classname", "org.postgresql.Driver");
propertyBag.put("Database", databaseName);
propertyBag.put("Database Type", "JDBC (JNDI)");
propertyBag.put("JDBC Connection", "True");
propertyBag.put("Server", "");
propertyBag.put("User ID", userName);
propertyBag.put("Password", password);

----------------------------------------------------------------------------------------