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
4 views
Npgsql Exception while reading from stream
I'm using Npgsql 3.1.7 since yesterday when I have updated it to 3.1.8 via Nuget but I get the same exception.
I'm connecting to a remote Windows server 2012 and Postgresql 9.5 also tryed Postgresql ...
0
votes
0answers
13 views
Troublesome conversion of plain PostgreSQL query to Slick query
I have a plain PostgreSQL query I'm having trouble translating into slick query. I go stuck in syntax soup when using groupBy clause.
SELECT u.id AS investor_id,
u.account_type,
i....
0
votes
0answers
7 views
Error when ignoring accents in full text search using Ecto and PostgreSQL
I have this search function in my controller:
def search(query, search_term) do
(from u in query,
where: fragment("(to_tsvector('portuguese', unaccent(?)) @@ plainto_tsquery(?)", u.name, ^...
0
votes
0answers
5 views
Sync elasticsearch and casandra with postgres database
I want to sync two dependent databases (elasticsearch and casandra) with my parent database: postgres. I am trying to implement a method in this article: https://qafoo.com/blog/...
elasticsearch
0
votes
0answers
7 views
debian jessie: can't stop postgresql from starting automatically
I have postgresql installed on my system (debian jessie), and I do not want it to start automatically on boot. But that's what it does.
This is what I tried:
The corresponding line in sysv-rc-conf ...
0
votes
0answers
9 views
How to pass the output of select query as column list to another select query in postgresql?
I'm trying to pass the output of a select query as column list to another select query.
select ARRAY_TO_STRING(ARRAY(SELECT distinct COLUMN_NAME::VARCHAR(50)
FROM INFORMATION_SCHEMA.COLUMNS WHERE ...
0
votes
0answers
13 views
PostgreSQL create index in JSON array element
I'm working with PostgreSQL 9.5, and I want to create an index for multiple fields of a JSON object. I'm showing two examples, the first one is working and I'm stuck on the second one.
Example 1 - ...
0
votes
1answer
9 views
heroku postgres Group by query on indexed columns takes more than 2 seconds to execute
On heroku postgres my group by query is taking more than 2 seconds, is it normal? How can I optimize this further? Both columns are indexed So I was assuming that it should run faster.
The query is,
...
0
votes
1answer
12 views
Select Top product from multiple tables combine the duplicates products and SUM the quantity
I a have multiple sells lists tables i want to select the top product of each table (the higher quantity value) and "the validated sells only in their master tables" from each sells list table then ...
2
votes
1answer
14 views
Partial Pivoting in PostgreSQL or Rails
Assume I have 3 tables, and I can't change any DDL
Class
id: integer
name: string
and
Student
id: integer
name: string
class_id: integer //Foreign key to Class table
and
Score
id: integer
...
0
votes
1answer
26 views
Why does Postgres not recognize my DST?
In my postgresql.conf file, my default timezone is set to 'Europe/London'.
Since the DST will be switched off on 30th October, this means that now (4th October while I am writing) 'Europe/London' ...
0
votes
1answer
19 views
How to full text search more than one “model” field using Ecto and PostgreSQL
I'm using this search function in my controller:
def search(query, search_term) do
(from u in query,
where: fragment("to_tsvector(?) @@ plainto_tsquery(?)", u.name, ^search_term),
...
0
votes
1answer
26 views
Can't start my postgres server
When I try to launch my psql server, I receive a postgres popup saying:
There is already a PostgreSQL server running on port 5432. Please stop
this server before starting Postres.app. If you ...
0
votes
1answer
9 views
Can't authenticate user “ambari” in ambari-server setup in PostgreSQL
I am setting up the Hortonworks Hadoop stack on a single instance RHEL 7 node. I am stuck in the part where I am setting up my ambari-server, using my PostgreSQL 9.2.15 database (not default, not ...
0
votes
1answer
13 views
Docker Postgres data volume on the host
I've a simple tiny issue. I have an app (ASPNET Core) and db (Postgres). I run the container of the db first and then run the app container so that the app can discover the db at runtime. Now I can ...
1
vote
1answer
19 views
How to speed up Django's admin pages with PostgreSQL count estimates?
It's quite known that Django's admin list views are getting rather slow when the database tables have lots of rows. That's because the Django paginator uses by default a (slow) PostgreSQL COUNT query.
...
0
votes
0answers
9 views
Any way to execute the schema.sql (or concrete schema-postgres.sql) after “schema from model” creation?
Is there any way to tell the Spring Boot to execute its schema.sql or in my case schema-postgres.sql after creating the database model from object model?
The relevant part of my application....
0
votes
1answer
12 views
Error ERROR (undefined_table) in a full text search with Ecto and PostgreSQL
I'm trying my implement a very basic full text search in my Phoenix application using Ecto and PostgreSQL. Based on one example and PostgreSQL docs I have in my controller:
def search(conn, %{"q" =&...
1
vote
2answers
20 views
How to count string in column with select unique string by other column
Trying to solve the problem find that, but it is not what i need...
select distinct column1, count(column2) from table group by column1;
column1 column2
aaa type1
bbb ...
0
votes
0answers
20 views
Array type column in Database [duplicate]
I am storing some data in a table. I have to use the datatype real[]. How do i access the elements or is there a special method to get the array from the table?
i am trying to access the single ...
1
vote
0answers
12 views
generate_series in postgresql no result data in array php
I was trying to retrieve a data result provided by the SQL below
SELECT to_char(generate_series('2016-10-04', '2016-12-31', interval '1 mon'), 'YYYY-MM') AS month
If we can run the said query above,...
0
votes
0answers
16 views
UPSERT into multiple tables using postgres
I have the following 3 relations:
Job (number)
Phase (job_id, number)
Category (phase_id, number)
I am reading CSV file with data that is represented in the following table:
This CSV file can be ...
0
votes
0answers
11 views
Persistent Data when Scaling Postgres with Citus and Docker Compose
I am trying to run a scalable PostgreSQL database cluster using Citus and Docker. I am following the instructions from the readme in the Citusdata Docker repository.
With that, I can successfully ...
0
votes
0answers
9 views
Rails SSE with postgresql notify
How can I use Rails 4 with Server sent events and postgresql notify with different callbacks (not only one callback - https://www.sitepoint.com/mini-chat-rails-server-sent-events/): create, update and ...
0
votes
1answer
11 views
How to create Sum Partial Payment and Outstanding Payment
I have 2 table like below:-
Table: Invoice
InvDate InvNo InvAmount
01/01/2016 T0001 3000
02/01/2016 T0002 8000
Table: Payment
ResDate ResType ResNo ResAmount InvNo
01/01/2016 C ...
0
votes
1answer
9 views
pgadmin4 - New install not working
I downloaded the postgresql-9.6.0-1-linux-x64.run package and ran through the installer on ubuntu 16.04. Postgres is working fine. I am trying to use the pgadmin4 package that was included with this ...
-3
votes
1answer
25 views
What are different types of sleep function in different web databases?
I want to know what(syntax) sleep functions are used in different types of databases,
for example mysql has sleep(n).
1
vote
1answer
23 views
How to get distinct prices by product with single row per product
Order contains same product with different prices.
How to get list of distinct prices per product in order, with one row per product?
I tried
SELECT product, string_AGG(DISTINCT price::text, ',' ...
0
votes
1answer
22 views
Can we update two tables in 1 query postgresql
I am using postgres as my database and have 2 tables
accounts
id | name | status
accountOwner
account_id | user_id
I have user_id(accountOwner table) and status(accounts table) to be updated ...
-1
votes
1answer
13 views
What does pg_basebackup do?
I perform some steps to do PITR by referring https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/ site.
I would like to know what pg_basebackup actually does? ...
0
votes
1answer
24 views
Find specific data within 365 days
I have a table that has various historical data on where certain people worked and lived over a 50 year period. Data is sporadic and not yet complete. I want to find each instance when there is a work ...
0
votes
2answers
33 views
Getting results from two different tables using a join
Let's say I have the following tables:
+-------------------------------------------+
| t_classroom |
+-------------------------------------------+
| PK | id ...
1
vote
1answer
7 views
postgresql insert timestamp error with python
I use psycopg2 for postgresql. Here is my snippet:
a = "INSERT INTO tweets (Time) VALUES (%s);" % (datetime.now(),)
cursor.execute(a)
this won't work and gives me an error:
ProgrammingError: ...
1
vote
2answers
22 views
Node / Express & Postgresql - when no rows match
Hello I am new to Postgresql and I wanted to learn how one handles 0 results as an error is thrown. Essentially I want to get a user if it doesn't exist, return null if one doesn't, and have an error ...
0
votes
1answer
6 views
How to return a value inside a postgreSQL PL/pgSQL EXECUTE
I am trying to return the insert id from within EXECUTE. This works fine:
CREATE OR REPLACE FUNCTION public.mytest2()
RETURNS INTEGER
LANGUAGE plpgsql
AS $function$
DECLARE
retval INTEGER;
...
1
vote
0answers
10 views
Removing query decorations from psql output in PostgreSQL
I have an SQL file which extracts data from a PostgreSQL database in a particular format. However, I'm getting query "decoration", specifically a "SELECT N" output when creating my temporary tables.
...
0
votes
0answers
13 views
SUM the results of GROUP BY whilst using the IN operator with PostgreSQL
My query:
SELECT "Tracks"."PageId", date_trunc('month', "Tracks"."createdAt") AS month
FROM "Tracks"
WHERE "Tracks"."PageId" IN (1,2,3)
GROUP BY month, "Tracks"."PageId"`)
However, this yields:
[...
0
votes
0answers
9 views
Use Letsencrypt certificates for Postgresql
I have successfully configured Letsencrypt for my NGINX webserver on Debian 8 Jessie.
Now I have installed Postgresql 9.4 and want to use the LE certificates (in /etc/letsencrypt/live/mydomain/com/......
0
votes
1answer
14 views
Create Postgres Dump from Local .sql file
I want to create a .dump file from a Postgres .sql file I have locally. I don't want to connect to the database to do so, I just have a local file (or remote, wouldn't really matter), that I want to ...
0
votes
0answers
10 views
How to view the generated/resolved query executed by pg_query_params?
I'm using pg_query_params and would like to log the query executed by the server, not just the string sent separately of the parameters (with the $1, $2 placeholders).
$result = pg_query_params($...
1
vote
1answer
20 views
How to use an aggregation key with null value and with UPSERT?
I have a problem using UPSERT in Postgresql 9.5.
I have a table with 50 columns and my aggregation keys contains 20 keys, of which 15 of them can be null.
So this is my table :
CREATE TABLE public....
0
votes
0answers
19 views
Error running development server with Postgres user in Django
Switching from user "postgres", I created a new Postgres user and changed my Django project to make use of this new user / credentials:
sudo -u postgres -i
psql -c "CREATE USER foo WITH PASSWORD '...
0
votes
0answers
16 views
Custom Postgres Update Function
I have a table which contains multiple rows for a user, holding their station_ids. When a station ID is changed from the front end via drop down button, I want to update the station ID in the table. I ...
0
votes
0answers
34 views
How to find changed prices in last two purchase invoices
Postgres 9.1+ holds purchase invoices in two tables:
Invoice headers:
create table dok (
dokumnr serial primary key,
invdate date not null
);
Invoice rows:
create table rid (
id serial ...
1
vote
1answer
15 views
Set up hostname for remote server pyqt
I have made a postgres database in my computer. (Linux Mint 17)
Then, I have made a front end script for manage it in pyqt. In this script I set up the host name with localhost, like that:
self....
0
votes
2answers
12 views
convert pgsql int array into python array
I have numeric data (int) stored in pgsql as arrays. These are x,y,w,h for rectangles in an image e.g. {(248,579),(1,85)}
When reading to my python code (using psycopg) I get it as a string (?). I am ...
0
votes
1answer
10 views
Using Lag() function to retrieve values across dates
I am trying to use the LAG() and LEAD() functions in postgres to retrieve values from other rows/records in a table and I am running into some difficulty. The functionality works as intended as long ...
0
votes
0answers
8 views
Speed up insert of json into Postgresql
Does it make sense to set the type of a column to text instead of jsonb when inserting a lot of records and change it back afterwards?
I'm assuming Postgres parses json when inserting text into a ...
1
vote
1answer
8 views
Double checking daylight savings rules on PostgreSQL server are up to date?
We run PostgreSQL, interfacing with source systems that store timestamps without time zones, fixed to 'America/Los_Angeles'. We convert those to UTC and store them with the UTC time zone (of course).
...
0
votes
1answer
11 views
Rail postgres array contains (<@) dont include nil
I have a Matchups table that includes a field that is equipment_id and stores an array
I am trying to search the Matchups table to get any records that have an array where it contains specific ...
