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 ...
1
vote
1answer
29 views
SQLException: No suitable driver found for jdbc:postgresql
java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5432/database2
thrown by entityManager.getTransaction()
JBoss 7.1.1
Class.forName("org.postgresql.Driver");
...
0
votes
2answers
9 views
function result type must be integer because of OUT parameters in postgresql
In the below query when i execute it throws an error function result type must be integer because of OUT parameters.Pls anyone help me to overocme this issue.
CREATE OR REPLACE FUNCTION ...
0
votes
1answer
21 views
Sum all of the items in a JSONB array field
If my table is setup like:
indicators:
id: 56789,
funding (JSONB): [
{
amount: 345678
},
{
amount: 7899
}
]
I can successfully sum the first amounts on every ...
0
votes
1answer
13 views
Hibernate Filter being ignored
My application uses Hibernate 5.02 and Wildfly 10 with a PostgreSQL 9.5 database. I'm trying to enable a filter on a @OneToMany collection held within an entity that is constructed via a NamedQuery. ...
0
votes
0answers
3 views
ERROR: no PostgreSQL user name specified in startup packet
I'm rocking a Phoenix app, and when I try to run mix ecto.migrate, I encounter the following error:
GenServer #PID<0.181.0> terminating
** (Postgrex.Error) FATAL (...
0
votes
1answer
21 views
Postgresql - INSERT RETURNING INTO ambiguous column reference
Can someone politely explain this crazyness?
INSERT INTO "dbo"."UserProfile" ("FirstName")
VALUES('John')
RETURNING "UserProfileId" INTO _UserProfileId;
throws an ambiguous reference error, however ...
5
votes
1answer
182 views
ActiveRecord::QueryCache#call taking over 70% of execution time
NewRelic is showing me that over 80% of execution time in the app server is taking place in "Middleware ActiveRecord::QueryCache#call"
Here is a gist of the relevant code tested (although I see ...
0
votes
0answers
6 views
How to use parameter in postgresql
Use in xml this query
searchCategory1="
SELECT "$1"
FROM "$2"
GROUP BY "$1"
ORDER BY "$1""
but error message
ERROR: relation ...
0
votes
2answers
11 views
Preloading associations for a model with queries
So, let's say I have a Users class, and I fetch an instance of User like so:
@user = User.first
And lets say User has_many :posts, and Post has_many :comments. I want to preload the posts and ...
0
votes
1answer
46 views
rake aborted:PG::ConnectionBad: fe_sendauth: no password supplied
I'm on Cloud9 Ubuntu Template and I installed postgres. I'm getting an error when I try to do a "rake db:migrate".
rake aborted:PG::ConnectionBad: fe_sendauth: no password supplied
Related settings ...
0
votes
1answer
12 views
Elixir Ecto - PostgreSQL jsonb Functions
I am in the process of converting a Ruby on Rails API over to Elixir and Phoenix. In my Postgres database, I have a table with a jsonb column type. One of the keys in the json is an array of colors. ...
3
votes
4answers
968 views
Emacs, sql-mode, Postgresql and inputing password
I'm trying to connect to a PostgreSQL DB thru Emacs using the sql-mode.
I start Emacs, command M-x sql-postgres and it prompts for User,
Database and Server but not for the password. An empty buffer ...
0
votes
2answers
13 views
Updating Local Postgresql Database with R, using Update or Insert
I'm working on establishing a local server in order to interact with hosted tables locally and remotely. I've successfully connected through Rpostgresql to the local db, and I am able to query and ...
0
votes
0answers
3 views
Restore postrgres without ending connections
I run a number of queries for adhoc analysis against a postgres database. Many times I will leave the connection open through the day instead of ending after each query.
I receive a postgres dump ...
0
votes
0answers
10 views
Flywaydb Migration freezes
I am using postgres 9.4 in a docker container for a project. It has 9 databases. All database sizes are below 100Kb except one database having sql script size of 219Mb. I made a backup of the data ...
0
votes
0answers
27 views
Insert the result of a PostgreSQL “select” at the end of every line of a file
I have a file with some IPs in the second column
[postgres@hotname]$ cat /tmp/ips.txt
538954 10.20.30.1
130708 10.20.30.2
55300 10.20.30.3
47634 10.20.30.4
And a table with the name of the ...
0
votes
0answers
4 views
How can I tell/specify which version of the postgres client/libs psycopg2 is using?
I have psycopg2 for a python program, but the computer the program is running on has two version of postgres installed: 9.2 in the 'main' system, and 9.5 in /opt. The postgres DB is hosted elsewhere, ...
3
votes
4answers
963 views
Add number to rows based on identical values in selected columns
I have a PostgreSQL database that contains traffic tickets written by a few jurisdictions.
Some jurisdictions don't indicate if multiple tickets are written in one traffic stop. However, that can be ...
0
votes
0answers
5 views
Configuring postgres with openSSL
I tried installing postgres with openSSL by doing
./configure --with-openssl
but I got an error saying
configure: error: header file openssl/ssl.h is required for OpenSSL
However, I do have ...
0
votes
0answers
8 views
Postgre Trigger error 42601 CREATE OR REPLACE FUNCTION
So, I'm trying to create a trigger that receive some values from the insert action, join with some tables and then insert it's result into another table.
I don't know if the query is too complex for ...
1
vote
0answers
26 views
How to identify a db transaction and use it on both sides of a RPC
In our system we use transactions heavily. Our platform will automatically roll back the transaction at the very end of a request if any error messages were found in a known message collection. ...
0
votes
1answer
8 views
“syntax error at or near ”ON“” when doing bulk manipulation using NHibernate + Npgsql
I'm trying to execute a parameterized "upsert" query using NHibernate named parameters but I'm getting an exception. The syntax appears to be correct but it is still throwing:
NHibernate.Exceptions....
0
votes
1answer
23 views
jOOQ - How to create a SQL query with WHERE having multiple conditions
I want to create a query which should work like this one:
SELECT
sensor_id,
measurement_time,
measurement_value
FROM
public.measurement_pm2_5
WHERE
(sensor_id = 1 AND measurement_time ...
2
votes
0answers
11 views
SQL duplicate elimination for tables with columns using UNION or INTERSECT in Postgres
In Postgres the simplest possible query is
postgres=# select;
(1 row)
The result of this query is a relation with a single
tuple (row), i.e., the empty tuple.
So we get the relation {()}.
This ...
0
votes
0answers
6 views
access to dialectresolutioninfo cannot be null when 'hibernate.dialect' windows server
I am trying to host my spring boot application to the server. I am able to run the application on local machine but immediately I host the application to the server, via tomcat it throws a weird ...
0
votes
1answer
7 views
create trigger in PosgreSQL
So I found this example:
create function eager.account_insert() returns trigger
security definer
language plpgsql
as $$
begin
insert into eager.account_balances(name) values(new.name);
...
0
votes
1answer
10 views
Setting value using a subquery that return's no row on Postgres
I have two tables: tire_life and transaction on my database.
It works fine when the subquery returns a value, but when no row is returned it does not update the tire_life table.
That's the query I'm ...
0
votes
0answers
3 views
PostgreSQL full text search issue (to_tsquery)
I'm having issues searching my database for game name that contains a stop word. I'm only looking for exact matches in general, I want as few "fuzzy" hits as possible for all my searching, optimally ...
4
votes
3answers
4k views
How to copy table from server to another in PostgreSQL?
I have two servers on PostgreSQL 8.4:
server1:5432
server2:5432
Now i want to copy table from server1 and put it in data base on server2.
Its possible to do?
UPDATE
I try do like in @Valery ...
2
votes
1answer
19 views
Postgres: remove check constraint on varchar column
I'm coming from a MySQL wold and am having a hard time doing things in PostgreSQL.
I have a column which looks like this in my GUI client:
I'm not sure if it is an enum column, or a varchar with a ...
4
votes
2answers
907 views
Redshift: Serializable isolation violation on table
I have a very large Redshift database that contains billions of rows of HTTP request data.
I have a table called requests which has a few important fields:
ip_address
city
state
country
I have a ...
0
votes
0answers
24 views
SSL error when pushing postgres db to heroku
I'm trying to push my local postgres db to heroku and I get the following SSL error:
psql: sslmode value "require" invalid when SSL support is not compiled in
I uninstalled postgres and ...
1
vote
0answers
8 views
Django Cache (Redis) auto refresh when data is changed
Is there any way to notify django to refresh the cache if there were any changes made on the database related to the cache data ? I have found this post, there is no latest answer and the django ...
2
votes
5answers
63 views
SQL Retrieve rows that have all rows from other table
I am facing some issues retrieving data using SQL in one specific scenario:
Suppose I have the following tables:
A (id, attr_a, attr_b);
B (id, attr_d, attr_e);
C (id_a, id_b);
As you can see, ...
0
votes
0answers
10 views
Time formats are different in the login session log captured in the CSV in postgres
I set the log_destination = 'stderr,csvlog' in postgresql.conf. So, I am getting the login session detail in the csv file, but the log_time gives the time including milliseconds whereas ...
0
votes
1answer
8 views
How to replace a single character in every record for every table + column in the entire database with an empty string?
my problem is the following:
I have a bibliographic database that was started around 1995 and has gone through several conversion into different formats, the latest is PSQL. Due to automatic ...
1
vote
2answers
27 views
SQLAlchemy emitting cross join for no reason
I had a query set up in SQLAlchemy which was running a bit slow, tried to optimize it. The result, for unknown reason, uses an implicit cross join, which is both significantly slower and comes up with ...
1
vote
2answers
43 views
Run code if x times if item present
From my last question, I cant seem to get what I want. Below explains a lot better:
external = ["apple"]
internal = ["grapes", "pear", "mangoes", "apple"]
external.each do |fruit|
if not internal....
0
votes
0answers
12 views
PostgreSQL Change Column Name To Another Tables Column
I'm using PostgreSQL.
I have a table for a customers booking, Folio, that shows the staff ID of the staff that created the booking, and the ID of the most recent staff to change this booking.
I want ...
0
votes
0answers
8 views
SQL Query in a payroll view with Postgresql
I have a payroll database with the following script, which in a database works, and execute it in another database copy and not. I could not find the error. I would appreciate any suggestions.
SCRIPT:...
0
votes
2answers
18 views
Rails and Heroku - Rake db:seed only populates some databases and not others?
I have a rails app that I've been working on locally and have deployed to heroku. I have a lot of data that needs to be seeded that has worked well locally, with a small issue that I didn't pay much ...
1
vote
2answers
29 views
Do I first need docker environment before starting my project?
I am going to work with Node.js and PostgreSQL on Linux. I read many hours about how docker actually works. Still I am not sure that is docker environment needed before starting my project or I can ...
0
votes
0answers
22 views
cakephp condition using '?' in the string
Is there any way to make a condition using '?', ex.: Model.field ? 'value'.
I trying to make a query in a jsonb type column in postgresql, but it seems that PDO handles the character as a placeholder.
0
votes
0answers
17 views
Translate Postgres query into T-SQL for SQL Server 2012
There are many articles pertaining to "translate SQL Server to Postgres". I have a Postgres query that I would like to translate into SQL Server. Does someone have the answer or at least a website to ...
1
vote
0answers
12 views
How can I reuse the results of a WITH clause for two queries in Redshift / Postgres?
(Asking about Redshift's subset of PostgreSQL)
This is an "I want a pony" question: I know how to do this using explicit temporary tables, but would prefer to avoid that.
I would like to run two ...
1
vote
0answers
6 views
Enable Postgres PDO module for PHP 5 on Ubuntu to work with Laravel
I've spent some hours trying to change the connection driver of my Laravel app from MySQL to Postgres. I'm working with Laravel 5.2 on a Ubuntu 14 (and PHP 5.5.9).
I had Postgres installed in this ...
0
votes
2answers
17 views
SQL get values without duplicates (that are different in case)
I'm trying to get a list of all users in my database, but when I run the following query:
SELECT cu.user_name AS username, cu.display_name AS displayname,
cm.lower_parent_name AS group, cu....
1
vote
2answers
45 views
Extract key, value from json objects in Postgres
I have a Postgres table that has content similar to this:
id | data
1 | {"a":"4", "b":"5"}
2 | {"a":"6", "b":"7"}
3 | {"a":"8", "b":"9"}
The first column is an integer and the second is a ...
0
votes
1answer
25 views
Using multiple values in a SQL column - Is it possible (or even a good idea)?
If you were creating a todo list app where users can sign up and manage their own todo list, would it be better to:
Option 1: Have a todo_lists table in the database. There will be a username, ...
1
vote
1answer
9 views
Alembic not handling column_types.PasswordType : Flask+SQLAlchemy+Alembic
Background
I'm trying to use a PostgreSQL back-end instead of Sqlite in this Flask + RESTplus server example.
I faced an issue with the PasswordType db column type. In order to make it work, I had ...
