Query JPA (@OneToMany) without attaching child table

I'm writing a service with JPA and Postgres db. I have a class named Student: public class Student { @id private String id; private String firstName; private String lastName; @OneToMany(targetEntity = Phone.class, fetch = FetchType.LAZY, cascade = Ca

SQL - Number of occurrences of distinct values ​​in column

To make long story short: I have table like this nr sat col 1 1 dsag 1 2 gds 1 2 gds 2 1 gdsa 2 2 gf 2 3 gdsa And expect the result like this: nr Sat_1 Sat_2 Sat_3 1 1 2 0 2 1 1 1 I want new column for each row from "SELECT DISTINCT sat" and nex

Printing a custom message in Postgresql

I have an sql command in Posgresql: insert into table1(a, b, c) select (d, e, f) from table2; It's just a single command, not a procedure. Is there any way to print out some information to stdout after an operation on each row has been performed? If

Removing the time part of a timestamp column

I am in need of experts' help here. I have a column "Actual_Date" of data type timestamp. It is in the format: yyyy - mm - dd 00:00:00 I want to convert the column into the format: mm - dd - yyyy How can I convert the date?You just need to forma

Change the ActiveRecord state of the default Rails cache

I have a suspicion that my app is having a memory leak, I read about that rails is automatically managing cache of the last 1,000 query execution. Almost every query in my app is unique and pretty heavy and contains many rows returned. So Is there a

Error trying to access Postgresql via java / spring

Am getting the error while trying to communicate with the PostgreSQL. The error I am getting is Could not load requested class : org.postgresql.Driver Another error I get is java.sql.SQLException: Unable to load class: org.postgresql.Driver from Clas

How to avoid DRY in PostgreSQL with a nested query

This query works fine in this way: SELECT DISTINCT inv.move_id, inv.client_id, inv.reference, inv.date_inv, inv.amount, inv.state, inv.client_type, inv.agent_id, ( SELECT SUM(nc.amount) AS total FROM invoice nc WHERE nc.journal = 15 AND nc.ref_move =

How do I insert into a JSON Postgresql array

The table definition is: chat_id serial primary key, last_update timestamp, messages JSON[] and I want to insert a record like this: insert into chats (messages) values ('{{"sender":"pablo","body":"they are on to us"

Create & ldquo; counter & rdquo; array in psql

I have a table in postgresql-9.4.2 with an array column. create schema test_schema; create table test_schema.test_table; ( array_column int[] ); insert into test_schema.test_table values (ARRAY[5,12,6,2]), (ARRAY[51,4,2]), (ARRAY[2]), (ARRAY[3,16]);

Install the extension in the PostgreSQL database via migration?

We are using migrations (via Sequelize, in JavaScript) to maintain changes to our database. I have a need to add a CREATE EXTENSION call but since I am running as the database creator, and not superuser, I get a permission denied to create extension.

Merge daily hourly price history

Database looks like: ID | volume | timestamp (timestamp without time zone) 1 | 300 | 2015-05-27 00: 1 | 250 | 2015-05-28 00: 2 | 13 | 2015-05-25 00: 1 | 500 | 2015-06-28 22: 1 | 100 | 2015-06-28 23: 2 | 11 | 2015-06-28 21: 2 | 15 | 2015-06-28 23: Is

In psycopg2, how to use register_hstore with connection pools?

I use psycopg2 in many python projects, and frequently use HSTORE as a postgres data type. Typically I create my connections as follows: connection =psycopg2.connect(...) psycopg2.extras.register_hstore(connection) which then allows seamless integrat

3 SQL queries

I have three tables: niveis(id, descricao) formacoes(id, id_nivel, nome) formacoes_professores(id, id_professor, id_formacao) I'd like to run a query that returns the education levels(niveis) with the number of professors that has it. I'm running thi

Why can not I connect to a Postgres database?

I've installed and started a Postgres 9.1 instance on a DigitalOcean droplet. When I try to connect to it using my PgAdmin III client (settings see below), I get the message that the server is not listening at the specified port. The output netstat -

PostgreSQL - INSERT INTO statement

What I'm trying to do is select various rows from a certain table and insert them right back into the same table. My problem is that I keep running into the whole "duplicate PK" error - is there a way to skip the PK field when executing an INSER

O WH NOT EXISTS in PostgreSQL ™ gives a syntax error

When trying to use the WHERE NOT EXISTS clause to prevent adding a row with a duplicate value in the column age, I get the error syntax error at or near "WHERE". Why did it throw a syntax error? I'm using Postgresql 9.1. SQL INSERT INTO live.use

Recursive Insertion in the Postgresql Clause

I want to perform a big insert statement on multiple lines, but recursion is making it hard to build the correct SQL statement. I believe an example will make it easier to explain. Consider the model: car |id|code|Model name | |1 |100 |Deluxe | |10|1

Command Postgres UTF8

I have this query in Postgres where I'm ordering a small amount of rows according to a varchar field. There seems to be an error in ordering UTF8 strings in Postgres: For example: 'W' in UTF-8 is 87, while 'g' is 103, but running SELECT 'W' < 'g'; wi

Heroku ActiveRecord Error: PgSQL sets null in id field

I just deployed a simple Ruby on Rails (3.0.10) application to Heroku. There is a line of code create a new User object like this. User.create(:name => "[name here]", :email => "[email here]") It can run well in my local machine,

save pdf in postgres

Is it possible to save pdf in postgres database? If so, how?You can use the datatype bytea to store the pdf. Another option could be a large object, but that's not the easiest thing to use.

High-quality ERD generator for PostgresQL on Linux?

Background MySQL Workbench can produce appealing and high-quality ERDs such as: Research What PostgreSQL ERD tools are available that meet the requirements? The following are unsuitable: dbVisualizer - Yellow squares. AquaFold - Yellow squares. SQL D