Questions tagged [postgresql]

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 directed to dba.stackexchange.com.

0
votes
1answer
12 views

Sum all values in a column and group by 2 minutes

I want to sum all values in a column "value" and group by a interval of 2 minutes I have values like this: value TIME 0.3 2019-05-22 01:11:45---> first value 0,3 0.3 2019-05-22 01:12:16-...
0
votes
0answers
9 views

Why use SQLAlchemy to connect a postgres database in Flask instead of psycopg2? [duplicate]

I would like to know what are the advantages and disadvantages of using one method or another, thank you.
0
votes
0answers
4 views

Syntax error raw sql on rails when query inner results

how do you query raw SQL on rails? So I have this raw sql that I need to run on rails but giving me a syntax error. I also escape the extra parenthesis but still got a syntax error near the first ...
0
votes
0answers
5 views

Query based on jsonb column

I have a table that has a jsonb column (called info) which has two types of shapes: sometimes is an object and sometimes is an array of objects. I want to be able to get all the rows in that table ...
0
votes
1answer
8 views

Conditionally do jsonb_array_elements

I have a jsonb column called info that some type have the form of an object: { id: 2 } and some other types it has the shape of an array: [{ id: 2 }] I have a query that does this: SELECT * FROM ...
0
votes
1answer
14 views

Cannot eagerly load the polymorphic association :item

I need get all the accounts of the @client (has_many :accounts, as: :item is the relationship). Have a Account model with :item polymorphic field (belongs_to :item, polymorphic: true, nothing more), ...
2
votes
0answers
15 views

How to store table history in PostgreSQL

Scenario We need to store record history in PostgreSQL such that when a record is inserted into or updated in a primary table (e.g: pets), it is automatically backed up to a history table (...
1
vote
1answer
15 views

Is there a way to prevent insert for specific cases while without unique?

I'm setting up Postgres table to organize active store commissions in my company and I need to set a rule for preventing insert errors. I've tried to create a table without constraints and set the ...
0
votes
0answers
17 views

Does POSTGRES have a “autocomplete” query?

I have a question about Postgres. For example I want to query a column but I only fill in the first letters. Am I able to get all the results with these letters?
0
votes
0answers
33 views

Using a loop to export data

I have data with classes A, B,and C. I need to export each class separately to excel. Until now I have been running Select * From foo WHERE class = 'A'; and I would export the data and then change the ...
0
votes
1answer
19 views

Python( Django ) pip install psycopg2 error in MacOS

I set the virtualenv for my project and when I want to run pip install psycopg2 I get an error below ERROR: Complete output from command python setup.py egg_info: ERROR: running egg_info creating ...
0
votes
0answers
6 views

Unable to connect an app to IIS server running postgresql

I realise this will be a very vague post from the outset, I apologise. We have a new server running Windows server. It turns out it was not setup fully. It was missing the IIS webserver. I have set ...
1
vote
2answers
20 views

passing a variable to an ActiveRecord query

Let's say I have a query: Foo.where("lower(bar) LIKE ?", "%#{baz}%") which transforms to plain SQL as: ... WHERE (lower(bar) LIKE ... and works perfectly. But instead of bar I want to pass an ...
0
votes
1answer
25 views

Count occurences along with result using DISTINCT ON on PostgreSQL

I have data like this: [ {"name": "pratha", "email": "[email protected]", "sub": { "id": 1 } }, {"name": "john", "email": "[email protected]", "sub": { "id": 5 } }, {"name": "pratha", "email": "[email protected]", "...
1
vote
0answers
19 views

authenticate php sessions via the postgresql database

Problem My app is in the process of being converted from MySQL/MariaDB to PostgreSQL. And I'm having a hard time finding the resources on how to work with PHP sessions, when authentication is done by ...
0
votes
0answers
30 views

Is there any way to transpose columns into rows in Postgresql?

I would like to switch columns and rows in postgresql. Is there any way to do that considering that the date will keep increasing over time and therefore create more columns?
0
votes
3answers
17 views

Rails 5 left_outer_join with specific saved ID

I have a system with the following models class Application < ApplicationRecord has_many :offers, dependent: :destroy belongs_to :accepted_offer, class_name: 'Offer', ...
0
votes
0answers
9 views

Dbvisualizer view table relationship

I have a postgresql database with some tables. These tables have primary keys, but when I opened dbvisualizer, it didn't show me the relationships between tables. How can I get the result with ...
1
vote
0answers
26 views

Importing huge JSON file into DB

I have a 4GB JSON file that I need to import into a DB. The JSON is: { "transport_metadata": { "id": "81b18379105fa9626fde73b99bfa6cbac4d3618d", "source_system": "test", "build_version":...
0
votes
3answers
20 views

How to get the MAX(SUM of values) to find the category with the biggest total? PostgreSQL

I have two tables. One is Transactions and the other is Tickets. In Tickets I have the Ticket_Number,the name of the Category(Theater,Cinema,Concert), the Price of the Ticket. In Transactions I also ...
0
votes
0answers
10 views

DataGrip, PostgreSQL - Connection failed

I use DataGrip on Windows to work with PostgreSQL. Lately, however, I am getting this message Connection to PostgreSQL - [server]:5432 failed. [08001] Connection to [server]:5432 refused. Check that ...
0
votes
0answers
29 views

Nested FOR loop and EXECUTE

I have problems with this function. I don't understand why lower is not working inside the execute. It simply says that lower does not exists. tableA has a single column columna_columnb with ...
-1
votes
0answers
12 views

Can json store benefit from same structure/schema of stored json files?

Does any from json storage (mongodb/postgresql/mysql/…) store json fies more effectively if they are of the same schema/structure? Or is it more effective to store them in relational model and create ...
0
votes
1answer
25 views

Django doesn't convert the datetime using the right timezone

I'm struggling to figure out how timezone works in Django. On my systems I have all datetimes in UTC+00 even with a UTC+02 TIME_ZONE setting. timezone.get_default_timezone() ...
0
votes
1answer
19 views

Overwrite a column in Postgres from a column in a csv file

I have a large table, and I wish to update only a single column. The values for that column is present in a CSV file. I want to avoid a single insert to event column because it would take a long time. ...
0
votes
0answers
25 views

Bulk insert in migration not inserting data

As I've updated my postgres database scheme, I wanted to use a migration to migrate all existing data to the new tables in a migration, so it runs automagically when I deploy it to my host. ...
0
votes
1answer
23 views

Postgresql : Average over a limit of Date with group by

I have a table like this item_id date number 1 2000-01-01 100 1 2003-03-08 50 1 2004-04-21 10 1 2004-12-11 10 1 2010-03-03 10 2 ...
2
votes
0answers
15 views

Postgres closes connection during query after a few hundred seconds when using Psycopg2

I'm running PostgreSQL 9.6 and psycopg2 2.8.2. My PostgreSQL server (local) hosts two databases. My goal is to create materialized views in one of the databases that use data from the other database ...
0
votes
0answers
10 views

Import csv file into postgresql using specific row delimeter

I need to import a csv file into PostgreSQL table. My csv file has specific column delimeter and row delimeter set. I cannot use the copy command as I cannot specify the specific row delimeter that I ...
0
votes
1answer
8 views

How to pivot multiple columns in postgres

I have a table in Postgres that I want to pivot from wide to long on 2 columns. The data source is too large to be able to use Python as this would require loading to memory. Is there a function in ...
0
votes
0answers
8 views

(JetBrains) postgresql connection failed

I use JetBrains on Windows to work with PostgreSQL. Lately, however, I am getting this message Connection to PostgreSQL - ... failed. [08001] Connection to ... refused. Check that the hostname and ...
0
votes
1answer
22 views

jOOQ table generation from existing class

I need to create tables based on existing classes form the library using jOOQ. It's possible to use DB migration scripts (something like V1__first_script.sql) manually. The question: is it possible ...
1
vote
2answers
38 views

Calculate an average value from two columns af a same table

I have a table (ttable) which looks like this : id debut fin value 1 2 4 10 2 4 6 0 3 6 8 12 I would like to update the value ...
0
votes
0answers
12 views

Can not run migrate in CircleCI with knex migration

I want to Create a database for integration test by Circle CI and here is the .config.yml Circle CI config version: 2 # use CircleCI 2.0 jobs: # a collection of steps build: # runs not using ...
0
votes
1answer
19 views

How to make sql table column from jsonb int array

I have a table with jsonb array column like: name | arr --------------- n1 [1,2,3] n2 [4,5,6] I want to get a table from it like: name | element --------------- n1 1 n1 ...
0
votes
0answers
13 views

Outer join django Models . Many to Many relationship

I am unable to join the following tables I tried these things . But i am still unable to to join CartDetail and Products table on the basis of product_id I am using django 2.1.7 ''' Product ...
-1
votes
0answers
35 views

How to store zeros in db in rails

I have model with filed length - decimal , scale: 8, precision: 4. If I enter 5 in the field, there is 5 in the database. If I enter 4.4 in the database there is 4.4. But if I enter 4.500 in the ...
0
votes
2answers
29 views

How to convert postgreSQL to Sequelize format

I have to try to convert my postgre sql query to orm concept using sequelize npm package, kindly guide me. select * from "locationDepartmentMappings" as a inner join "departments" as b on a."...
0
votes
0answers
18 views

How to create a audit table for a given table to log all the changes to the table

How to create a audit table for a given table to log all the changes to the table For example, there is a table called person with 3 columns (id,name,age). I have to create a new table with name ...
0
votes
0answers
25 views

Dblink to insert values i remote database with postgres

I have to insert values of ABC into my_table in different DB connected via dblink (both tables have the same columns but are unknown) I have tried using record and cursors sql_str := 'select ...
0
votes
0answers
26 views

Spring Boot Application gets stuck on “Hikari-Pool-1 - Starting…”

I'm trying to run Spring Boot application connected to PostgreSQL database, but it just gets stuck and nothing goes on. HikariPool-1 - Starting... appears in logs and then nothing happens. And I ...
-1
votes
0answers
21 views

how to design a database to handle multiple select options where more than one option can be selected?

I am just starting out with PostgreSQL and have been thinking quite a lot about the design of my database. I have a table: CREATE TABLE activities ( id BIGSERIAL PRIMARY KEY, title ...
0
votes
2answers
18 views

Postgres - Repeating an element N times as array

For example, where the element is 'hi', and where N is 3, I need a PostgreSQL snippet I can use in a SELECT query that returns the following array: ['hi', 'hi', 'hi']
0
votes
0answers
14 views

execute_values only inserts one entry into database

Very new to Python. I am trying to parse an RSS feed and insert items into a database. My code is supposed to: fetch the xml, parse the feed, add selected items together and create 2 lists of the ...
0
votes
1answer
22 views

PostgreSQL. Insert valuse in junction table

Good day. I'm having some trouble with insert data in junction table. Here's my tables: users table ----------------------- |user_id |FullName | ----------------------- |1 | ...
0
votes
0answers
25 views

Way in asp.net core identity to transfer user credentials to different database without resetting passwords?

I'm setting up a new database (postgresql) and moving away from MongoDB for my db of choice using asp.net core 2.1 and the built-in identity engine. When I made the switch between the two databases, I'...
0
votes
1answer
22 views

PostgreSQL Update From Values Treats Nulls As Text [duplicate]

Need help in solving an issue when one of the values' columns contains only Null values. Example: create table foo ( id serial constraint foo_pk primary key, a int, b int ); insert into ...
0
votes
1answer
30 views

Group Json array data with multiple fields using CROSS JOIN LATERAL on PostgreSQL

This is jsonb field data: [ {'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 1 } }, {'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 2 } } ] When I want to get name and email fields, ...
-1
votes
1answer
12 views

Select specific field and get result from array in jsonb field

If I put this data to jsonb field: {'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 1 } } I was able to select name with this query: SELECT data_field->>'name' from users where .... ...
1
vote
2answers
50 views

PostgreSQL delete row if condition met, else update some value

I'm trying to delete a row based on a simple condition, if conditions are met, delete the row else update the row. Basically I'm tagging my data with is_deleted, so perform delete if is_deleted equals ...