Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact version of Postgres when asking questions. Questions concerning administration or advanced features are best ...
0
votes
0answers
12 views
How to query postgres dict object in a list within a nested json using sqlalchemy?
I would like to write a sqlalchemy expression to query the items within "w" from the following nested json:
{"transcript": [
{"duration": 2390.0,
"interval": [140.0, 2530.0],
"speaker": ...
0
votes
0answers
8 views
Apply same query on different data, output is not as expected
I have two tables, N and G. Both tables have similar structures, N has much more records than G has. I applied same query on this two tables separately and explain analyze returned different results: ...
0
votes
0answers
7 views
Postgres, Npgsql and Integration tests using rollbacks
In our SQL Server integration tests, we wrap tests in a TransactionScope which we then rollback after each test to keep the database in a consistent state.
Using Postgres (or possibly specifically ...
0
votes
0answers
7 views
Rails5, Sqlite3 vs PostgreSQL
I was implementing Carrierwave with possibility to upload multiple files, so according to this source Add more files and remove single file Carrierwave tutorial I had to create a migration to add ...
1
vote
0answers
15 views
Why are Postgres lookups on jsonb columns so slow?
I have a table targeting that has a column marital_status of type text[] and another column data of type jsonb. The content of these two columns is the same, just in a different format (it's just for ...
0
votes
0answers
10 views
How fill the vias_path field for restriction table
In documentation explain the restriction table for pgr_trsp function, and that way I can restrict left turn or right turn {target_id, from_edge}. But if want restrict multiple links dont explain how ...
1
vote
0answers
10 views
Postgres describe of aggregate function returning type modification value of -1 for user defined type
I've created an aggregate function with the following:
CREATE FUNCTION rtrim(mychar) RETURNS mychar
AS '$libdir/libmy_pgmod', 'mycharrtrim'
LANGUAGE C IMMUTABLE STRICT;
CREATE ...
0
votes
1answer
17 views
ERROR: relation “author” does not exist SQL state: 42P01
I am a starter in Postgresql and i am trying to create a simple function with inner join inside it
CREATE OR REPLACE FUNCTION GetBooks()
RETURNS TABLE( Id int, Title text, AuthorName text) as
$BODY$...
0
votes
2answers
17 views
Can I get Ecto to log raw SQL?
I am building an Ecto query like this:
from item in query,
where: like(item.description, ^"%#{text}%")
I'm concerned that this allows SQL injection in text. Before trying to fix that, I want to see ...
0
votes
0answers
13 views
Postgresql too slow spatial intersection analyse
I have a postgres database with a table (approx. 150 000 rows) with three columns; row_id, position and number of flowers counted. Since the position is rather close to each other might there be some ...
0
votes
2answers
23 views
Kill postgre connection in rails console
How can I kill all postgre connections using rails console? I am getting this:
PG::ConnectionBad
FATAL: sorry, too many clients already FATAL: sorry, too many clients already
Rails.root: C:/Users/...
0
votes
0answers
8 views
Use Regex from a column in Redshift
I have 2 tables in Redshift, one of them has a column containing Regex strings. And I want to join them like so:
select *
from one o
join two t
on o.value ~ t.regex
But this query throws an error:
[...
0
votes
0answers
11 views
psql: could not receive data from server: Software caused connection abort (0x00002745/10053)
I installed Postgresql on windows 10 and when trying to connect I get this error.
psql: could not receive data from server: Software caused connection abort (0x00002745/10053)
Anyone know ...
0
votes
1answer
15 views
How to alter table in Postgres to show Timestamp with Time Zone abbreviation (2004-10-19 10:23:54 EST)
I want to alter my table row from 2011-06-30 05:59:59+00 format into 2011-06-30 05:59:59 CDT format
0
votes
1answer
14 views
How to create table with datetime in its name using postgres sql
Is there a way to create a table with date time on it's name purely using postgres sql.
Consider the typical create table:
CREATE TABLE someNewTable AS SELECT .... FROM someSourceTable WHERE date &...
0
votes
1answer
7 views
Adding column with foreign key into table.
I have some problem. I want to add new column into my table that references to other column in other table. I do something like that:
class ...
0
votes
1answer
12 views
Check Sum error in JHipster when editing an existing entity generated using the entity wizard
I am using Postgresql for my db and created all the entities using the JHipster entity wizard. When I try to make any changes like adding/removing fields, relations to an existing entity I am getting ...
0
votes
1answer
11 views
PostgreSQL advisory lock stays after user disconnect
I have a backend application that implements session level advisory lock. However, if the client suddenly loses the connection (e.g forcing reboot while connected) the advisory lock stays there and ...
0
votes
0answers
23 views
Best practice for tables in One-to-one relationships
What's the best practice for a scenario where, if you dont split data up into multiple tables, you'll end up with a table consisting of more than 100 columns? The data is of the one-to-one relation.
0
votes
0answers
22 views
Class “pg-uuid” Not Found Exception for Annotation @Type(type=“pg-uuid”)
I'm having some trouble trying to utilize the annotation @Type(type="pg-uuid"). It throws this exception:
Caused by: java.lang.ClassNotFoundException: Could not load requested class : pg-uuid
Full ...
0
votes
0answers
12 views
postgres to oracle replication
I am trying to replicate postgres database to oracle database and satisfy the flowing:
New data is transformed from postgres to oracle.
New columns, triggers, index... etc must be transformed.
...
0
votes
1answer
15 views
postgres json_agg() ignores index in joined tables in GROUP BY expressions
I have two tables (foreign keys are omitted for simplicity):
CREATE TABLE timetables (
"ttid" SERIAL4 NOT NULL,
"bioid" int4 NOT NULL,
"component" int4,
"route" int2,
"time_num" numeric,
"time_unit" ...
2
votes
3answers
40 views
SELECT * FROM t WHERE t.x OR t.y IN (SELECT id FROM z)
Is something like this possible (I know this statement is not working, I tried it):
SELECT * FROM t WHERE t.x OR t.y IN (SELECT id FROM z)
Example
Table t:
|id|x |y
|1 |101|201
|2 |102|202
...
0
votes
1answer
27 views
PSQLException: ERROR: could not identify an equality operator for type xml
select distinct ref
FROM Data d
inner join d.reference ref
WHERE d.role = 'admin'
ORDER BY ref.id
This JPA query fails with following error:
PSQLException: ERROR: could not identify an ...
0
votes
0answers
4 views
Openerp - Holiday yearly allocation
I need to allocate leaves on yearly based and take the leave as yearly base as well. I am using standard Leave Management Module and please let me know how to modify that.
Following is the source code ...
0
votes
1answer
10 views
postgresql partitioning by date from existing table
I need to partition an existing table which is main.customer by year. The situation I have is that, I have a column that contains a string with year in it and I want to extract the year part from that ...
0
votes
0answers
19 views
How to implement Push notifications
I need to implement push notification in my application, whenever a table gets updated by other user(updating some records through UI).
I have been googling from the morning,but I cant able to ...
0
votes
0answers
23 views
postgresql update column with least value of column from another table based on condition
I'm trying to run an update query on the column answer_date of a table P. I want to fill each row of answer_date of P with the unique date from create_date column of H where P.ID1 matches with H.ID1 ...
1
vote
1answer
13 views
Running different versions of postgresql side by side
I have postgresql 9.3 installed.
I would like to have also postgres 9.6.1 installed.
Each application is using a different DB. Most of the times I don't run both applications, so I don't need them to ...
0
votes
2answers
15 views
Creation of a procedure [POSTGRES]
How to create a procedure that asks for a user name and return the following select:
select application_name, client_hostname
from pg_stat_activity
where usename=p_nameuser;
Any ideas? Thank you.
0
votes
2answers
13 views
relation “old” does not exist
When i try execute this script, i get an error:
ERROR: relation "old" does not exist.
Script:
update organization
set row_status = 30;
I found some question like this, but their dont help me.
My ...
1
vote
2answers
26 views
Use date without time in database with ef
What class should I use to save a date (without time) with ef and asp.net core (using database postgres)? DateTimeOffset always tries to save a time.
public class something
{
[Column(TypeName=...
0
votes
1answer
19 views
CSV import from JAVA using postgres script
So I have this script that i need to execute from a Java web application.
The file containing this lines is located under WEB-INF
truncate table amm.mm_dosage_unit;
alter table amm....
1
vote
0answers
17 views
Compare value between Postgre Numeric with PHP variable
I have a problem.
I store a number to Postgre database table named item:
id | quantity
1 | 10.55
id is integer, and quantity is numeric
And now i have a PHP variable like this :
$quantity = 10.55;...
1
vote
1answer
9 views
Mismatch of values when using xml input in postgre sql
I want to create a xml format so that i can use it in the input of a stored procedure,but the result came from the select query is not the output i am expecting.Could you please help me with this?
...
0
votes
0answers
17 views
java CallableStatement failed, while calling function of postgreSQL enterprise edition
Case History:
I have upgraded postgreSQL normal to Enterprise edition
Old version:
PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
New ...
1
vote
2answers
42 views
PostgreSQL how to convert rows to colums
I have a table like this in my postgres BD:
CREATE TABLE foo(id,type,date,value) AS
SELECT *
FROM ( VALUES
(11::smallint,'A','2016-06-06 19:00:00'::timestamp,81),
(11,'A','2016-06-06 20:00:00',70)...
0
votes
0answers
22 views
Hibernate and multiple DB connections
Having following architecture:
So every server (GS) can talk to each database server (DB, PostgreSQL). The servers (GS) are running Spring Boot with Hibernate.
As far as I know Hibernate does a lot ...
-1
votes
0answers
6 views
Can I make reports on RDLC Report Designer for PostgreSQL?
I use Visual Studio 2015 + PostgreSQL v.9.5. I didn't found any documentation to how to do reports on Report Designer (Visual Studio, .rdlc files). Can I make reports on MS Report Designer for ...
0
votes
1answer
16 views
find using model association
Dialect: postgres
Database version: @latest
Sequelize version: @latest
I'm trying to find out how to use an associate model. I've got 3 models: post, postCity and region. They have the following ...
0
votes
0answers
19 views
Django Is using ManyToManyField more flexible and extendable than other Fields?
I know difference between three relation fields; OneToOneField, ForeignKeyField, ManyToManyField. I also know basic concept of index, primary key, foreign key in database level.
Well, I heard that ...
1
vote
2answers
21 views
psql query not returning proper results?
var searchValue = 'shahid';
var query = ("select * from students where name ilike '%"+searchValue+"%'");
This is my psql query, but it is not returning any values.So that i just console the query to ...
0
votes
1answer
18 views
Postgres PostGIS: st_intersects returns false but only for first record returned, even if hard coded
Using Postgres 9.2
I have a strange issue. To simplify it:
I have some data with line points. The query in question is using st_intersects to determine if the line points overlap with a polygon. ...
0
votes
3answers
33 views
SQL - conditionally select a column if exists
I need to select a column only if it exists in table, else it can be set to null.
Sample table below, lets say the marks col is not necessary be there, so need to be checked if it exists
Table1:
...
0
votes
0answers
14 views
PhpStorm saying PostgreSQL database does not exist?
I am able to connect to the protomolecule database with these exact same credentials through a PHP script. Trying to set it up in PhpStorm fails as you can see in the picture.
If I change the ...
phpstorm
0
votes
1answer
23 views
Converting PostgreSQL Subqueries into Joins
Below is an example schema with 3 tables. I'm trying to run a query that returns all Jobs where all child Shifts are of status 6. If a Job has a child Shift with a status of 5, the Job should not be ...
1
vote
2answers
17 views
how do I get PL PGSQL error code in a function?
I am writing a PL/PGSQL function and it produces an error. I want to print the error with RAISE NOTICE statement but I don't know how to get the error code? What variable holds the last error?
This ...
0
votes
0answers
14 views
Sequelize PostgreSQL: query to see if string is within an array
Given that I have a model called Model with a column called items which holds an array of strings, how can I query to see whether a string queryString is in the array or has a similar element in the ...
-1
votes
0answers
26 views
Need to change my query in to better one in postgres
I am using golang and postgreSQL version 9.5.5 in my application. I am using github.com/lib/pq as my database driver to connect to the database.I have following fields in the table: (id,employee_id, ...
0
votes
0answers
19 views
SQLAlchemy table relation does not exit
I am trying to query my Postgres database. Ultimately, I am trying to conver the following query into sqlalchemy:
SELECT DISTINCT id
FROM (
SELECT id, transcript_info->'words' AS info
FROM ...
