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
2 views
Comparing Subqueries
I have two subqueries. Here is the output of subquery A....
id | date_lat_lng | stat_total | rnum
-------+--------------------+------------+------
16820 | 2016_10_05_10_3802 | 9 ...
0
votes
1answer
12 views
Postgresql reorders table rows after java entity update - why?
I have the following table in PostgreSQL
and I am using all things that are in topic tags.
I edit table via p:dataTable EDITABLE from primefaces.
And now the question. When I for example edit row ...
1
vote
1answer
7 views
Rails 5 Dynamic Conditions for has_many
Been looking everywhere for an answer to this.
has_one :region_location, ->(location) { where("region_locations.site_id = ?", location.current_site.id) if location.current_site }
This code is ...
0
votes
1answer
7 views
PostgeSQL misinterpreting characters as numbers after casting
I have been following the example here to convert a date to month name. However, when I get a shifting window of two months, the second month's name gets fragmented in the interpretation of some ...
0
votes
0answers
18 views
Java json string to Serializable [duplicate]
I'm meeting this problem.
I'm using PostgreSQL with a column whose type is jsonb in a table. After Hibernate mapping, it come to Serializable. I got one json string from client, and want to store ...
1
vote
0answers
7 views
Add timestamp with duration in sqlalchemy + postgres
I am trying to add a timestamp with an integer duration in my query.
QTrack.query.filter(
func.dateadd(
QTrack.start_time, QTrack.duration * func.interval('1 second')
) < datetime.datetime....
0
votes
0answers
11 views
Naming conventions in pgAdmin3
I am creating tables in the GUI of postgreSQL, PgAdmin3. There is an element that seems to be specious in the naming of the tables. The following is my fragmented comprehension of postgreSQL (I could ...
0
votes
0answers
7 views
How to failback with Postgresql cluster using repmgr?
I want to build a Postgresql Cluster in a Cloud environment (yes I know it's a bad idea ... but that is not the question).
For your information, I use a Dockerized Postgresql (see: https://github.com/...
2
votes
2answers
16 views
Postgres non-unique if other field is same, unique otherwise?
I'm looking to do the following:
Create a constraint where a user can claim a unique URL, and they can create as many rows with that URL as they want, but other users cannot use that URL once it's ...
-1
votes
1answer
24 views
Good or bad database design
I have following migration:
create table(:countries_codes) do
add :iso, :string, size: 2
add :name, :string
timestamps
end
create unique_index(:countries_codes, [:iso])
create table(:...
0
votes
1answer
16 views
Postgresql indexing a range type
I have an table which has an daterange column. I want to select rows that contain date of today.
select * from mytable where drange @> now()::date
the @> operator is true if range contains ...
0
votes
2answers
32 views
weird postgresql query
I could not make sense of a query, I found a workaround to get the data but I would like to know what happened.
so basically I want to obtain all ids that are not contained in another table. if I ...
0
votes
1answer
15 views
Apache Tomcat connection to PostgreSQL database (via JDBC driver)
I have installed PostgreSQL 9.5.2 (rhel 6.7 x64) and Apache Tomcat 7.0.73(x64 rhel 6.7) after this i have downloaded postgresql-9.4.1212.jar jar file and paste to Catalina_home/Lib path and adding ...
1
vote
0answers
48 views
echo doesnt work after pg_query
I'm currently trying to retrieve information from my database and display this data with a php file but it doesnt work as intended. Here is my code:
<?php
require("dbconnect.php");
if(!$db){
...
0
votes
3answers
21 views
How to remove duplicate values in two columns and fetch only specific value in Postgresql
I have to write a query in which i have to add conditions on two columns , conditions is like, the first column aquire_nation and second column in target_nation. I have to find all records those are ...
0
votes
2answers
31 views
Postgresql Unknow field in function
I'm trying to create a function in postgresql and i get the following error:
ERROR: column "A00" does not exist
LINE 1: SELECT * from "UpdateStatus"(91206618515,"A00")
...
0
votes
0answers
25 views
Postgres query in SQLAlchemy
I have a postgresql query, which I am trying to do it in SQLAlchemy:
SELECT words['p']
FROM (
SELECT transcript_info['words'] AS info
FROM Audio, func.json_array_elements(transcript['...
0
votes
1answer
14 views
Slow Update in Postgres
Trying to update a column in a 4.4 million rows table. I have been able to reduce my query time from 30+ minutes to 14 minutes with the following version:
update t_settlement x
set dt_ad_decode = ...
0
votes
1answer
11 views
PSQL: non-trivial update and join
I am trying to update balances table from liquidation_speed table. The problem is that I need to join campaigns to set update criterion based on campaign id.
balances table: id, liquidation_speed
...
0
votes
1answer
22 views
Return back dropped(deleted) table Rails
I accidentally deleted table "results" from schema with this command in rails console:
ActiveRecord::Migration.drop_table(:results)
I tried but not working
rake db:migrate:up VERSION=201608021358
...
1
vote
0answers
19 views
How to declare a variable in PostgreSQL queries
I'm trying and struggling to declare variables in PostgreSQL queries. Can anyone help me with the below?
declare CampaignID val INT;
select CampaignID = 6
select
result_code.description,
...
0
votes
0answers
8 views
Is TLS v1.2 supported by dotConnect 7.6.763 running Postgres 9.6.1 on Windows?
In my setup I'm using dotConnect 7.6.763 and Postgres 9.6.1 running on Windows 10 and Windows Server 2012 (tried both) with the following postgres ssl configuration:
ssl = on
ssl_ciphers = 'HIGH:...
0
votes
1answer
32 views
spring jdbc update method does not commit the update as get Record returns old value
I am using spring Jdbc for Database operation with postgres db.
I get continuous notification which I am processing.
Event new Call: I insert the new record in the DB
Event call Lost : I update the ...
0
votes
1answer
15 views
Postgres width_bucket() not assigning values to buckets correctly
In postgresql 9.5.3 I can't get width_bucket() to work as expected, it appears to be assigning values to the wrong buckets.
Dataset:
1
2
4
32
43
82
104
143
232
295
422
477
...
0
votes
0answers
8 views
Which is better having a column type array or column type string while storing array in postgresql?
I'm using Postgresql, need to store multidimensional array which is better idea between serialize multidimensional array and store as a string or directly store multi dimensinal array.
Note: I don't ...
0
votes
0answers
14 views
Insert Uuid in ltree type column in postgres
I have a following table,
CREATE TABLE ProductCode_Timeline
(
"Uuid" uuid,
"Descriptions" text,
"Hierarchy" ltree,
)
In above table's "Hierarchy" column, I want to add uuid as path ...
1
vote
0answers
21 views
How to export data from a database to a json file?
In my haskell application I'm using a database, for now it's Postgresql, but in the future it might also be MySql or Sqlite3. I'm looking for a way to export data from my database to a json file via ...
1
vote
1answer
18 views
How to dump the large object data from Postgres 9.4, and then import it to Postgres8.x?
I used pg_dump to export the data including large objects (LO) from Postgres 9.4 like this:
$ pg_dump fhir -O -b > fhir.sql
The LO statements in my generated fhir.sql are like this:
SET ...
0
votes
0answers
23 views
PostgreSQL conditional join - if column is not NULL
I have a table "temp"
author | title | bibkey | Data
-----------------------------------
John | JsPaper | John2008 | 65
Kate | KsPaper | | 60
| ...
1
vote
1answer
37 views
Need the add the date by 1 year in postgreSQL using golang
I am using golang and postgreSQL version 9.5.5 in my application. I am using github.com/lib/pq as my database driver to connect to the database. One of my fields(resetdate) have the type date. I would ...
go
0
votes
1answer
22 views
PostgreSQL: Update primary key, avoid conflict
I want to update the primary key in several rows of a table. If all rows were updated, the key would be unique again, but the update of the first row results in a temporary conflict with the key of ...
0
votes
1answer
13 views
how to split the strings and store it into different row
i have table name as res_schedule.In that table i have field as sq_name_new1.
the values inside that field is as follows:
sq_name_new1
Gawade & Sushil & Arvind(Ramchandra Ankush Gawade,Arvind ...
1
vote
1answer
21 views
How to remove f1 from returned column when using row_to_json in postgresql?
I am using row_to_json to transform the columns into json.
this is my query:
Select row_to_json(row(func1('cust_1')));
note func1 is an sql function that takes one varchar and returns 3 floats
...
1
vote
0answers
15 views
Crosstab function and Dates PostgreSQL
I had to create a cross tab table from a Query where dates will be changed into column names. These order dates can be increase or decrease as per the dates passed in the query. The order date is in ...
0
votes
1answer
15 views
PostgresSQL: Permission denied for large object
When migrating from my development to staging environment, I encountered the following error during a hibernate sql call to the postgresSQL database on the staging server.
There is no error in the ...
0
votes
0answers
22 views
Sqlite error when deploying to Heroku
I've searched everywhere for an answer to this questions, but none of the solutions seem to account for the difficulty I'm having.
Currently, I have Sqlite in my :development, test do and Postgres ...
0
votes
0answers
12 views
Why does model for PostgreSQL not return values of linked tables?
I have Asp.NET MVC5 app and try to write a model for PostgreSQL using Npgsql and EntityFrameworkCore.
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System....
1
vote
1answer
23 views
Does Postgres use local resources when connecting to a server?
I am new to Postgres and wanted to understand if i make a remote server for Postgres and use PGadmin to connect to the remote server, will it use local resources to run the queries?
I tried it on a ...
1
vote
1answer
40 views
Unknown duplicates from querying a nested JSON
I would like to do text search in a JSON object in a table.
I have a table called Audio that is structured like below:
id| keyword | transcript | user_id | company_id | client_id
-------------------...
1
vote
2answers
36 views
Best practice for updating production database?
I am new to programming. I am using Rails 4 and Postgres as database in production. When I change my database structure, what is the best practice to update production database when deploying using ...
0
votes
1answer
37 views
Search a series of date time entries
I have a weird problem I'm trying to solve.
In my model I have a start_time and and end_time, we will refer to the model as “cost”. It's associated with an item.
The item will change ...
-1
votes
2answers
27 views
SQL RETURNING id always give back 1 as result
i use CodeIgniter and PostgreSQL
I write SQL like this :
$sql = "INSERT INTO(a,b) VALUES (1,2) RETURNING id";
I execute that query like this :
$id = $this->db->query($sql);
The question is ,...
0
votes
0answers
16 views
How should I deal with my UNIQUE constraints during my data migration from Postgres9.4 to Greenplum
when I execute the following sql (which is contained by a sql file generated by pg_dump of Postgres9.4) in greenplum:
CREATE TABLE "public"."trm_concept" (
"pid" int8 NOT NULL,
"code" varchar(100) ...
0
votes
1answer
29 views
Cannot update the timestamp with timezone in postgres using golang
I am using golang and postgreSQL version 9.5.5 in my application. I am using "github.com/lib/pq" as my database driver to connect to the database. One of my fields have the type timestamp with ...
0
votes
0answers
33 views
Where I should put a complex sql on a rails project?
I would like to know what is the best way to keep in maintenance and call a complex query due to using Active Record is not a good practice to call stored procedures and my query contains a temp table ...
0
votes
0answers
15 views
Query on a query returns different results to the bare query
If I define the following in my postgresql database:
create table t1(i int, c varchar);
insert into t1 values(1, 'One');
insert into t1 values(2, 'Two');
create table t2(i int, c varchar);
insert ...
0
votes
1answer
18 views
width_bucket not returning empty buckets
In postgres 9.5.3, width_bucket() is returning fewer than the specified number of buckets if there are no values to fill some of the buckets. Is there a way to make these empty buckets show up?
...
1
vote
1answer
24 views
Postgres function upper doesn't work for cyrillic (version 9.4)
This question is similar to Postgres doesn't search case-insensitive unicode regex
But it appears that upper function doesn't work as well?
Also, it seems that encoding on server utf8
I use ...
0
votes
1answer
24 views
Postgres error “ERROR: INSERT has more target columns than expressions”
I have the following tables :
CREATE TABLE public.participant_audit
(
participant_audit_id bigint NOT NULL DEFAULT nextval('participant_audit_participant_audit_id_seq'::regclass),
participant_id ...
0
votes
0answers
6 views
Viewing the contents of Heroku Postgres's memory
Heroku outputs postgres stats to the logs periodically. They're usually in the below format (formattted here for readability)
source=DATABASE
sample#current_transaction=175926
sample#db_size=...
