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
4 views
Consume Dynamics CRM Web API directly into PostgreSQL database in Django web app
Microsoft Dynamics 365 CRM has most current client and company data and I need it for the internal database the frequently uses the data. Trying to replace duplicative databases where one is getting ...
0
votes
1answer
7 views
Rails | SQL where for datetime and date
I am trying to fetch records on database on localhost.
My columns from & to are dateranges and I would like to convert daterange to date to find records.
date = Date.parse(2017-02-17)
sc = ...
0
votes
0answers
5 views
Liquibase Create View Schema Issue
I'm creating a view in a schema for Postgres, with the following changeset:
<changeSet author="xxyyzz (generated)" id="148150949234281-2">
<createView replaceIfExists="true"
...
-1
votes
1answer
12 views
SQL insert a User role
I have the following users/roles table structure in a database. And I want to set a role (for example, "CUSTOMER" for a user). So that the role id will be inserted into the User_Roles table as well as ...
0
votes
1answer
13 views
Postgres: How to group a very large table and insert the results into a new table?
I am working in Postgres 9.6 with PostGIS. I have a very large table (about 30GB):
Table "public.parcels"
Column | Type | ...
0
votes
0answers
3 views
Trouble installing psycopg2 for Python 3 on CentOS 7.3
I went through quite a bit of trouble to get a success return code out of python3 -m pip install psycopg2 on CentOS 7.3. However, the package is still not functional. At first, I was unable to see the ...
1
vote
1answer
11 views
Convert JSONB Array to Columns
I have a query that returns a single column containing jsonb objects. Here is an example of some returned rows (jsonb::text for legibility here):
[{"amount": 3, "consumable": "Mitsu 90mm ODEX Pilot ...
0
votes
1answer
11 views
PostgreSQL rename attribute in jsonb field
In postgresql 9.5, is there a way to rename attribute in jsonb field ?
For example :
{ "nme" : "test" } will be renamed to { "name" : "test"}
0
votes
1answer
12 views
Hot to stop sequelize from creating an unwanted primary key when inserting data?
I have a sequelize model without any primary key:
module.exports = (sequelize, DataTypes) => {
const usersDoors = sequelize.define('usersDoors',
{
user_uid: {
type: DataTypes....
0
votes
0answers
14 views
Can I use a database on an offline product and not have to install postgres
So I made this java project that does the basic saving and fetching from database. When I run it from another pc i get this error
I'm guessing this is because I don't have postgres installed on the ...
0
votes
0answers
4 views
Setting postgresql timezone from Rails
I have a Rails API that calls a Postgresql function. The Postgresql function returns a string that includes some columns from a table. Some of these columns are timestamps with time zone. The Rails ...
0
votes
0answers
14 views
PostgreSQL foreign data wrapper that supports authentication over HTTPS
Tried using www_fdw for this purpose, but it seemed pretty likely from the get go that it would not work. The options it listed did not include anything about including a user and password to access a ...
1
vote
0answers
9 views
How to embed multi-line shell command in psql file
I have a .sql file with some lines which look like this:
\set buf `cat example-message.msg | base4 -w0`
SELECT * FROM run_select(:'buf');
However, this hides the content of example-message.msg and ...
0
votes
1answer
18 views
Hibernate create table at runtime
In my database(postgreSQl) is table Person which contains name and password. It is possible to dynamically(at Spring runtime) create new table by using Hibernate? For example I want to create ...
1
vote
1answer
18 views
How to specify tablespace when creating postgres database?
My postgres server is running out of space when restoring database, after dumping, How can I create a tablespace and specify a new database to that specific location.
Some examples would be nice. ...
0
votes
1answer
25 views
Number of Transactions in a given week
I have this column, Actual_Date. Eight transactions took place for the week ending 2/12/2017. I am trying to work on a SQL snippet that would provide me with the number of transactions, which took ...
0
votes
1answer
10 views
How to search from multiple values in single field (Bus Stops in GTFS)?
In the GTFS data for Denver, the BUS_STOPS table stores multiple comma-separated values in the ROUTES column:
28, 19, 44, 10, 32
I'm selecting BUS_ROUTES that are within a distance of a school.
But ...
0
votes
1answer
12 views
Custom sql in django migration doesn't work
I'm trying to run custom SQL in my migration. This is how it looks like:
from django.db import migrations
class Migration(migrations.Migration):
dependencies = [
]
operations = [
...
0
votes
0answers
9 views
Spring JPA row_to_json PostgreSQL
Instead of creating an entity with column annotation, is it possible to get query result with column name? Like:
@Query(value = "select emp.emp_id, emp_fname, emp_lname, emp_division_id, emp_role_id,"...
1
vote
1answer
33 views
Django can't connect to Postgres in Docker setup
I'm setting up an application in Docker that uses Django and Postgres. I'm unable to connect to Postgres from the app. When I run either:
docker-compose run web python manage.py runserver
docker-...
1
vote
0answers
9 views
Bookshelf.js - Passing models to collection.atttach()
The bookshelf documentation indicates that I should be able to pass an array of models into collection.attach(), and they demonstrate this with the following code:
var admin1 = new Admin({username: '...
2
votes
3answers
27 views
postgresql create table of days per month
I'm trying to write a script which returns a list of months with the number of days in the month. It references this table
CREATE TABLE generic.time_series_only (measurementdatetime TIMESTAMP ...
1
vote
0answers
25 views
psql command line / terminal keyboard shortcuts
I would like to use psql at the command line in terminal on my mac to connect with my postgres DB. I can connect to my database with psql and am able to enter sql commands in my terminal. However, ...
2
votes
1answer
19 views
Postgres conditional unique constraint
Pretend I have a users table where users are members of a specific tenant, and their e-mails are uniquely indexed to their tenant, like this:
User
id | tenant_id | email
1 1 ...
1
vote
0answers
21 views
The correct way to swap values that have a unique constraints with Ecto?
I need to swap values that have unique constraints. I'm trying to use the update_all function as shown below.
from(e in Episode, where: e.show_id == ^id, update: [set: [position: fragment("position + ...
0
votes
0answers
9 views
Two results for which -a pg_config, trying to install psycopg2 on Mac
I am trying to install pyscopg2 (using pip) on Sierra.
Seems to be a common problem, so I've checked out a handful of other SO answers. I tried the solution from here.
But I still can't install. ...
0
votes
1answer
14 views
Can PostgreSQL query external Microsoft Dynamics REST API directly?
Our most up-to-date client information is stored in Microsoft Dynamics 365 CRM and would like to query it anytime information related to it is needed to make sure the internal PostgreSQL database has ...
1
vote
2answers
16 views
Modify datatype of a field in JSONB in Postgres
I have a JSONB column with JSON that looks like this:
{"id" : "3", "username" : "abcdef"}
Is there way to update the JSON to :
{"id" : 3, "username" : "abcdef"}
1
vote
2answers
19 views
PostgreSQL 3 Table Join Multiplying
I have 3 tables. The first has the records I want. The other two have categories to be applied to the first table. If the lookup value from table3 is found in the description, I want to return that ...
0
votes
0answers
17 views
Cannot SELECT FROM declared table in postgresql?
In my function declaration :
$function$
declare
alllogins schema.EmployeeLogin;
nologins schema.Employee;
begin
SELECT * INTO alllogins FROM schema.EmployeeLogin;
SELECT * INTO nologins FROM ...
0
votes
0answers
11 views
Query time after partition in postgres
I have table location in postgres database with more then 50.000.000+ rows, and i decide to do partition!
Table parent have columns id,place and i want to do partition onplace column, with php and ...
0
votes
1answer
19 views
Postgres: replace second occurrence of a string
I tried to fix bad data in postgres DB where photo tags are appended twice.
The trip is wonderful.<photo=2-1-1601981-7-1.jpg><photo=2-1-1601981-5-2.jpg>We enjoyed it very much.<photo=2-...
0
votes
0answers
11 views
CakePHP can't save into Postgres database, but with successful return
Does anyone encounter this issue before?
In CakePHP, I'm having this.
$this->loadModel('BankBalance');
$data = array("BankBalance"=> array(bla bla bla...);
$this->BankBalance-&...
0
votes
1answer
26 views
Postgresql parallel bulk INSERT with worker don't parallelize
My scenario:
10 worker
Database has set 100 max connections
Every worker has its own DB connection (max. 10 connections)
Every worker starts a transaction (BEGIN; COMMIT;)
Every worker inserts data ...
1
vote
1answer
16 views
SQLAlchemy ignoring specific fields in a query
I am using SQLAlchemy with Flask to talk to a postgres DB. I have a Customer model that has a date_of_birth field defined like this
class Customer(Base):
__tablename__ = 'customer'
id = ...
0
votes
2answers
41 views
Postgresql count by past weeks
select id, wk0_count
from teams
left join
(select team_id, count(team_id) as wk0_count
from (
select created_at, team_id, trunc(EXTRACT(EPOCH FROM age(CURRENT_TIMESTAMP,created_at)) / ...
0
votes
0answers
21 views
Compare id's stored in XML to id's stored in a table
I have XML stored in a table called tbl_products, I want to extract the ID from this XML <id> and return the ID if the ID does appear in a different table.
I'm aware this may be worded badly, ...
0
votes
0answers
21 views
OpenShift PostgreSQL Database Restore
I have an OpenShift App that uses the PostgreSQL database.
I want to restore this database with data from a 'live' database.
When running locally, I just drop the database. Then I create the ...
3
votes
2answers
38 views
Window Function: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
First I had the common problem why first_value(ORDER BY ... DESC) != last_value(). Now I understand that the default window includes only all rows <= current_row.
Sample data
CREATE TABLE test
...
1
vote
0answers
21 views
Removing element from array within object JSONB
I am attempting to remove an element from my database. The element I want to remove is within a JSON object called playerContainer which contains an array named players. This all sits within a table ...
0
votes
3answers
44 views
SQL query to extract information from joined tables
I need some help with a SQL query, I don't even know how to name this kind of SQL query pattern... I have the following simplified tables:
teachers:
ID | NAME
1 | john
2 | paul
...
0
votes
1answer
12 views
Detached entity passed to persist with primary key that is also foreign key
I have the following tables in postgres:
create table USER(
USER_ID SERIAL primary key,
USER_NAME varchar(50) not null,
PASSWORD varchar(120) not null,
)
And
create table ACCESS_TOKEN(
...
2
votes
1answer
25 views
how to save time without time zone in postgres . i am using hibernate Spring MVC
ERROR: column "receipt_time" is of type time without time zone but
expression is of type bytea Hint: You will need to rewrite or cast
the expression. Position: 490
private LocalTime ...
0
votes
0answers
22 views
cannot parse json respone when query from foreign table in www_fdw postgresql
I am using www_fdw to query from twitter search API, here is the process that I followed:
CREATE extension www_fdw;
CREATE server www_fdw_server_twitter FOREIGN DATA Wrapper www_fdw OPTIONS
(uri '...
0
votes
0answers
22 views
PGSQL showing packs based on parent pack id
Ive got a problem assigning job name to a parent packs (the parent pack contains for example two packs)
You can see in the picture that a parent pack has no job_name...
In the output table there ...
-1
votes
1answer
34 views
Where the error in my actions?
Hello everyone, I have one small task but I don't know where the error is in my actions. I'm using Yii2 2.0.10 with PostgreSQL and I need to update a few ActiveRecord models and change status in ...
-1
votes
0answers
22 views
Postgres Foreign Table returns 0 rows
I have a foreign table defined within my database which when I query returns 0 rows. However the remote table does have rows (about 10K+ rows) and when I login into the remote database server I can ...
0
votes
1answer
25 views
Django no object matches the given query but it should
I have the following problem my database is not empty and has the record that I need. But when I query the db it says that there is no such object matching the given query. I query the db from within ...
0
votes
1answer
24 views
Error : function levenshtein() does not exist
I am using levenshtein() function in my SQL script. I am using Postgresql database and client to execute the script. It's throwing an error:
function levenshtein(character varying, character ...
2
votes
2answers
25 views
Select with own array. PostgreSQL 9.5
I have the following schema:
create table reports (id bigserial primary key);
create table scens (id bigserial primary key,report_id bigint references reports(id), path_id bigint);
create table runs (...
