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
3 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
0answers
11 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
26 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, ...
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 ...
0
votes
0answers
17 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 ...
0
votes
1answer
18 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
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
1answer
13 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? ...
2
votes
0answers
20 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 ...
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
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": ...
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
2answers
40 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 < ...
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 ...
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."...
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.
...
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
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
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:...
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
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
8 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
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
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
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
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
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
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 ...
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: ...
0
votes
1answer
10 views
Safely Making Minor Postgres Upgrade
I currently have postgres 9.5.2 installed on my Mac OSX machine. I want to upgrade it to postgres 9.5.4 (which is not the latest 9.5.X version). What is the best way of doing this? I installed ...
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
0answers
19 views
Postgres/ActiveRecord select as not generating dynamic column
I'm running into a bit of trouble with what I thought was a straight forward enough Postgresql/ActiveRecord query.
I have courses that have many sessions. Date/Time info rests with the sessions. I ...
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
1answer
23 views
PL/pgSQL function returns incorrect bitwise result
I have a PL/pgSQL function callbackfunc. If I run it with bitwise result and unpack the result in my app, I got 65/0/65/65 (which is 1090535745). If I return directly result of bitwise operation (...
0
votes
1answer
17 views
How do I convert timestamp and offset columns to a single timestamptz column?
Image I have a table containing the following two columns:
timestampwithouttimezone (of type TIMESTAMP WITHOUT TIME ZONE)
utcoffset (of type INTEGER)
I want to convert those two column to a ...
0
votes
0answers
7 views
Error Making Report with Postgresql + ODBC + Crystal Report + C#
I'm having a problem with Crystal Report, Postgresql 9.5 (x86) and c# (Visual Studio 2015), I need to make a report in Crystal, so, I make a ODBC Connection between Postgresql and Crystal.
I made a ...
0
votes
2answers
15 views
How to escape \n (newline) when importing CSV into Postgres using \copy command?
I am trying to import data into my postgres table using \copy from a CSV file
The data is as follows:
1,2,"First Line \n Second Line", "Some other data"
My motive is to preserve '\n' while importing ...
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 ...
2
votes
1answer
20 views
How can I get a CIDR from two IPs in PostgreSQL?
In PostgreSQL, I can get the upper and lower boundary of a CIDR-range, like below.
But how can I get the CIDR from two IP addresses (by SQL) ?
e.g.
input "192.168.0.0";"192.168.255.255"
...
0
votes
2answers
21 views
Reset postgres sequence to take un-used primary key ids
I am using postgres 9.5. As part of application initialization I make some inserts in database at application startup with random ids. Something like insert into student values(1,'abc') , insert into ...
0
votes
1answer
11 views
What am I doing here while updating a table in a remote db using postgres_fdw?
Here is how I am doing it:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SERVER IF EXISTS myserver CASCADE;
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.1.1....
0
votes
0answers
22 views
Get back newly inserted row in Postgres with sqlx
I use https://github.com/jmoiron/sqlx to make queries to Postgres.
Is it possible to get back the whole row data when inserting a new row?
Here is the query I run:
result, err := Db.Exec("INSERT ...
go
0
votes
0answers
16 views
How can I achieve this using dblink in postgres?
I have two databases - db1 and db2. These have tables employee with the same schema.
Let db1 be the local database.
I want to compare employee table of both the databases using db1 as a local db.
...
0
votes
2answers
13 views
How can you do an update to a table in another database using postgres dblink?
The query that has to be executed is as simple as below -
Update employee set is_done=true;
The table that I want to update is only present in another database.
I have been using these kinds of ...
1
vote
1answer
24 views
Creating utf-8 database in Postgresql on Windows10
I am trying to create utf-8 database on windows 10:
createdb.exe -h localhost -p 53131 -U user -E UTF8 -T template0 -l en_US.utf-8 test777
But response is:
createdb: database creation failed: ERROR:...
-3
votes
0answers
14 views
database access with jsp
I have dought i.e how we insert the data into more than two tables while submitting the one single form in JSP.I am new to JSP totally.
I feel happy if any on provides the code according to my ...
0
votes
1answer
28 views
PostgreSQL : Get first and last inserted record for each ID column
I have the following table with two columns.
Table:
create table tbl1
(
p_id int,
p_price int
);
Insertion:
INSERT INTO tbl1 values(1,100);
INSERT INTO tbl1 values(1,50);
INSERT INTO ...
1
vote
1answer
9 views
Copy join-query from database to another datable
Previously, I am using dblink to achieve the mission but it involved copy one query only. What if I have doing the join query (4 tables) in one database, then i want to copy the data output into ...
