Questions tagged [postgresql]

43056 questions
1

votes
1

answer
238

Views

Partitioning table based on first letter of a varchar field

I have a massive table (over 1B records) that have a specific requirement for table partitioning: (1) Is it possible to partition a table in Postgres based on the first character of a varchar field? For example: For the following 3 records: a-blah a-blah2 b-blah a-blah and a-blah2 would go in the 'A...
Winker
1

votes
0

answer
349

Views

Python 3.5.x multiprocessing throwing “OSError: [Errno 12] Cannot allocate memory” on a 74% free RAM system

I wrote a Python 3.5 application that spawns several processes using multiprocessing library. It runs on Ubuntu 16.04.3 LTS on a dedicated server machine that ships with 2 x Intel Xeon E2690 v1 (8 cores each) and 96GB of RAM. This system runs a PostgreSQL instance which is configured to use a maximu...
pietrop
1

votes
0

answer
306

Views

Connecting to Azure database for PostgreSQL from R

I created a PostgreSQL server on Azure, but I am not able to connect to it from R. I am able to connect to it using pgadmin though drv
let_there_be_light
1

votes
0

answer
114

Views

PostgreSQL: Index is used - but not when querying with LIMIT OFFSET

Let's say I have a messages table in a PostgreSQL database that contains many entries. The primary key of the table is just a simple serial column (like int but with 'auto increment'). Then I want to select the TOP-25 messages that a user received and that meet certain criteria. The result is ordere...
MrSnrub
1

votes
0

answer
55

Views

NHibernate - How to query entities with children using fetch=“join” and Transformers.DistinctRootEntity but selecting only some properties

I would like to select entities with its children using fetch='join' strategy in mapping - due to large amount of records in dataase. The query looks like this: IList questions = Session.CreateCriteria(typeof(QuestionForVisit)) .Add(Restrictions.Eq('IsTechnical', 0)) .CreateCriteria('ParentVisitMo...
Lukasz
1

votes
1

answer
193

Views

Lemur + PostgreSQL - can't render element of type

I'm trying to locally deploy a Netflix open project locally using the following documentation https://media.readthedocs.org/pdf/lemur/latest/lemur.pdf (page 46) and following these steps: git clone git://github.com/netflix/lemur.git # Create a python virtualenv mkvirtualenv lemur # Make the magic h...
David Faiz
1

votes
0

answer
52

Views

Unsupported index types through migrations. Schema:update wants to remove some

I have a symfony 3.4.2 + doctrine 2.6 project using postgresql 9.6. I am trying to add some unsupported index types. I found this issue which points out that migrations should be used for this purpose. My problem is that the migrations work for 2 out of 3 indices. But doctrine:schema:update wants 3r...
Evren Yurtesen
1

votes
1

answer
462

Views

ERROR schema does not exist

CREATE OR REPLACE FUNCTION analyzeTables() RETURNS INT AS $$ DECLARE v_rec information_schema.tables%ROWTYPE; BEGIN FOR v_rec IN SELECT * FROM information_schema.tables mytable WHERE mytable.TABLE_SCHEMA = 'public' AND mytable.table_type = 'BASE TABLE' ORDER BY mytable.table_name LOOP ANALYZE v_rec....
Bernard Craddock
0

votes
0

answer
3

Views

Django+PostgreSQL Not Able to Fin Object From DB Right After Create It

I was testing User creation by creating a TestCase, and it couldn't be found right after created it. I've tried to flush cache by calling .refresh_from_db(), but it doesn't work. Here is my TestCase: class SuperStrangeTest(TestCase): def test_super_strange(self): john = User.objects.create() john.re...
Cai
1

votes
0

answer
84

Views

Restore .sql.xz file to postgresql database in docker

First, I follow the postgresql instruction on docker: docker run --name test-postgres then i tried to restore my db by the following command: xz -dc matrix-ww.sql.xz | docker exec -i test-postgres psql -U postgres matrix --set ON_ERROR_STOP=on --single-transaction the command seems executed. however...
Rizary
1

votes
0

answer
746

Views

How to set pgpassword and run postgres command in one line using Python?

I'm trying to set pgpassword and run pg command in one line but not working, I'm using Windows. os.system('SET PGPASSWORD=admin && psql -U postgres -c 'CREATE DATABASE TEST'') what I got: psql: FATAL: Ident authentication failed for user 'postgres' It's weird because it's the password How can I so...
User100696
1

votes
1

answer
61

Views

Desktop admin panel for web app

I'm building a eCommerce web application for shops using Laravel/Foundation/PostgreSQL, and it will provide them (shop owners) with web based admin panel. My question is, can I make the super admin panel (the tool that I use to control everything) using desktop technology such as VB.net (or anything...
Ahmad Issa
1

votes
2

answer
930

Views

Find all customers who have an account at all branches located in the same city they live in

I have the following tables. account(account_number, branch_name, balance) branch(branch_name, branch_city, assets) customer(customer_name, customer_street, customer_city) loan(loan_number, branch_name, amount) depositor(customer_name, account_number) borrower(customer_name, loan_number) Crude visua...
HiveMindize
1

votes
0

answer
62

Views

Maintaining a graph and getting path lengths of all the nodes in SQL

I have a simple system that maintains the friendships of people. for example, something like this: | id1 | id2 | ------------- | 1 | 5 | | 5 | 7 | | 3 | 7 | | 2 | 1 | Where id1, id2 are 2 ID's of 2 different people, respectfully. This relation represents a graph where each id is a no...
Loay
1

votes
1

answer
338

Views

How can i know which query takes long time while running a postgres function?

Actually i am running a function in postgres which takes 1123 + ms to execute the function. That function consist of calling other function and have many query to execute . How can i know which query is culprit for slow execution of function . I have seen . select * from pg_stat_activity; give the o...
Himanshu sharma
1

votes
0

answer
986

Views

postgresql - Error: Connection terminated

I am trying to add data to my empty table. In the following codes, I was able to log the client. That means connection to the database was successful. However, when it went to the first client.query, it gave me the error: Connection terminated. The database was left untouched. I log the result and r...
scabiosa
1

votes
1

answer
439

Views

creating pg_cron extension within docker-entrypoint-initdb.d fails

If I create the pg_cron extension in a docker-entrypoint-initdb.d/init.sql file, the docker image fails to run and docker logs just says 'No such container.' Here's the relevant .sql snippet: CREATE DATABASE my_database; \c my_database; CREATE EXTENSION IF NOT EXISTS postgis CASCADE; CREATE EXTENSI...
Alex Leung
1

votes
0

answer
190

Views

how to continue with uploading a csv file to postgres using flask+panda

i am new to python and postgres and am trying to build a simple flask web app. a user can choose a csv file, press upload, and it will create a table in postgres on my server with the table in the csv. a simple copy paste. my html code: upload my python module: from flask import Flask, render_templa...
Tal Alterzon
1

votes
0

answer
288

Views

ERROR: missing FROM-clause entry for table “xxx” in Postgres

Facing some trouble with the query below and looked for options to nullify the error but couldn't succeed. Maybe the joins are not proper, and the query may not be in it's best optimized state too. Will be glad if you could help with this: SELECT DISTINCT hpd_help_desk.contact_company, hpd_help_desk...
K Singh
1

votes
0

answer
164

Views

Pandas, Postgres, and Daylight Savings Time (DST)

I am using read_sql_query from Pandas vesion 0.22.0 to pull time series data from a local PostgreSQL database. If I do not parse the date columns, then I get the following data frame: dataid localminute use 0 1642 2012-05-11 19:00:00-05:00 0.827 1 1642 2012-05-11 19:01:0...
davidrpugh
1

votes
0

answer
676

Views

“Query compilation failed” for redshift query

I'm using psql (postgresql 10.1) to access a table on AWS/redshift. The following query gives me a very cryptic error: => select to_timestamp(json_extract_path_text(data, 'scheduling_at'), 'YYYY-MM-DD'T'HH24:MI:SS.MS'Z'') as ts from my_table; ERROR: Error: DETAIL: ----------------------------------...
Luis
1

votes
1

answer
268

Views

Add A New Field To Existing Model - django.db.utils.ProgrammingError: column {field} does not exist

models.py I have a Scorecard model that has a ManyToManyField to another model named Account: class Scorecard(models.Model): .... name = models.CharField(max_length=100) accounts = models.ManyToManyField(Account) ... def __str__(self): return self.name My Account class currently has four fields (acc...
Jarad
1

votes
0

answer
29

Views

AWS Postgres RDS Disk Spikes, randomly going down to 0

We have run out of possible answers for this, initially we thought this was due to MVCC and vacuum, but our table sizes are < 10 GB, and there are < 20 tables.
Joddy
1

votes
1

answer
137

Views

Update Case-Sensitive DB Field In Laravel 5.3 With Postgres

I am trying to update a database column field with raw SQL in laravel. It's important to mention that the update code was written to MySQL drive but now I use Postgres. The column name is dayID. So the update code is: DB::update('update table set travel = ... WHERE dayID = {$this->dayID}'); I must u...
CodeL
1

votes
0

answer
68

Views

How to filter objects in django based on json field where a key is list and based on number of list items mathched for each object?

I have a an object named canditade. class Candidate(models.Model): name = models.CharField(max_length=64) skills = models.JSONField() I have created objects like this Candidate.objects.create(name='mark',skills={'list':{'name':'python'}, {'name':'java'}, {'name':'sql'}, {'name':'django'} }) Candidat...
Arbaz Hundekar
1

votes
1

answer
95

Views

How do I use lag function to skip a row? PostgreSQL 9.3

I am currently trying to write a query from a table that list out users surfing behaviour. The table looks like the one below **RecordID RespondentID DeviceID UTCTimestamp Domain** 1 01faca75-1216-4a55-b43c-9d64ade852f7 4DF57C06-F0BD-4779-8983-37A8B02E5EDF 06/11/2017 10:21 goo...
user2315860
1

votes
0

answer
342

Views

CDC change data capture start time - Postgres replication

I'm using AWS DMS for Postgres-postgres migration. For on-going replication for other engines there is a parameter CDC start time where we can specify the start time of picking up up changes for replication but unfortunately postgres does not support that parameter. By default, my assumption is when...
Ramya
1

votes
0

answer
21

Views

Getting same value in all rows

Can someone please tell me what wrong I am doing with the query. select cr.name, count ( p.user_id) from profiles_profile p inner join profiles_contact pc on p.contact_id = pc.id inner join location_address la on pc.address_id = la.id inner join cities_country cc on la.country_id = cc.id inner join...
Rock
1

votes
1

answer
212

Views

Why does hibernate save two @OneToMany lists in one table?

Imagine having following simplified code using Hibernate and JPA: @Entity class C { @Id @GeneratedValue public long id; } @MappedSuperclass abstract class A { @Id @GeneratedValue public long id; @OneToMany(cascade = CascadeType.ALL) public List list1; @OneToMany(cascade = CascadeType.ALL) public Lis...
Dracam
1

votes
0

answer
479

Views

Spring JDBCtemplate ROW Mapper is too slow for a little records

I'm using Spring, postgresql and trying to run a query by JDBCtemplate ROW Mapper. I Have around 1 million records in my db and from the query returned only 12 records. When I'm running this query on the db its taking less than a second but when I'm using JdbcTemplate with a RowMapper its taking aro...
Tal Shani
1

votes
0

answer
128

Views

“AttributeError: 'psycopg2' has no attribute '__version__'” when switching from sqlite to postgres with Django - Vagrant

I'm a newbie in Django/Python. I just setup Django successfully with vagrant on my computer. But I want to change the default database from sqlite to postgresql. To do so, I just changed the database section on the setting.py file to : # Database # https://docs.djangoproject.com/en/2.0/ref/settings...
kabrice
1

votes
0

answer
667

Views

unterminated dollar-quoted string at or near "$BODY$

I do not know why I am getting the following error message: unterminated dollar-quoted string at or near '$BODY$ Here is my SQL query: CREATE OR REPLACE FUNCTION cdr_insert_function() RETURNS trigger AS $BODY$ BEGIN if (new.call_date >= '2014-01-01' and new.call_date = '2015-01-01' and new.call_dat...
LunchBox
1

votes
0

answer
569

Views

PostgreSQL equivalent to SQLite ROWID

With the new IDENTITY type in Postgresql 10, I wonder if there is anything similar to ROWID in SQLite which is a pseudo-column to get the primary key. I want to do something like: SELECT ROWID FROM Table in Postgresql and get the primary key. Edit: This is not a duplicate of this question because RO...
antoyo
1

votes
1

answer
230

Views

SQLAlchemy scoped_session is not getting latest data from DB

I'm rather new to the whole ORM topic, and I've already searched forums and docs. The question is about a flask application with SQLAlchemy as ORM for the PostgreSQL. The __init__.py contains the following line: db = SQLAlchemy() the created object is referenced in the other files to access the DB....
SharedObject
1

votes
0

answer
46

Views

Using Ruby on Rails 5, how do I define a URL route with unknown/:folder/:sub-folder depth and save/recall it from Postgresql?

In my application, users will be able to create categories and I would like the 'breadcrumb' to be visible in the URL, i.e. www.domain.com/user/:user_id/folder/:top-folder-slug/:sub-folder-slug/:sub-sub-folder-slug I do not intend to have a limit on the levels of sub-folders a user can create so I'm...
chrickso
1

votes
3

answer
214

Views

SQL Make every n row to a seperate column

i have a little question. My problem is that i have a table with a single column with sensor data. Every third row represents a new sample. My target is right now to transform every third row to a seperate column like a 3 x N matrix. I dont know how big my N becomes. Is it possible to do this with S...
bla93
1

votes
0

answer
274

Views

Triggers on PostgreSQL Streaming Replication Standby

I have a primary and a standby PostgreSQL 9.4 database. They replicate via streaming replication. I would like to have the standby database notify an application when it is changed (i.e. replays an xlog). Similar behavior is possible on the primary database by creating triggers on each table that ex...
Brayden Willenborg
1

votes
0

answer
26

Views

How do i return number of empty array's based on the limit in PostgreSQL?

Am looking for a solution here are the details Assume : req.params.collage_id = 1; As i have only 2 records found for collage 1, so only 2 records are displaying. instead of this , the empty braces has to be displayed if there is no data, please help me out here. db.query('SELECT * from students wh...
Schüler
1

votes
0

answer
151

Views

Ruby on Rails ERB Form for a Hash or field in a Postgres Hstore

in my ruby on rails project, I am using a Postgres hstore field called plugin_settings to store a hash of all my plugin settings dynamically. This way if a new plugin in written, no migrations need to be written in order to save the plugin settings on my ActiveRecord object. The hash is currently Pl...
Scott B
1

votes
1

answer
57

Views

How does one join a varchar to an integer

For my homework, I have been given the problem where I need to write a query to join the following two tables to give me an output table like below: select order_number, Menu_Items from d.orders join d.menu as m on order items = menu items Although I feel this is the correct approach I believe it...
fire2018

View additional questions