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
3 views
Heroku, Rails: PG::SyntaxError
When loading the schema in my Rails 5.1 application on heroku, the following exception gets raised:
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "ENGINE"
LINE ...
0
votes
0answers
2 views
How can I replay web requests on a production clone to control a release candidate?
I have a critical Rails app running in production with a fairly standard setup: Rails 5 API, Puma, PostgreSQL, Sidekiq, Heroku. I also have a branch that involves risky database migrations and ...
1
vote
2answers
18 views
Postgresql - Opposite of string_agg
I'm looking for a postgresql function that will do the opposite of string_agg.
I have a movies table where the tags column contains values such as
Action|Adventure|Drama|Horror|Sci-Fi
Action|...
0
votes
0answers
12 views
Better practice in storing files [duplicate]
I have a theoretical question, whether is it good practice to store files in the form of base64 in the database, or better save them physically on the disk and store path (indicator) to these files in ...
0
votes
0answers
6 views
How to cast pointZM geometry field to point in postgresql?
I have a table A with geometry(PointZM, 80001) type and another empty table B with geometry(Point, 80001) type and I want to insert data from table A to table B and I need to do a cast.
How can I do ...
0
votes
0answers
11 views
exclude through attributes in sequelize
I have 2 tables post and tags. I'm using Tag to get all the posts associated with it.
models.Tag.findAll({
attributes: ['tagName'],
include: [
{model: models.Post
attributes: ['content']
...
0
votes
0answers
14 views
DataIntegrityViolationException should not terminate Spring Boot batch job
I have been following this tutorial : https://dzone.com/articles/spring-batch-tutorial-spring
In this batch job I am reading from a csv file (1.5 GB) and insert/update into a table named B. I use ...
0
votes
0answers
20 views
How to create and save new POJO object based on another one requested from Hibernate+Java
I do have a Java Web Application. It does use hibernate for creating, updating, saving the web form data to database. The task is to create a new record in the database within the same table based on ...
-1
votes
0answers
15 views
Insert bulk data automatically into postgresql [duplicate]
class Tester():
def __init__(self,count):
execute(SETUP_SQL)
self.count=count
self.data=[
{
'upo_user_id':%s,
'upo_last_modified': ...
0
votes
0answers
9 views
npm pg-generator error: domain information_schema
I tried to gen models from postgre local with this command:
pgen exec sequelize-template -d helloworld -u postgres -p etvp@#4312 -t model --optionsfile options.js
However I got an error sayings:
...
0
votes
1answer
19 views
PostgresSQL foreign key constraint violation with inheritance
I have a PostgreSQL database, 3 tables and my schema as follows
--- Parent Table
CREATE TABLE IF NOT EXISTS abc.parent(
record_id SERIAL PRIMARY KEY,
description text NOT ...
0
votes
3answers
30 views
Fastest way to anonymize and sanitize field in db table?
For research purposes (e.g. outsourcing to 3rd party data scientist community group), I need to export production db, masking certain sensitive fields (e.g. customer name, phone number, address, etc.)....
2
votes
1answer
27 views
Unexpected deadlocks in Postgresql (while using psycopg2)
I am dealing with a deadlock issue in PostgreSQL that I do not understand.
I am trying to implement a Round Robin-like algorithm using Python, psycopg2 module, and a Postgres database.
I want several ...
1
vote
1answer
39 views
Cast type given a type name
I have this json structure where i also store a PostgreSQL type name
{
"temperature":{
"value":"(-75,-70]",
"type":"int4range"
}
}
i can get the value such as
SELECT '{"...
0
votes
1answer
37 views
How to group by a regular expression in a postgres query
I'm cleaning up someone else's restful application, and in doing so it appears that there are some routes that aren't being used. To begin troubleshooting, I created a table with a unique text column ...
-2
votes
0answers
9 views
PostGIS Shapefile DBF Loader 2.2 in pgAdmin III greyed out - why?
I have two PostgreSQL 9.5 instances. In one, the pgAdmin III plugin "PostGIS Shapefile DBF Loader 2.2" works when I highlight my database. In the other instance that plugin menu choice is greyed out....
0
votes
1answer
16 views
Replacing nested SELECT
How can I make postgreSQL query like this:
SELECT event_id, user_id FROM public."point"
WHERE user_id = (SELECT id FROM public."user"
WHERE email='[email protected]')
with JOINstatement and without ...
0
votes
1answer
16 views
reading a txt file: Permission denied - postgis
I get this error while trying to copy some data from a txt file to a table in a database, in postgis/postgres... (I am using windows 10).
ERROR: could not open file "C:\Users\Luchito\Desktop\...
1
vote
2answers
21 views
3 level nested sorting
I'm trying to implement a 3 level nested sort. Basically I have four columns:
A | B | C | D
--- | --- | --- | ---
bob | GOOD| 1 |
kat | BAD | | 24
bob | OK | | 15
bob | GOOD| ...
0
votes
2answers
12 views
guarantee uniqueness of table relationship
So I have a table of states.
CREATE TABLE public.states(
state_code char(2) NOT NULL
);
I have several different ways of grouping those states into regions, so I came ups with this schema:
...
0
votes
0answers
27 views
PHP not work with postgresql?
I already check all 7 similar questions on this site but can't solve the problem
I have:
Operating System CentOS Linux 7 (Core)
WEBserver nginx version: nginx/1.8.1
PHP 5.4.16 (cli) (...
0
votes
0answers
10 views
How to add pgxx to cmake project
I am trying to compile script from tutorial https://www.tutorialspoint.com/postgresql/postgresql_c_cpp.htm
("Connecting To Database")
it seems dumb, but:
g++ test.cpp -lpqxx
works
...
0
votes
0answers
19 views
PostgreSQL error using JDBC
I am getting an error trying to connect to a postgresql database through an android project.
The error is "Caused by: java.lang.VerifyError: org/postgresql/jdbc/TimestampUtils"
I could not figure ...
android
1
vote
1answer
31 views
JDO, org.json.simple.JSONObject and PostgreSQL JSON type
In my PostgreSQL database I have:
CREATE TABLE category (
// ...
category_name_localization JSON not null,
);
In Java, I have a JDO class like so:
@javax.jdo.annotations.PersistenceCapable(...
1
vote
0answers
17 views
Adding multiple phrases together in phraseto_tsquery
I have been successfully able to concat arrays of single words into strings for to_tsquery but phraseto_tsquery in postgres 9.6 only allows one keyword phrase. Does anyone know of a solution to query ...
0
votes
1answer
11 views
PostgreSql: how to do where CURRENT_DATE - timestamp > (Value of years/months/days)
May I ask, in PostgreSql, how to do
where CURRENT_DATE - timestamp > (Value of years/months/days).
Here I'd like to calculate the age of a person and verify if it is older or younger than a ...
0
votes
0answers
26 views
How to map a value in table-A to a column in table-B in postgresql
I have table-A as below (Original Image):
+ ---------------------------- + -------------------------- + ------------- + --------- + ---------- + --------- + ---------------------------- +
| id ...
1
vote
0answers
32 views
node-postgres simple SELECT is becoming extremely slow (PostgreSQL)
I'm developing an server-side app for mobile game backed with postgresql and I'm using pg with Knex("pg": "6.1.2" and "knex": "0.12.6"). Not so long ago I faced a problem with select perfomance ...
0
votes
0answers
17 views
Flask Admin on postgres database view
Is there a way I can create admin view on Flask-Admin on a database view? I'm using Postgres, and have created a view, let's take the below one as example:
CREATE VIEW test_view AS (
SELECT id, ...
0
votes
1answer
19 views
Why and when should Read/Write Splitting in MySQL and PostgreSQL?
I am a fresher for database tuning, I am trying to learning improve my PostgreSQL database performance, So I search lots of articles in google
There are many advices are Read/Write Splitting for ...
0
votes
1answer
24 views
postgresql Remove trailing zeroes
I have a query which is proving to be cumbersome for me. I want to remove the trailing zeroes from the result.
Remove trailing zeros from decimal in SQL Server
Remove trailing zeroes using sql
...
0
votes
0answers
22 views
Provide sync between PostgreSQL and NoSQL database
I've got a PostgreSQL DB with very normalized data, so a lot of requests spawn a lot of joins and my DB works slow. I want to denormalize data from PostgreSQL and store it in a NoSQL DB for readonly ...
0
votes
1answer
17 views
laravel db postgresql columns with quotes
I have a website (Laravel & AngularJS) which used MySQL.
Now i'm trying to change to Postgres, the database is created and accessible (with all the columns in lowercase which i can't change ...
0
votes
2answers
44 views
access - row_number function?
I had this query, which gives me the desired results on postgres
SELECT
t.*,
ROW_NUMBER() OVER (PARTITION BY t."Internal_reference", t."Movement_date" ORDER BY t."Movement_date") AS "cnt"
FROM (...
0
votes
1answer
12 views
JBoss CLI escaping SELECT 1
Using JBoss (EAP 6.4) CLI to deploy, and at this point, want to add a datasource.
The cli command is like this
/profile=XXX/subsystem=datasources/data-source=XXX:add\(connection-url=XXX,url-...
0
votes
2answers
15 views
PHP - Is there PostgreSQL equivalent to mysqli_next_result()
I'm converting some PHP from using MySQL to PostgreSQL. Is there an equivalent function for PostgreSQL to mysql_next_result()?
0
votes
0answers
18 views
Sequelize not storing additional Join Table Fields
I have two models with a many-to-many association. I've created an additional model for the join table so I can store data with the join table.
Now the association works fine but the data which ...
1
vote
0answers
49 views
Query takes 60 times as long after change of value
I try to run 2 queries, difference only in where clause where I change state from 'CA' to 'FL'. And execution time increased 60-fold Can You point me in right way, why two symbols changes query time ...
0
votes
1answer
42 views
“Complex” query with multiple conditions (BEGINNER)
I'm basically new to (postgre)SQL and have to write ONE query for following scenario. I bet it's almost laughable to the more experienced members here but please bear with me (I dont even have a ...
0
votes
1answer
31 views
Trigger to update a column on update
I have written this function and this trigger:
CREATE OR REPLACE FUNCTION test4() RETURNS TRIGGER AS $BODY$
BEGIN
UPDATE "cf"
SET vol=(area*alt);
RETURN null;
END;
$BODY$
LANGUAGE plpgsql
...
0
votes
0answers
22 views
testing active record associations on models Rails
I tried to seed a database to test associations with active record methods in the rails console. This wasn't a good approach as I would manually have to add all the references and foreign keys. This ...
0
votes
0answers
22 views
How do Postgres, MongoDB and Lucene compare against each other for geo-spatial keyword processing? [on hold]
How do Postgres, MongoDB and Lucene compare against each other for geo-spatial keyword processing?
Any past experiences would be much appreciated
0
votes
2answers
21 views
Driver:org.postgresql.Driver@3ed03652 returned null for URL… While deployin spring boot to Heroku
I try deploy my application on Heroku, but have some errors which I can't fix
my application.poperties
spring.mvc.view.prefix = /WEB-INF/view/
spring.mvc.view.suffix = .jsp
spring.datasource.url ...
1
vote
1answer
48 views
SQL delta calculation with varying records
I am calculating the difference of amounts between batch runs of calc_table and store it in delta_table. The number of records in calc_table per batch run can vary.
I can calculate the difference, ...
-1
votes
0answers
18 views
Postgres connect remote server passed authentication
When you try to connect to remote server postgres:
psql -h 10.10.10.2 dgrt postgres
I get an error:
User "postgres" has not passed authentication (Ident)
Configuration my files postgresql.conf:
...
0
votes
1answer
27 views
Filter JSON field django with greater than, less than and a range using _contains
Let's say that I have this model:
class Item(models.Model):
data = JSONField(blank=True, null=True)
And then in the console:
>>Item.objects.create(data={'size': 11})
>>Item.objects....
0
votes
2answers
26 views
SqlAlchemy ORM and aggregate functions
I am using sqlalchemy ORM to get data from Postgresql database and I am wonderer how you are doing a complex query with sqlalchemy ORM like this one :
select table1.name, Array(select name from ...
0
votes
0answers
11 views
Postgresql, create xmlelement with namespace
Could you tell me how to create a tag (xmlelement) with namespace? like :
<xs:attribute name="id">Some text
</xs:attribute>
0
votes
2answers
71 views
Why does my Clojure application take _minutes_ to connect to Postgres?
My project is using Docker Compose to create two services (app and postgres). Locally, app (Clojure application using Compojure, JDBC, Korma, Ragtime, etc.) connects to postgres immediately and ...
-1
votes
1answer
42 views
cut a postgres table into small tables
A big table is saved in a postgres database with the name EZDATA.
It contains 12 million rows.
I want to cut it into 20 small tables, 500000 rows each,
(EZDATA1, EZDATA2,...)
Can anyone help me ...
