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
1answer
8 views
Postgres: Trim white space from array values
My records in the table are as follows:
id column1
1 'Record1'
2 ' Record2'
3 ' Record3a, Record3b'
4 'Record4a , Record4b, Record4c '
column1 type: text
pre-defined array= {record1,...
2
votes
0answers
12 views
Partial update on an postgres upsert violates constraint
I want to be able to upsert partially inside postgres (9.5), but it seems that a partial upsert fails when not all of the constraint is fulfilled (such as the not null constraint)
Here is an example ...
0
votes
0answers
7 views
Join 2 Table in postgresql but the other table only will extract its columns
Is there a way in postgres when you join 2 Table but you only extract the columns of the first table using '*' symbol which means all of the columns of the first table.
0
votes
1answer
15 views
Set nextval sequence data type to integer only
I have an issues running around my mind regarding default for 'id' field in my postgresql database. Here is the syntax:-
nextval('unsub_keyword_id_seq'::regclass)
However I'm not really understands ...
0
votes
1answer
16 views
Why is my sum for two columns in psql incorrect?
I am trying to get the sum of two columns in psql
select id as id, name as name, count(winner_id) as winner_count,
(count(winner_id) +count(loser_id)) as total_matches from player left join
...
-1
votes
0answers
20 views
Ruby on Rails App production on heroku shows error
The Post what i create that created but doesn't show on show page but when i move to root at there i shows . There are alot functionality added to app but how could i know that will they properly work ...
0
votes
0answers
5 views
How should I connect to Omnibus Gitlab Postgres Database using PGAdmin?
We installed Omnibus Gitlab and would like to use the embedded database to run a few sql reports.
I am not very good with SQL commands and have used PGAdmin's graphical query builder in the past but ...
1
vote
2answers
27 views
Rails 5: Best way to iterate through a polymorphic model and find the top level parent?
I'm trying to implement a "most recent comments" for a user in their control panel. A comment can be left on a Post or another comment.
In my view I want to display the :body of the comment along ...
0
votes
0answers
3 views
Can I use SQLAlchemy Events to track external changes in a table?
I am interested in firing a callback when a table in a specific (postgres) DB is updated.
I've modeled said table via SQLAlchemy's declarative_base.
class SomeTable(declarative_base()):
...
0
votes
0answers
10 views
Embedded PostgresQL and “has no equivalent in encoding” on Windows 10
Wee have migration files using Flyway that can't be loaded into enbedded-postgresql (from yandex) during tests execution on Windows 10. On OS X they are working fine.
For example we are getting:
...
0
votes
1answer
10 views
How to translate WITHIN GROUP from Oracle to in Postgres?
I am trying to convert a subquery with a complex query from Oracle to Postgres. Below is the subquery and the error it gives. I know 'WITHIN GROUP' is also there in Postgres. What am I missing? I even ...
0
votes
0answers
4 views
Which type of data can be stored in Agens Graph (Graph database)?
I read the documentation about Agens Graph.
Your graph database is based on PostgreSQL, right?
So I think your solution is operating anywhere including general RDBMS features.
Then I have a question ...
0
votes
0answers
7 views
FluentPDO insertInto table with composite primary key
Method insertInto() return primary key of the new record. It is nice.
When I use FluentPDO with PostgreSQL, I have to use sequence name in method execute('sequence_name').
Otherwise, the method ...
0
votes
1answer
10 views
How to build an efficient select command across multiples tables in Rails4?
Trying to figure out an efficient ways to select records that have attributes across multiple tables. Here's the basic setup:
structure
Plants (fields: id, name_id, location_id, color) (1000 ...
0
votes
1answer
13 views
Wrong name of the column in Rails app with PostgreSQL database
I have association for my School model:
class School < ActiveRecord::Base
belongs_to :city
has_many :sclasses
has_many :users, through: :sclasses
validates :name, presence: true
...
0
votes
0answers
7 views
Get aggregate sum of json array in Postgres
How to get aggregate SUM(price) from "price" in PostgreSQL select Following is my data schema and structure:
Table Name: modifiers
create_table "modifiers", force: :cascade do |t|
t.text "...
0
votes
0answers
20 views
How to escape from use PreparedStatement.setNull?
I have a very big table in my DataBase. Some of they fields may be null.
I just want to escape from use the:
PreparedStatement.setNull(index, Type.X)
Like:
if(obj.getSomaData() == null){
...
0
votes
0answers
18 views
find difference between multiple rows in postgresql
what syntax should I use to find the difference between multiple rows for a unique user in postgreql? I have several entries recorded by the browser for each user and I'm trying to find out the total ...
0
votes
0answers
22 views
regex_replace using plpgsql syntax
Trying to modify a cool function from @wingedpanther which removes trailing spaces from each and every column of all tables in a db.
The goal is to replace the TRIM functionality with regexp_replace ...
-2
votes
1answer
10 views
How do I plot PGSQL queries onto google maps? [on hold]
So i am trying to learn how to store pgsql queries on a google map. So my database contains two json columns for the lat/long. I have the slightest idea of how to query json from pgsql and display it ...
google-maps
0
votes
1answer
27 views
Rails: PG::SyntaxError: ERROR: syntax error at or near “:”
I'm trying to write this query:
@myquery = Event.where("reservations.map(&:first)= ?", current_user.id)
but I get this error:
ActionView::Template::Error (PG::SyntaxError: ERROR: syntax error ...
0
votes
2answers
34 views
Need to filter my sql results for the last 4 days
I need my filter to return only the last 4 days from the current date. My struggle has been in properly using the DATEADD() and NOW() functions because I keep keeping getting errors. Any advice?
As ...
0
votes
0answers
12 views
How to migrate data from ExpressionEngine to Rails App
Right now we have a system that is using ExpressionEngine, but we are rebuilding everything from scratch using Ruby on Rails. What I'm trying to figure out is how to get all the existing data in ...
-1
votes
0answers
12 views
Postgresql - Data versionning for data warehouse
I need to create a data warehouse based on PostgreSQL. In a data warehouse, the records should be non-volatile, so need to manage records with versionning.
What is the best way to implement ...
0
votes
0answers
15 views
Executing an external python script from django, leads to segmentation fault
I wrote a script a while ago, which creates some figures and does a few SQL querys with psycopg2 on postgresql after processing a file "A". This script works fine when executed on its own. Tested ...
0
votes
0answers
24 views
Postgresql simple query very slow response time
I have a really simple database that I am using to build out some JSON for Elasticsearch. I'm the only person connected to the database, and only using it for this specific task. Anyway, hitting some ...
0
votes
2answers
16 views
Postgres foreign keys / schema issue
If I create a new schema on the current database (management), why does it complain about cross-database references?
management=# create schema mgschema;
CREATE SCHEMA
management=# alter table ...
0
votes
2answers
17 views
Retrieving json query from postgres and display in java
String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";
Statement statement = dbConnection.createStatement();
ResultSet rs = statement.executeQuery(selectTableSQL);
while (rs.next()) {
String ...
0
votes
1answer
15 views
I don't understand explain result of a slow query
I have a slow query (> 1s). Here is the result of an explain analyze on that query:
Nested Loop Left Join (cost=0.42..32275.13 rows=36 width=257) (actual time=549.409..1106.044 rows=2 loops=1)
...
0
votes
0answers
20 views
Rails query on combined unique columns
I have 3 tables
ride, rider, horse
I want to create a query to retrieve @results that find unique matches of the horse and rider combination associated with ride
class Ride < ActiveRecord::Base
...
0
votes
1answer
18 views
Why can't I extend docker postgres image to create extra database and user
I'm using docker postgres 9.4.5 images and extendeding it using an init.sh copied to /docker-entrypoint-initdb.d in the docker container. I am trying to create another database and a non admin user ...
0
votes
1answer
14 views
update increment of serial field from several records in postgres
I want to change the increment value of all my columns that use sequencial IDs. How can I do it?
I tried the following 2 approaches, both failed:
UPDATE information_schema.sequences SET increment=1;
...
0
votes
0answers
12 views
PostGIS geometry data type no longer recognised
I am currently running postgresql 9.6.1 with postGIS 2.3 on windows 7 after upgrading several weeks ago from 9.4. After the upgrade my database worked without issue for over a fortnight, however this ...
0
votes
0answers
15 views
Python3, using Pandas function df.to_sql() with SQLAlchemy to insert dataframe into PostgreSQL database
How do I specify the primary key of the table? If doing this with standard SQL I would use:
CREATE TABLE table (
uid TEXT PRIMARY KEY NOT NULL,\
age SMALLINT NOT NULL,\
gen TEXT ...
0
votes
0answers
13 views
stored procedure query is not working when uploading file which is having size more than 20MB in postgresql
I'm using the below code to store file into DB.
CallableStatement proc = null;
File file = u;
FileInputStream fis = new FileInputStream(u);
proc = dm.call("{ call contentadmin.insert_page({?,"+...
0
votes
1answer
10 views
How to increase max_locks_per_transaction
I've been performing kind of intensive schema dropping and creating over a PostgreSQL server,
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
I need ...
-2
votes
0answers
18 views
How to check if links in DB have existing files in directory?
How can I check (in PowerShell) through my directory if for a link in an attribute of a PostgreSQL DB table, there is an existing file with the same name. An example:
Here my db:
db: db_name
...
0
votes
1answer
6 views
Postgres - Peer authentication - psql: FATAL: role “xxxx” does not exist
I know there are plenty of posts with close message. I've read hopefully all. And did not find the answer. Some offer to use -h localhost to jump from local to host rules in hba.conf. Some say to set ...
2
votes
1answer
16 views
Recursive list of sub products in postgres SQL
I have a quick question concerning Postgres SQL.
My goal is to a product table where we have 1 or more parent products and then certain child products.
In another table sales I just put it the sales ...
0
votes
1answer
31 views
How to make a query using a database function with Django ORM?
I want to query the database using a WHERE clause like this in Django ORM:
WHERE LOWER(col_name) = %s
or
WHERE LOWER(col_name) = LOWER(%s)
How can I do this using QuerySet API?
0
votes
1answer
7 views
Matching regular expression in an if statement in PostgreSQL
I have written the following function:
CREATE FUNCTION Validate_Password_Hash() RETURNS trigger AS $$
BEGIN
IF (NEW.Password ~* '^[a-f0-9]{64}$')
THEN
RAISE EXCEPTION 'The password ...
0
votes
0answers
27 views
Is there a way to make a download from a PostgreSQL query?
I want to know if there is a way to make a download without having the file on the server.
To be more precise, my server run on a VM with very limited disk space (actually ~200 Mo of free space), and ...
0
votes
0answers
10 views
how to correctly analyse the costs produced by an EXPLAIN statement
In order to analyse my query I trailed it with the following statement
EXPLAIN (FORMAT json, COSTS true)
which produced the output here below. The point is: is there a total cost I can compare with ...
0
votes
0answers
14 views
Peewee get all descendants
I am using peewee as a ORM in python (in combination with postgresql) and I am trying to query every descendant of a given id out of a table with self referencing (parent-child)
What is the best way ...
0
votes
0answers
14 views
BIRT connects to PostgreSQL database, shows table columns, but displays error while querying
Today I have encountered quite unusal problem. I have downloaded proper JDBC driver and copied it to the folders. Then I have created new connection it BIRT, successfully, BIRT has viewed all the ...
0
votes
0answers
9 views
Pulling db from heroku to local db
I am trying to pull my pg database on heroku into my local development database. I am running
heroku pg:pull HEROKU_POSTGRESQL_URL LOCAL_POSTGRE --app MY_APP
Unfortunately I get :
pg_dump: server ...
0
votes
1answer
20 views
Postgresql jsonb_agg subquery sort
How can I sort the results of a subquery that's using a json aggregate?
If I had a schema like this:
CREATE TABLE plans( id integer NOT NULL, name character varying(255));
CREATE TABLE plan_items ( ...
1
vote
1answer
17 views
Postgresql - Add 2 random columns
I have a large dataset, which I want to divide into training, validation and test set according to some predefined probability at the beginning (e.g. with 0.8 probability, a tuple is being used for ...
0
votes
1answer
11 views
How to restore APSchduelder sqlite to Postgres
I am migrating from sqlite to postgres.
Here is dumpfile.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE apscheduler_jobs (
id VARCHAR(191) NOT NULL,
next_run_time FLOAT,
...
3
votes
1answer
23 views
Docker-compose MemoryError
I try to launch odoo on vps with 512 Mb RAM.
I use docker. When I launch containers with plain docker everything is fine. I launch like that:
postgres container:
docker run -d -e POSTGRES_USER=odoo -...
