FAQ Database Discussion Community


Hibernate Criteria aPI Multiselect

java,hibernate,postgresql-9.2,java-ee-7,jpa-2.1
If I use Hibernate Criteria API like: CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(); Root<OneEntity> entityOneRoot = criteriaQuery.from(OneEntity.class); Root<TwoEntity> entityTwoRoot = criteriaQuery.from(TwoEntity.class); criteriaQuery.multiselect(OneEntity, TwoEntity); Do I need to use EQUAL-restriction (as ON-restriction in SQL) for equaling IDs between both tables (entities)? Because of Cartesian product of both tables?...

Why does a PostgreSQL SELECT query return different results when a schema name is specified?

postgresql,database-schema,postgresql-9.2,database-permissions,search-path
I have a PostgreSQL database table with 4 columns - labeled column_a, column_b, etc. I want to query this table with a simple select query: select * from table_name; I get a handful of results looking like: column_a | column_b ---------+--------- 'a value'|'b_value' But when I use this query: select...

Grant an automatic select to a all tables / PostgreSQL

sql,postgresql,postgresql-9.2
I have a production database, where the user can create a set of tables that inherits a master table X. The matter is when i want to apply an automatic grant for all the tables; Naturally with this command : GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only...

How to display field if entry exists in join table?

sql,postgresql,postgresql-9.2
I'm writing a query that display data from our issues table. We have another table called labels and a join table called issues_labels. We usually assign an issue a label of 'High-Priority', 'Medium-Priority' or 'Low-Priority. I'm unsure how to write my query so it would return this result: Id |...

how to add column value in postgresql?

sql,postgresql,postgresql-9.2
My running query is select to_char(fs.order_item_id ,'99999999999')as Order_Item_Id , (case when (sum(fs.shipping_fee) < 0) then (-sum(fs.shipping_fee))else sum(fs.shipping_fee) END) as Shipping_Fee_Charged , (case when (sum(se.shipping_fee) < 0) then (-sum(se.shipping_fee)) else sum(se.shipping_fee) END) as Standard_Shipping_Charges , (case when (sum(fs.shipping_fee - se.shipping_fee) < 0) then (- sum(fs.shipping_fee - se.shipping_fee)) else sum(fs.shipping_fee - se.shipping_fee)...

How to use date format in postgresql?

postgresql,postgresql-9.2
I am using the following query: SELECT distinct to_char(order_date, 'Mon') as mon from meta.ship_error order by to_char(order_date, 'Mon') asc and the output is: "Apr" "Aug" "Dec" "Feb" "Jan" "Jun" "Mar" "May" "Nov" "Oct" "Sep" "" but I want output like: jan feb mar apr .... What changes should I do...

Between doesn't yield expected results in pgsql in function

postgresql,postgresql-9.2,psql,between
I have the following sequence in pgsql in PostgresSQL 9.2.10. The SELECT statement works as expected. foreman=> SELECT 'architectures', id, created_at FROM architectures WHERE created_at BETWEEN '2015-05-04 12:03:38.234225' AND now() ; ?column? | id | created_at ----------+----+------------ (0 rows) The following is the function definition: foreman=> CREATE OR REPLACE FUNCTION...

Keep sequence created from BIGSERIAL when deleting table

postgresql,sequence,auto-increment,postgresql-9.2
I have a postgres table creating with the following SQL: CREATE TABLE mytable ( mytable_id BIGSERIAL NOT NULL, mytable_char VARCHAR(8) NOT NULL ) This creates the table as well as an implicit mytable_mytable_id_seq sequence. Now, after creating 1.000.000 records, I want to split this table into partitioned tables (using inheritance)....

SQL CTE Syntax to DELETE / INSERT rows

sql,postgresql,common-table-expression,postgresql-9.2
What's the CTE syntax to delete from a table, then insert to the same table and return the values of the insert? Operating on 2 hours of sleep and something doesn't look right (besides the fact that this won't execute): WITH delete_rows AS ( DELETE FROM <some_table> WHERE id =...

PostgreSQL query failure

sql,postgresql,postgresql-9.2,postgresql-9.3
I have a unique problem with PostgreSQL. After inserting data into a database I try and retrieve everything greater than a specific string. However, it does not return any data. So, I tried this on another machine and it worked. So my problem is that my data is returned on...

Postgres 9.2 find, compare and replace two values in column

sql,sql-update,postgresql-9.2
Hi i am trying to run a query to find two values in the same column, compare them and set the highest value to both and I am get a bit confused. I can not work out how to do it prettily so I have gone for an ugly approach...

PostgreSQL 9.4, 64-bit windows: Unable to create user from commandline

postgresql,postgresql-9.1,postgresql-9.2,postgresql-9.3
I'm trying to create a user from command line using PostgreSQL version 9.4 on a 64 bit machine with windows. I'm using the below command to create a new user: createuser -d temba On executing the above command, it prompts me for a password. I enter the password (qwerty) which...

How do I return json in a psycopg2 query?

python,json,postgresql-9.2,psycopg2
I am using psycopg2 (version 2.5.4) to query a PostgreSQL database (version 9.2.7). One of the columns I'm querying is a json type which psycopg2 is documented as being able to handle. However, I'm receiving the following error: psycopg2.ProgrammingError: could not identify an equality operator for type json I'm not...

how to join more then two table in postgresql?

sql,postgresql,postgresql-9.2,odoo,pgadmin
i am running following query select to_char(sale_order.date_order ,'DD-MM-YYYY') , sum(sale_order.amount_total) as amount from public.sale_order where sale_order.order_year = '2015' and sale_order.order_month = 'April' group by to_char(sale_order.date_order ,'DD-MM-YYYY') order by to_char(sale_order.date_order ,'DD-MM-YYYY') asc its giving correct output to_char.. amount "14-04-2015"; 1298.00 "15-04-2015"; 4294.00 "16-04-2015"; 1398.00 "17-04-2015"; 1927.00 "18-04-2015"; 3094.00 "19-04-2015"; 6988.00 "20-04-2015";...