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 ...
4
votes
1answer
9k views
Why am I getting a “cross-database references are not implemented”?
very simple update to a postgresql database, and it's not working. The sql select statement is fine, and returns the right values.
It's when i get to the update, it throws the error:
{"ERROR [...
-2
votes
0answers
11 views
PostGRESQL Database Query
I seem to be having a port error when I run my app. This is what appears.
My understanding of databases are limited, and this is the first time I am forced to use postgresql. Is there any resources I ...
0
votes
0answers
21 views
Passing JSON From Angular 2 Form To Node Server Side
I have all the pieces to add my form data to the postgres database but I am having trouble understanding the actual hand off of the data from the form to the route.
I am creating an object with the ...
0
votes
1answer
15 views
How to drop database which has some sessions open
I am trying to drop a database using the following command and I get an error
DROP DATABASE IF EXISTS mydb;
There are 5 other sessions using the database.
Is there any sql statement/sequence of ...
0
votes
1answer
9 views
Can postgres role name be `user`?
My username in the personal laptop is user. So running psql from command line throws an error psql: FATAL: role "user" does not exist. This is because the user is a reserved keyword(not sure about ...
0
votes
1answer
14 views
How to update all columns with INSERT … ON CONFLICT …?
I have a table with a single primary key. When I attempt to do an insert there my be a conflict cause by trying to insert a row with an existing key. I want to allow the insert to update all columns? ...
0
votes
1answer
29 views
Hierarchical structure in PostgreSQL table
I have a PostgreSQL table that contains the following structure:
Parent child1 child2
1 10 12
2 13
3
I want to have:
Parent child1 child2
1 ...
0
votes
1answer
19 views
PostgreSQL query: getting latest forecast before a deadline, compare to actual
In general, I want to see how close the wind forecast comes to the actual wind, by hour. And in my table I have revised wind forecasts that come in every few hours. But I want my query to show the ...
0
votes
1answer
9 views
Single quotes stored in a Postgres database
I've been working on an Express app that has a form designed to hold lines and quotes.
Some of the lines will have single quotes('), but overall it's able to store the info and I'm able to back it ...
1
vote
1answer
27 views
How to write parameterized sql query to prevent SQL injection?
I initially discovered that this was an issue when I tried to search for terms that had been prepended with a hashtag, which it turns out is a comment delimiter in SQL. The search returned nothing, ...
3
votes
2answers
44 views
Hartl Rails tutorial chapter 2, “hello world” isn't shown in http://localhost:3000
I'm following every step of M. Hartl's Ruby On Rails Tutorial, but stuck on Chapter 2 ("Hello world"). I changed the code in application_controller.rb:
class ApplicationController < ...
9
votes
2answers
6k views
SQLAlchemy no password supplied error
This is probably a silly error but I cannot seem to find a satisfying solution.
When running db.create_all(), I got the following error.
sqlalchemy.exc.OperationalError: (OperationalError) ...
0
votes
0answers
20 views
PostgreSQL schema for a game to pick the winner of two teams
I'm looking to create a project for fun where users pick the outcome of two teams. For starters I know that I would need tables for User, Team, and possibly Contest objects. What I'm really stuck on ...
3
votes
3answers
203 views
Belongs_to presence in Rails 5 not working
To my knowledge, the new default in Rails 5 requires belongs_to associations to be present. I made a model with this association, but the problem is I don't get presence validation error when the ...
0
votes
2answers
14 views
Use SQLite for Django locally and Postgres on server
I am starting with Django development as a hobby project. So far I have happily worked with SQLite, both in development (i.e. with py manage.py runserver) and in deployment (on Nginx + uWSGI). Now I ...
0
votes
0answers
12 views
How get multiple resultset / cursors with django and postgresql?
Taking the example from http://trentrichardson.com/2012/01/04/return-multiple-result-sets-with-php-and-postgresql-functions/, I wanna return multiple results to a view.
I have this code now:
CREATE ...
2
votes
0answers
21 views
Postgresql 9.2 - Why the speed difference - SQL Subselect and JOIN?
I am trying to query another software's DB and so I have no control over making indexes, functions, etc. I have read-only access, so I don't believe I can make a temporary table even. I just can't ...
-2
votes
1answer
14 views
How to get and compare the elements of the jsonb array in Postgres?
Postgres 9.6.1
CREATE TABLE "public"."test" (
"id" int4 NOT NULL,
"packet" jsonb,
)
WITH (OIDS=FALSE)
;
Jsonb
{"1": {"end": 14876555, "quantity":10}, "2": {"end": ...
1
vote
2answers
18 views
How do I do a deep copy with a single PostgreSQL query?
I have three tables:
CREATE TABLE offers
(
id serial NOT NULL PRIMARY KEY,
title character varying(1000) NOT NULL DEFAULT ''::character varying
);
CREATE TABLE items
(
id serial NOT NULL ...
0
votes
2answers
23 views
How to find the sum of local maximas per day in Redshift?
I have some numeric data with ascending time stamps, as follows:
amount | received_at
_______|______________
30 | 2016-11-18 10:21:35 AM
60 | 2016-11-18 10:22:05 AM
90 | 2016-11-18 10:22:...
2
votes
1answer
36 views
Join Alias Columns SQL
I am trying struggling with joining alias named columns. Overall, I want an output with the with date, hour, and the actual and forecasted (most recent before 10am on the previous day) windspeeds.
...
12
votes
0answers
47 views
Hibernate multiple connections dynamically changing
I know there are similar questions about this scenario, however none of them I have found matches my case and I'll like to have a solution that doesn't impact performance. I have to do multiple ...
0
votes
0answers
7 views
Liquibase unable to authenticate user with @ and ! in password
Using Liquibase 3.5.3 against PostgreSQL 9.6, I am unable to authenticate using users (e.g. postgres) with a password containing @ and ! characters. If I change the password to remove them ...
0
votes
0answers
10 views
Performance of NUMERIC type with high precisions and scales in PostgreSQL
I am storing cryptocurrency transaction balances in NUMERIC column. As cryptocurrency values vary in somewhat extreme ranges compared to traditional currency, I am using NUMERIC(60,20) type to capture ...
3
votes
0answers
54 views
Slow updating record on heroku postgresql
I have a rails app hosted on heroku with their postgres database add-on. I can't figure out why sometimes updating a record on users table is getting slow. It's always slow, but it happens as often as ...
1
vote
1answer
18 views
postgressql Aggregate function select 2 corresponding fields
I have the following sub query:
LEFT OUTER JOIN
(
SELECT
MAX(testresult."testdate") AS beforeresult,
testschedule."test_id",
testschedule."asset_id",
testschedule."...
0
votes
1answer
8 views
Specify timeout to connect to pg database using node-postgres package?
I'm trying to connect to a postgres database. If it takes more than 1/2 a second to connect(), I'd just like to timeout and assume it's unavailable. Unfortunately, if I change the config to an ...
1
vote
1answer
14 views
How to build an efficient select command across multiples tables in Rails4?
Trying to figure out an efficient ways to select records that have attributes across multiple tables. Here's the basic setup:
structure
Plants (fields: id, name_id, location_id, color) (1000 ...
1
vote
2answers
43 views
JavaEE the right choice for a non-web n-tier server model?
I'm working on a hub-and-spoke architecture n-tier client/server solution for a small company I work at. Basically, the server (written in JavaSE) running at our site will talk to client server (...
0
votes
0answers
38 views
regexp_replace instead of TRIM
EDIT - complete rewrite as per user comments.
First post here - trying to do this right.
I am looking to use a regex_replace instead of a TRIM in the following.
SELECT
'UPDATE '||quote_ident(...
0
votes
2answers
18 views
problems with updating multiple columns in postgresql 9.3
I want to update three columns in the profile table based on the phone table. it did not work and got error message:
ERROR: syntax error at or near "home_phone"
LINE 2: set ...
0
votes
0answers
11 views
Count the number of sessions over the trailing X days
I have a connection log table for customers in our database, and I am trying to count on a daily basis the number of connections each customer has made over the previous seven days. The source table ...
0
votes
1answer
25 views
Reducing a Combination of Multiple Selections to a Simpler One
Looking at the SQL below, it feels like a very verbose way to accomplish what I am trying to do. Essentially, my goal is to select all adult patients (determined by tables mimiciii.patients and ...
0
votes
1answer
14 views
Convert a PostgreSQL integer[] array to a numeric list in R
I am storing the result of a PostgreSQL query as a data.frame in R. One of the "columns" is an integer[] array type. In R, this is imported as a character string by the RPostgreSQL package.
How ...
1
vote
1answer
40 views
How to build a list of unique values and display an array of their ids?
This code is a simplified version of the concept I'm trying to achieve. In reality I'm working with the results of a somewhat complex query - but I figured this simple example would be enough.
I'm ...
0
votes
1answer
24 views
SQL command to stop job in pgAdmin 4
I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the ...
0
votes
2answers
43 views
Count for each record within table
I have a table (stu_grades) that stores student data and their grades.
I want to find out how many times for e.g. each student in that table got 'A' and then 'B' etc.
How do i do this? my feeling is ...
0
votes
1answer
11 views
Generating sql file with uuids and referring those ids further postgres9.5
I am creating a sql file which has uuids as primary key. Here is how my create table definition looks like using pgcrypto extension
CREATE EXTENSION pgcrypto;
CREATE TABLE snw.contacts(
id ...
0
votes
0answers
7 views
Make simple substraction in PostGIS with Map Algebra operation
I have multiple raster images in my PostGIS database, and created tiles of each raster. Now I want to subtract the values of one raster from a second raster image and I want to save the changed values ...
0
votes
1answer
33 views
how to return an array of (bigint, double precision) from a function in order to convert it to json array
What I need is a string/text json-compliant array of arrays like the following:
[
[1421420100, 14214201003487],
[1421420101, 14214201003487],
[1421420109, 14214201003487]
...
]
in order ...
2
votes
2answers
37 views
Rails 5: Best way to iterate through a polymorphic model and find the top level parent?
Edit: In an effort to remain as transparent as possible and provide a working solution to people in the future I wanted to explain how I used @John Hayes-Reed's answer with my issue. I'll update the ...
0
votes
1answer
19 views
Postgres inner query performance
I have a table which I need to select from everything with this rule:
id = 4524522000143 and validPoint = true
and date > (max(date)- interval '12 month')
-- the max date, is the max date for ...
0
votes
1answer
23 views
Postgresql jsonb_agg subquery sort
How can I sort the results of a subquery that's using a json aggregate?
If I had a schema like this:
CREATE TABLE plans( id integer NOT NULL, name character varying(255));
CREATE TABLE plan_items ( ...
1
vote
1answer
39 views
CSV data into postgreSQL using Python
I am trying to import CSV data into postgreSQL using Python. I tried to search on Stack Overflow for this issue but couldn't find anything fruition for my situation. I have empty columns in my CSV ...
-1
votes
0answers
8 views
sql stored procedure for username/ password validation and output message formating [on hold]
Please guys, i need an sql stored procedure that accepts username and password as parameters. if username & password matches then it should output two strings in the following format: x @@@ y ...
0
votes
1answer
16 views
postgresql, could not identify column in record data type
I'm trying use dynamic SQL with Postgresql. I'm stuck with trying to use id field from db_row RECORD in another EXECUTE statement. Field id is set in record variable but I still get error:
ERROR: ...
1
vote
0answers
25 views
How to migrate data from ExpressionEngine to Rails App
Right now we have a system that is using ExpressionEngine, but we are rebuilding everything from scratch using Ruby on Rails. What I'm trying to figure out is how to get all the existing data in ...
0
votes
1answer
17 views
How to delete rows depending on the previous column - postgresql
i've a table which looks like this:
action | timespan
------ | ------
start | NULL
------ | ------
finish | 00:10:30
------ | ------
start | 00:05:00
------ | ------
start | 00:00:01
------ | ----...
0
votes
0answers
37 views
+50
How to persist an Entity that only contains a collection and an ID?
I have a problem where I cannot persist or merge an object that only contains an ID and a collection of other objects. If I add another field it will commit the transaction as normal. Also, if I ...
0
votes
4answers
41 views
See how many times condition met
I have a table (stu_grades) that stores student data and their grades.
I want to find out how many times for e.g. each student in that table got 'A' and then 'B' etc.
How do i do this? my feeling is ...
