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
10 views
How to write a raw SQL query in Django QuerySet?
I want to use following SQL query in Django but I was unable to use that after trying directly with raw SQL. The SQL works directly while executing on PostgreeSQL query window but on Django did not ...
0
votes
1answer
7 views
Count on this LEFT OUTER query is returning 1 when I want 0
I have the following query:
SELECT
l.id, l.name, l.store_id, COUNT(1) as reviewCount
FROM
locations l
LEFT OUTER JOIN
reviews r ON l.id=r.location_id
GROUP BY
l.id
ORDER BY
...
0
votes
1answer
33 views
postgreSQL: How Select the nearest date that is not null
I got a date that I want to find the all records in the past that got the same month and day.
The problem accrues when there is no such date in the same year. For example, the 29th February.
My goal ...
0
votes
0answers
11 views
docker-compose rails app doesn't find db when using up but does when doing run
I have a simple Rails application that I'm trying to run using Docker. When I run docker-compose up it runs but when I load a page it tells me that my db doesn't exist. Even though when I run docker-...
1
vote
1answer
15 views
In PHP PDO how to get “RETURNING” clause values of PostgreSQL upsert query
I have this upsert query written in postgreSQL
$statement = 'INSERT INTO "CharactersUnlockToBuyLevels"
("CharacterId", "LevelId", "AmountToBuy", "EagleStatueId", "Location",
...
0
votes
0answers
9 views
PostgreSQL Connection String with ODBC driver in C#, Keyword not supported: driver
Lately I've been trying to connect a PostgreSQL 9.6 database on my C# project but I've been struggling setting up the connection string correctly due to "Keyword not supported:driver".
After having ...
0
votes
1answer
10 views
Difference between SDO_CONTAINS and SDO_RELATE with MASK=CONTAINS?
What is the difference between following oracle spatial query? Does both function return same result? I am confused with MASK=CONTAINS
SDO_CONTAINS (col1_geometry,col2_geometry )='TRUE'
SDO_RELATE (...
0
votes
1answer
19 views
I am writing postgresql data to file using bash and psql. I need the column headers written to the output file
Script:
#!/bin/bash/
PGPASSWORD='*******' psql -h host_name server_name user_name -t -A -F "," -c "select b.id AS booking_id, b.hotel_id as hotel_id, b.invoice_no as invoice_no, b.guest_id as ...
0
votes
1answer
22 views
PostgreSQL matrix transformations
I have a PostgreSQL table called test which has 2 columns - (1) id & (2) matrix as follows: -
create table test (id integer,
matrix double precision[]);
insert into test (id, ...
1
vote
1answer
28 views
Boolean column in multicolumn index
Test table and indexes:
CREATE TABLE public.t (id serial, cb boolean, ci integer, co integer)
INSERT INTO t(cb, ci, co)
SELECT ((round(random()*1))::int)::boolean, round(random()*100), round(random(...
-1
votes
1answer
19 views
Is there any options to use SQL Server tables in postgresql?
I want to see and query data from SQL Server tables in postgresql. How can I do it? I'm using SQL Server 2014, POSTGRESQL 9.4. Windows 10
0
votes
0answers
22 views
CakePHP cannot connect to Postgres. Pgadmin4 can however
CakePHP3 cannot connect to my PostgreSQL database.
My setup is the following:
Windows 10 host
CentOS7 Virtualbox VM guest
PostgreSQL 9.6
Apache 2.4
PHP 7
Error:
CakePHP is NOT able to connect to ...
2
votes
1answer
73 views
How to improve PostgreSQL performance on INSERT?
I have written a Node.js application that writes lots of records to a PostgreSQL 9.6 database. Unfortunately, it feels quite slow. To be able to test things I have created a short but complete program ...
-5
votes
0answers
33 views
I have up my web into the server, it worked fine locally but it doesn't work once up to the server and giving following error
It says, root cause as follow,
javax.servlet.ServletException: org.postgresql.util.PSQLException: The connection attempt failed.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(...
0
votes
0answers
17 views
sqlalchemy query computed column as model property
Given the following query, is there any way I can have the number_of_kids as property of returned models so I can use model.number_of_kids notation?
Not that I don't want to load the whole list of ...
0
votes
0answers
16 views
Deleting or update rows based on multiple tables
I'm trying to find a way to remove all "parents" from a team where none of their kids are on the team any longer.
I have a PostgreSQL 9.5 table called parent_child like so:
Column | ...
0
votes
1answer
15 views
Post method always returning 503: timeout message while using nodejs to connect to Postgre SQL db
I am running a nodejs app on Heroku and using Postgre SQL db. I am using a post method add_user to add some data to the table users. Everything works fine and data is correctly entered in the table ...
0
votes
0answers
18 views
How to calculate area around a geometry using postgis?
I want to convert the following oracle spatial function into postgis spatial function to calculate an area around a geometry
sdo_geom.sdo_area(geometry, 0.005, 'unit=SQ_MILE')
1
vote
0answers
19 views
Setup Visual Studio's Server Explorer to work with postgresql
I'm trying to setup Visual Studio's Server Explorer to connect to a postgresql database. I am using ASP.NET Core & EF Core with npgsql.
This feature works fine with SQL Server but there's no ...
1
vote
0answers
10 views
How to solve Php Error Cannot instantiate abstract class sfDoctrineRecord on Symfony 1.5.9?
I have a problem when I want to use the command on terminal (linux)
php symfony doctrine:build --all --and-load
The terminal displays the next message:
>> doctrine generating sql for models
...
-1
votes
1answer
17 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 ...
1
vote
1answer
12 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
2answers
63 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
1answer
8 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 ...
1
vote
1answer
19 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
30 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
21 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
20 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
2
votes
2answers
20 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
1answer
29 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
1answer
16 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
21 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
48 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
10 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
26 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
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
20 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
14 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
17 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
22 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
2answers
22 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
36 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
24 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
54 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
18 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 ...
