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
5 views
PostgreSQL execute trigger as different user
I'm implementing a database with a few triggers that registers changes to specific tables into a couterpart tables in a named "audit" schema.
This is, as it sounds, to be able to audit changes made ...
0
votes
0answers
7 views
postgresql passwordcheck windows server
I have a windows server with installed postgresql.
I would like to enable passwordcheck extension but is not present and do not know where to find it.
Can someone help me?
Thanks so much
0
votes
0answers
12 views
How to exclude many rows based on one row in PostgreSQL 9.3
I have a products table:
productid name
4 Product A
5 Product B
each product has properties
propertyid productid propertyname convert
1 4 ...
0
votes
0answers
8 views
Postgresql Make existing primary as SERIAL
I am using postgresql 9.4. I want to change existing primary key with serial. My query is not working. Anybody know how to do this?
Alter table 'table_name' alter column id BIGSERIAL;
There should ...
0
votes
0answers
18 views
Update array from subset of the array itself
I have the following table in a Postgres database:
CREATE TABLE test(
id SERIAL NOT NULL,
arr int[] NOT NULL
)
I would like to keep only the n last elements of a specific array in the table. For ...
0
votes
0answers
5 views
Ejabberd postgreSql Connection
i connected postgreSql with ejabberd but ejabberd doesnt create tables . Is there any problem? I suppose ejabberd still using local db.
In configuraion file ejabberd.yml . must i change something ...
1
vote
0answers
16 views
How to cleanup postgres temporary tables of recursive query from django/psycopg2
When postgres runs recursive query it creates temporary table for it.
After query is finished, the temporary table remains on disk, until whole program competes.
The program consist of main process ...
0
votes
1answer
13 views
How to brew uninstall postgres on OX Sierra
Reading from the here, I want to uninstall postgres completely with homebrew and have used brew uninstall postgresql but I get this response.
No such keg: /usr/local/Cellar/postgresql
I know ...
0
votes
1answer
30 views
Postgres pass more than 100 arguments to CONCAT function
I have the following query:
SELECT
"external_id" AS EVENT_ID,
"uuid" AS EVENT_VERSION_ID,
"timestamp" AS EVENT_VERSION_TIMESTAMP,
CONCAT('{',
'"timestamp": ', COALESCE(timestamp, NULL), ',',
...
0
votes
0answers
11 views
How to configure HikariCP with PostgreSQL
I am trying to integrate HikariCP with PostgreSQL in my web application,I am using the postgresql driver and this DataSource class: org.postgresql.ds.PGSimpleDataSource and my hibernate configuration ...
0
votes
0answers
9 views
Docker volume details for Windows
I am learning docker and deploying some sample images from the docker hub. One of them requires postgresql. When I deploy without specifying a volume, it works beautifully. When I specify the volume '...
1
vote
1answer
15 views
how do I pass a user defined type variable to a function as a parameter?
I want to pass a user defined type parameter to a PLPGSQL function, but I am getting this error at runtime:
dev=# select process_shapes();
ERROR: invalid input syntax for integer: "(,,7)"
CONTEXT: ...
0
votes
0answers
11 views
Multiple annotations found at this line In eclipse by JSP language? [duplicate]
Now I am working on JSP language development in eclipse IDE, and current project I just import from WAR file, so encountering with the error message on the head of the file.
Let me show the code that ...
0
votes
0answers
20 views
Syntax error at or near “USING”
I am trying to recreate the functionality of a query found inside of a mapfile on our mapserver into a plpgsql stored procedure.
Here is the query:
geom from (select g.gid, g.geom, g.basin, a.\"...
1
vote
0answers
13 views
pg_basebackup: directory “/var/lib/pgsql/9.3/data” exists but is not empty
I am trying to build a HA two node cluster with pacemaker and corosync for postgresql-9.3. I am using the link below as my guide.
http://kb.techtaco.org/#!linux/postgresql/...
0
votes
0answers
12 views
Converting an iLike query to regex [duplicate]
I am not getting the desired result when using this query in postgreSQL:
Select Where DESCRIPTION iLIKE '%[0-9] PF %'
I am looking to pull all rows where a number is followed by " PF ".
So for ...
0
votes
0answers
6 views
Flask-Bcrypt - Invalid Salt
I have a flask application (py-bcrypt, flask-bcrypt, python3) hosted on heroku. I just set up a staging environment and configured it exactly like the production environment. Everything works great, ...
0
votes
2answers
27 views
Postgres query returning SQL Error syntax error at “Where”
I have the following query, when I execute it I'm getting an error
INSERT INTO AM_EVENT_STATUS (EVENT_STATUS_ID, EVENT_ID, TIMESTAMP,OLD_STATUS_ID, NEW_STATUS_ID, NOTE, USERNAME)
SELECT
"external_id"...
0
votes
0answers
8 views
ecto failing to create table in postgresql
Using Phoenix framework. Following along in book programming phoenix.
The project was created using mix. The relevant config for the database user and password were updated as necessary.
The ...
0
votes
1answer
18 views
Alternative for [0-9] in SQL
This query use to return all values with two digits back to back:
Select WHERE (DESCRIPTION iLIKE '%[0-9][0-9]%')
But it no longer works. Is there a workaround using postgresql? Something possible ...
-1
votes
0answers
24 views
How to solve the the Error message “Multiple annotations found at this line” In eclipse by JSP?
Dear All our Experts,
Now I am working on JSP language development in eclipse IDE, and current project I just import from WAR file, so encountering with the error message on the head of the file, Let ...
1
vote
1answer
10 views
Heroku h12 Timeout Error with PG / Node.js
I am getting a nasty h12 (timeout error) when trying to insert into my db.
2017-01-25T00:22:24.764591+00:00 heroku[router]: at=error code=H12 desc="Request timeout" method=POST path="/api" host=...
-1
votes
1answer
23 views
Re-create primary key in Rails
I previously converted one of my tables' id column to bigint, and populated it with bigint values. I'm now regretting that as I can't seem to update values via ActiveRecord without runnning into ...
0
votes
0answers
11 views
PostgreSQL many jsonb columns vs many rows
From other answers on many columns vs many rows (or tables) it seems columns are more performant for normalized data. What about serialized data?
I'm going to store many in-progress web forms, i.e. ...
0
votes
1answer
8 views
Rails App on Heroku Can't Connect to RDS
I'm trying to get a Rails app that's hosted on Heroku, and currently using Heroku-Postges, to instead use an instance of AWS/RDS as its datastore. The problem is that the rails app doesn't seem able ...
1
vote
0answers
4 views
I need to force time to char, so that the deployment looks cleaner
First problem is that I have this output if I run the code in pgAdmin3: this is what I want.
But when deployed I get this: this is too long for my form.
Also can I replace negative time with text like ...
0
votes
1answer
21 views
Using Ansible's postgresql_privs to lock down Postgres public schema
I want to lock down the public schema in a postgres database using Ansible.
Using psql in a newly created postgres install:
postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# ...
0
votes
1answer
10 views
Heroku Friendly_Id Gem undefined method slug
So Iv created a basic app on rails and uploaded it to heroku. Everything works perfectly on my local machine including the friendly_Id gem.
Iv pushed my app to heroku and everything works fine, up ...
-1
votes
1answer
22 views
Make a comment in script unix
I want to make a comment in a script that will be run by psql with the command \i name_script
I have not found a way to make a comment in the script that works in most of unix SO .
0
votes
1answer
25 views
Cannot Connect To PostgreSQL Database
I am trying to connect to my database through R, but am getting an error. My coworker connects perfectly with the same exact script. Anyone know what else could be the problem? We have the same ...
0
votes
1answer
7 views
CSV Module “UnicodeEncodeError” when using Dictwriter.writerows
I'm setting up a prod environment on a new Mac server that should mirror my dev environment. The job runs without a hitch on my dev computer, but on the server I'm getting this traceback:
Traceback (...
1
vote
1answer
22 views
Update a table from another table using subselect
Using postgresql I have a table A with columns company_name, department_name, some other data, and department.
I have another table B with simply company_name, department_name, and did. (This are two ...
1
vote
2answers
23 views
Django raw SQL queries with ForeignKey
models.py
class Author(models.Model):
name = models.CharField(max_length=30)
surname = models.CharField(max_length=50)
class Exhibit(models.Model):
author_id = models.ForeignKey(Author)
...
0
votes
1answer
25 views
Rails scope with combined attributes
I am trying to create a scope where checkin_time (DateTime) is added with duration (Int) in hours which is greater than or equal to Time.now
So something like this
scope :current, -> { where('...
-1
votes
1answer
31 views
RangeError when using bigint IDs in Rails
Our Rails 4/Postgres app has a large table with an id that we have converted to column type bigint.
Migration:
class ConvertWidgetPkToBigint < ActiveRecord::Migration
def change
...
0
votes
2answers
19 views
How to deal with foreign keys when moving a postgres database between machines
I'm trying to move a postgres database between machines as I move from one development platform to another. I have yaml_db gem installed on both machines.
On my old platform I do:
rake db:scheme:...
0
votes
0answers
19 views
Postgres data set ETL
I'm extracting data from a MS SQL Server database into my Postgres database every 30 min.
This task is made more complex due to the fact that the data in the SQL Server database is very non-...
0
votes
0answers
20 views
how do I loop through an array contained in a JSONb field?
How do I loop through an array contained in a jsonb field inside a PLPGSQL function?
This is my field declaration:
CREATE TABLE afact_rule(
...
expressions jsonb,
...
)
And this ...
0
votes
0answers
15 views
From PostgreSQL's Large Objects to Spring's ResponseEntity
I'm trying to figure out how to upload and download to/from REST API implemented in Spring while the files are stored in PostgreSQL's Large Objects.
So far I've managed to upload a file and store it ...
0
votes
1answer
24 views
Using docker-compose to collaborate on existing git repo Django project
I'm just getting started with Docker, and I want to use it to containerize a Django project which ~7 collaborators are working on, and I want some of us to have the option of using Docker to speed up ...
0
votes
1answer
21 views
Django postgres order_by distinct on field
We have a limitation for order_by/distinct fields.
From the docs: "fields in order_by() must start with the fields in distinct(), in the same order"
Now here is the use case:
class Course(models....
0
votes
0answers
17 views
Delta load for data warehouse
I have set up delta load in Staging environment where I use created_at and updated_at timestamps to insert or update staging tables.
I have ODS database where multiple tables are combined and same ...
1
vote
1answer
21 views
Need help selecting distinct and max field in sql
So in the nc_sno.netcredit_dist_lp1 table, there are loan numbers and id numbers. Each loan number can have up to two different id numbers. I want to pull the highest id number for each loan number. ...
1
vote
0answers
19 views
Load-balancing PostgreSQL queries using two different Spring datasources
We're attempting to set up a PostgreSQL cluster using streaming replication inside Kubernetes, but we'd prefer to avoid the trouble of going through the configuration of pgpool or any other SQL-aware ...
kubernetes
1
vote
3answers
49 views
Optimise many-to-many join
I have three tables: groups and people and groups_people which forms a many-to-many relationship between groups and people.
Schema:
CREATE TABLE groups (
id SERIAL PRIMARY KEY,
name ...
0
votes
0answers
17 views
Difference between hibernate unique constraint annotation and postgresql query
I am currently using Hibernate with Postgresql.
There are some columns in my database that needs to be set with the Unique constraint. So I went online and found two ways of doing it
Query in ...
0
votes
1answer
18 views
Postgresql: The most efficient query for selecting “the highest lower” and “all higher”
Let's have a database table, e. g.:
t_times (id INTEGER, created TIMESTAMP WITHOUT TIME ZONE);
Is there a possibility to write a query that would return all entries created after the given date and ...
0
votes
1answer
16 views
Database function which updates json
In a Postgres DB I have a table (Users) which defines users. It is like
|ID| meta |
|01| {"email" : "[email protected]","importance" : "High"} |
|02| ...
0
votes
2answers
21 views
Remove n elements from array using start and end index
I have the following table in a Postgres database:
CREATE TABLE test(
id SERIAL NOT NULL,
arr int[] NOT NULL
)
And the array contains about 500k elements.
I would like to know if there is an ...
0
votes
1answer
13 views
Linking MS Access table to PG Admin schema
I would like to link a MS Access table to a table in PG admin if it is possible for use in a Postgres query. I have searched for an answer but all I can find is answers for listing postgres tables in ...
