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
1answer
11 views
Can someone please show me how to upload multiple files with Django into a postgresql Database??
I've been trying to figure this out for weeks to no avail unfortunately. Even after looking through hundreds of web pages and tutorials, I still can't figure it out. PLEASE HELP!
P.S.
Thanks ahead ...
0
votes
1answer
5 views
Best way to query postgres db from django to list all users (tab1) which have active book borrows (tab2)?
I have a simple application in Django. The simple logic is kind of book library.
Tab1. stores the users:
class Readers(Model):
last_name = CharField(...)
first_name = CharField(...)
...
0
votes
1answer
37 views
How to join these tables in SQL
I have the following tables:
match
id | rival_team
----------------------
| 1 | chelsea fc
| 2 | real madrid
player
ID | name | last_name |
---------------------...
0
votes
0answers
6 views
Naming for 'id' column in massive.js
I have an existing table in which the primary key column is called gid instead of id. When I use a massive document query like the one below I get Error: column "id" does not exist. Can I specify that ...
0
votes
1answer
14 views
Postgresql query to get sum of tree
Hi guys I would like to ask about postgresql and what could be the best query to get sum of column when you have table of elements that has some descendants of more levels ie.
id &...
0
votes
1answer
24 views
SQL update records with ROW_NUMBER()
I have a table called 'cards', which has a column called 'position'
How can I update/set the 'position' to equal the row number of each record, using ROW_NUMBER()?
I am able to query the records and ...
1
vote
1answer
17 views
Slow Postgres 9.3 queries
I'm trying to figure out if I can speed up two queries on a database storing email messages. Here's the table:
\d messages;
Table "public.messages"
Column | ...
0
votes
1answer
12 views
Do I need to have two different objects to read and to write in my database using gorm (golang)?
gorm tell in the documentation that "Base model definition gorm.Model, including fields ID, CreatedAt, UpdatedAt, DeletedAt, you could embed it in your model, or only write those fields you want":
// ...
go
1
vote
2answers
18 views
Postgresql getting other information from max tuple
Say you have a table with studentID, class, grade. I want the maximum grade for each class. This is easy, just group by class and get max(grade). But the problem I'm having is how to also get the ...
0
votes
1answer
7 views
Convert Unix timestamp to timestamp without time zone
How do I convert a Unix timestamp (or epoch time) to a PostgreSQL timestamp without time zone?
For example, 1481294792 should convert to 2016-12-09 14:46:32.
I tried SELECT to_timestamp(1481294792)::...
0
votes
1answer
22 views
Postgres: Could not choose a best candidate function
Can someone explain how to fix this query?
SELECT date_part('month', scheduler_scheduleevents.date), sum(price)
FROM user_settings_userservices
JOIN scheduler_scheduleevents
ON ...
0
votes
0answers
27 views
How do you transpose a table in a generalizable way in SQL?
How would you transpose this table:
old_table
+----+--------+-------------+-------------------+
| id | type | field | value |
+----+--------+-------------+-------------------+
| ...
0
votes
0answers
11 views
Catch username from spring web application to use by trigger
I would like to save in my database information about history, for example user "dog" edited field "grass" in table "garden".
I have trigger which saves everything correctly but I have problem with ...
0
votes
0answers
15 views
Postgresql: Identify events across multiple rows (power consumption)
I've got a Postgresql table that contains time series data of accumulated power usage and the actual current at a time. At the moment washing a washing machine is logged which looks like this:
https:/...
1
vote
3answers
46 views
Dispaying All Data Points between two ranges
I have a table that has several rows out lining ranges between two zip codes for each state. I am hoping to be able to take the two ranges and display every value between the two ranges on their own ...
0
votes
0answers
8 views
PostgreSQL Search Path cached, needed Java app restart
I recently added a migration to set a PostgreSQL(9.5) search path on a database.
ALTER DATABASE mydb SET search_path TO data,public;
ALTER USER myuser SET search_path = data, public;
This database ...
0
votes
0answers
25 views
Efficiently order SQL results with multiple where clauses
I have the following simple data model:
User table:
id (pk)
name (varchar)
Post table:
id (pk)
user_id (fk)
title (varchar)
description
Comment table:
id (pk)
user_id (fk)
post_id (fK)
status (...
0
votes
0answers
7 views
SELECT pg_try_advisory_lock / SELECT pg_advisory_unlock when deploying to Heroku
I'm trying to deploy my rails app to heroku and running into an error that looks like its stemming from a migration that sqlite is permitting in my dev environment and postgres not in production. I'm ...
0
votes
1answer
25 views
complex sql query combining 'select max' and select count(*) queries
I have the following table, which is used for an auto-responder, it tracks all the messages exchanged to each person. It tracks each person by match_id
CREATE TABLE public.sms_log
(
id bigint NOT ...
0
votes
0answers
14 views
IndexMissingException: [News] missing
I have porject in Spring boot where i utilize elasticsearch. i save my data to my primary database in postgres and for searching in elasticsearch(ES). I have configured added some data. and i can see ...
elasticsearch
-2
votes
0answers
13 views
foreign key constraint error on empty table
Why do i get a foreign key constraint error when i try to insert this record in database when the table is empty
INSERT INTO res_partner_mail_wizard_rel (wizard_id, partner_id)
(SELECT 8, 19697) ...
-1
votes
1answer
13 views
Could not initialize class com.ibm.ws.ffdc.FFDCFilter. DSRA0010E: SQL State = 28P01, Error Code = 0
can I get assistance with the error codes coming from eclipse when i try to deploy enterprise application on websphere. and secondly, as i followed craig st jean, i also face another problem with ...
0
votes
0answers
20 views
First Transaction and Second Transaction in Postgresql
I have two tables transaction & user
transaction includes columns
user_id transaction_type posted_at memo balance
12345 credit 2016/01/01 amazon 450
23456 debit ...
1
vote
1answer
18 views
Strange the rule works for an insertion on PostgreSql 9.5/9.6
example:
CREATE TABLE public.test
(
id bigserial NOT NULL,
name text
);
ALTER TABLE public.test
ADD CONSTRAINT test_constraint_pkey PRIMARY KEY(id);
CREATE TABLE public.v_test
(
id bigserial NOT ...
0
votes
0answers
34 views
Trouble understanding a protocol definition
I'm reading the Postgresql wire protocol and having trouble implementing a section, related to simple queries where the spec says:
Query (F)
Byte1('Q')
Identifies the message as a simple query....
0
votes
0answers
10 views
Foreign data wrapper within a function
I'm writing a SQL function with a foreign data wrapper in it. This is because I regularly use foreign data wrappers for data in shapesfiles and file geodatabases. The preferred output of the function ...
0
votes
1answer
17 views
Schema error from postgresql update statement
I have a POSTGRESQL update statement that I'm trying to rewrite with explicit joins and needing some help whereby the NESTED select statement alias is recognized to execute the outer UPDATE statement. ...
0
votes
1answer
23 views
Build pairs from an ordered list
I'm playing with train time tables and considering building connections between stations from the times.
Let's take an example:
Trip I: A (t1) ------------> B (t3) ------------> E (t5)
Trip ...
0
votes
1answer
45 views
Generate 5 million records and insert them into the database in five seconds
I have a task to practice. Generate 5 million records: string (15), string (15), int (min: 1 max: 99) and store them in a database postgresql.
My first version of the script was the estimated time for ...
0
votes
1answer
17 views
Intellij Idea Ultimate Postgres/EDB database plugin “oid” is ambigous
I use Intellij Idea Ultimate 2016.3 version. I try to connect to Postgres DB using the Intellij Idea's default "Database" plugin. However when i try to sync with the DB, I see this in the event log:
...
intellij-idea
1
vote
0answers
25 views
Postgresql JSON index strange query time
Let's say we have table like this:
CREATE TABLE user_device_infos
(
id integer NOT NULL DEFAULT nextval('user_device_infos_id_seq1'::regclass),
user_id integer,
data jsonb,
created_at ...
0
votes
1answer
28 views
How to aggregate records from two columns having same values in two columns in postgresql
I have to write a query in which i have to put condition on two columns and then aggregate the record in third column on the basis of first two columns. you can have an idea from database table.
This ...
0
votes
2answers
24 views
using timestamp sql
trying to add a row with timestamp for my table which consists of
time timestamp without time zone NOT NULL
I'm using the function now(), but I keep getting
ERROR: invalid input syntax for integer:...
0
votes
0answers
25 views
Rails - Group by 2 date columns
Using Rails and PostgreSQL how can I group by two DATETIME columns?
I want to format a string based on the weekdays of a establishment is open
Eg: MON-FRI 10:00 - 22:00, SAT-SUN 10:00 - 18:00
I ...
1
vote
0answers
33 views
What is better DECIMAL or may INT (BIGINT), to represent the amounts in the database?
I have a table and I keep in it prices in different currencies.
I know that I can't use floating point numbers.
I wonder whether to store them in a DECIMAL or INT (BIGINT).
More convenient is to ...
0
votes
0answers
16 views
Query to verify number 24 hours before the calling
I have a table [POSTGRE] like this...
- Agent who answered the call,
- call number
- datetime end of call.
Agent | Caller | DateTime
------------------------------------
101 | ...
1
vote
1answer
17 views
sorting data by “value” from jsonb_each(), is reliable?
Given jsonb type column and data structure like this:
{
"1": 10000.2,
"2": 77.2,
"3": -200.09,
"4": 12.55
}
Need retrieve result from this json and sort by VALUE
SELECT * FROM jsonb_each(
...
0
votes
0answers
27 views
Optimize table or query?
I've a fact_table that contains some values, where I do query on time range but it takes a several time to be loaded.
This table calculates data for each day, including some indicators based on user ...
0
votes
0answers
18 views
plpgsql syntx error at $$
I'm trying to write a function with plpgsql for some reason i'm getting a syntax error at $$ language plpgsql;
This is the func i'm writing:
create or replace function hello() returns trigger as $$
...
0
votes
1answer
12 views
Restart postgres server writes fatal error password authentication for user postgres in logfile
When i restart the postgresql server with sudo service postgresql restart everything works as expected.
But the logfile /var/log/postgresql/postgresql-9.3-main.log shows that a fatal error happened.
...
0
votes
0answers
8 views
How to resolve “Failed to connect to the storage cluster” error when installing RHQ`?
I am trying to install the red hat RHQ server on my local machine. I installed and configured a PostgreSQL DB following this guide.
Afterwards, I switched back to the official Quick Installation ...
0
votes
1answer
22 views
Import ONE_2_MANY data from flat CSV file into two PostgreSQL tables
I have a following PostgreSQL schema:
CREATE SEQUENCE user_question_rev_user_question_rev_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE ...
0
votes
3answers
27 views
Getting empty rows of GROUP BY Date without joining to a dummy date table
I have the following SQL table (I am using PostgreSQL)
create table roombooking (
r_no integer references room,
b_ref integer references booking,
checkin date not null,
checkout date ...
0
votes
1answer
41 views
PostgreSQL ERROR: payload string too long
I have the following PostgreSQL table and trigger function implementing history of the table:
CREATE TABLE "ps_counters"
(
"psid" integer NOT NULL,
"counter" bigint[] NOT NULL -- This is the ...
1
vote
0answers
38 views
How to use inserts in a Liquibase JSON changelog?
I'm just starting to use Liquibase (3.5.3) in a Java (1.8) project with Maven (3.3.9) and PostgreSQL (9.5.4). I'm not understanding why the second changeset of the following json changelog file is not ...
0
votes
1answer
22 views
Display a field of an object - PostgreSql
I have a function in PostgreSql that performs a calculation and returns an object that contains information separated by ;
How I can retrieve information from this object using request Postgre?
This ...
0
votes
1answer
33 views
Postgres select from few other tables with relation to main table
I have some troubles with writing sql query.
I have 3 tables in my postgres database.
Table devices:
- dev_id (PK)
- device_type (varchar)
Table thermomether_values:
- status_id (PK)
...
0
votes
0answers
13 views
How to setup postgres with Kubernetes using Deis Workflow?
I am using Google container engine and Deis Workflow to run my rails application. I do not have a dockerfile, I just use Heroku buildpack.I was successfully able to deploy my app but I am not able to ...
kubernetes
1
vote
2answers
17 views
Rails 4 - Order by the presence of an attribute
Using Rails 4. Psql DB.
I have a model Article with an attribute amazon_title. I am having trouble understanding how I can order my articles so articles with the amazon_title present are first, and ...
0
votes
1answer
28 views
Postgres DB stuck for a short period
My Application has Postgres DB with AIS. I receive events on application whenever a change happens in DB.
We are facing an issue where postgres DB table rows are not decremented, while I receive a ...
