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
9 views
Rails 5 Postgres 9.6 - How many relationships / indexes is too many and will severely affect performance?
I am developing a Rails 5 app with Postgres 9.6 as the database. As I design the data-layer and the model relationships, I have come to the question: how many relationships (foreign indexes)is too ...
0
votes
0answers
13 views
how to compile C code with pthread and lpq
I have been trying to compile a very simple code that contains postgres, pthread and mutexes. now when I do it with pthread alone every thing is fine, same when I do it with postgress alone! but when ...
0
votes
0answers
6 views
Convert PostGIS polygon as GeoJSON in rails
I have some polygons saved in postgres using postgis and now I need some way to convert this data to a GeoJSON file so I can display it over google maps.
Here is an example of a polygon in postgis
"...
0
votes
0answers
5 views
How to sort a sequelize .where query using postgresql
When using Sequelize to query on an integer (the id of an object in this case) I try to do this:
var whereQuery = Sequelize.where(
Sequelize.cast(Sequelize.col('organization.id'), 'TEXT'),
{$...
-1
votes
0answers
14 views
PostgreSQL regexp_replace to remove dots in acronyms/initialisms only
I'm trying to write a regexp_replace in PostgreSQL to remove dots in acronyms/initialisms while retaining all other dots.
This text, for example:
"I downloaded party.doc while in a U.S.A. airport."
...
0
votes
1answer
10 views
Migrate association column in PSQL
I have a table that we've run into an issue because we made a wrong assumption that Table A associates with Table B but it should be Table C. It's only an issue now that we're adding a feature but I'm ...
1
vote
2answers
7 views
Logging = ALL issue on PostgresSQL 9.5 on Ubuntu 16.04
So I understand that ALL will get me the SELECTS which is what I would like to monitor for my application, however, the POSTGRES daemon is running a select of its own against the database and it is ...
0
votes
0answers
14 views
Postgres insert data from one table to another where column names match
Abstract
I have one main table with columns named A through Z. I have a dozen other tables that also have strictly letter-named columns (same data types too), just not all columns from A to Z. (e.g. ...
0
votes
1answer
21 views
Database Design Performance of Many Table vs One Table
Say I have an ANIMAL table,
CREATE TYPE VALID_ANIMAL AS ENUM ('Dog', 'Cat', 'Pig');
CREATE TABLE IF NOT EXISTS ANIMAL (
animal_type VALID_ANIMAL,
name TEXT,
owner TEXT,
.... many ...
0
votes
1answer
18 views
Postgres packages to install on AWS Linux EC2 instance to access a Postgres RDS
I have an RDS with PostGres as the database and an EC2 instance, Amazon Linux AMI. I will be installing a ruby on rails application on the EC2 instance. I see that one can use the following command on ...
0
votes
0answers
21 views
How to use 'LIKE' function to select array of Strings with JOOQ
I now want to use 'like' function with JOOQ to select data including array of string data by not case sensitive and partitial-match.
Table schema is:
CREATE TABLE favorites (
id int,
items ...
0
votes
0answers
9 views
Django postgreSQL schema
Stupid question (again - referring to this question) but all I really want to do is to change the default schema from public to whatever. Is this really as complex as described above question? The ...
0
votes
0answers
6 views
Connect Infomatica Cloud to Heroku
I have created a postgres db on the Heroku platform and I'm trying to connect to it using Informatica Cloud (ICS) ODBC and mySQL connectors with the Heroku credentials. No success, and I am waiting ...
0
votes
2answers
18 views
Postgresql - Conditional Join if data exist
My current query show the data from the table called "Buque" and has some references from another tables. The problem is when i execute the query it never shows the result because it consumes too much ...
0
votes
0answers
21 views
Trouble Migrating database schema to Heroku (Postgres)
I have everything set up in my local environment and the code for my website up on the Heroku server, I'm just having serious trouble getting the schema to migrate to the PostgreSQL database on the ...
0
votes
1answer
31 views
PostgreSQL: Count based on distinct entries in different columns
The following is the schema of the txn table -
ID | SrcNO | DstNO | SrcCountry | DstCountry | Type |
1 A B USA UK RECV
2 A ...
0
votes
0answers
16 views
java postgresql ERROR: cached plan must not change result type
I'm having a weird problem on my application, I'm receiving this error on one of my query's:
SEVERE: java.sql.SQLException: ERROR: cached plan must not change result type.
But only on my ...
0
votes
2answers
29 views
How do I return the branches which have no manager? (SQL)
I am trying to return the branches which have no manager.
And this is what I tried:
SELECT branchno, city FROM branch
WHERE branchno IN
(SELECT branchno FROM STAFF
WHERE position <> 'Manager');...
0
votes
0answers
23 views
SQLAlchemy - select via JSON field, “Operator 'getitem' is not supported”
I've got a model that is something like this:
class Account(Base):
settings = Column(MutableDict.as_dict(JSON), default={})
An example object might be something like this:
a = Account(settings={...
1
vote
3answers
52 views
what can I do when distinct isn't an option on joins
I'm working on a pretty large dataset and running into an issue right now that I'm not sure is solveable with the current approach. I am fine posting this as I didn't come up with the initial example, ...
0
votes
0answers
12 views
React/Redux - Express/PostgresSQL 404 - Not Found Error on localhost Proxy
This project I've been coding was working perfectly a couple days ago and I go and open it today and I'm getting a 404 Not Found error when my front end trys to talk to my back end. I've reverted to ...
0
votes
3answers
33 views
PostgreSQL - How to select the first consecutive group having same value
I have a table with pk and dept columns:
pk dept
-------
27 A
29 A
30 B
31 B
33 A
I need to select the first consecutive group, that is the first successive set of rows all having the same dept ...
1
vote
1answer
29 views
What am I doing wrong in this query
I've simplified this down a bit since the literal data is pretty massive but a very simple example will suffice. I'm working on a query where because of the massive amount of data, I'm looking to do ...
-2
votes
0answers
14 views
How to connect my iOS app to postgreSQL database? [on hold]
I need to create an iOS app (using Swift), that grabs all the data from an existing postgreSQL database. I've looked for an answer everywhere, found some projects on github, but it all was not ...
0
votes
0answers
6 views
Hive metastore db PostgreSql. Following is the error is “FATAL: the database system is shutting down”
The cluster(all the servers turned off) is off due to unexpected power failure which happened this morning. I started the cluster after the power is back but Hive is not able to connect with ...
0
votes
1answer
29 views
FOUND variable set to `true` when there are NO records
In a PLPGSQL function I have some lines to check if a record exists. Well, the FOUND variable is set to TRUE even if there are no records. Very weird thing.
The lines where I am testing are:
query:='...
0
votes
1answer
26 views
How to get multiple rows in pgsql query result
I'm using pgsql 9.3.
I have two tables which are identical. One is the master table and other is temp table. Every time there is an update, data is written to temp table, if data is not present in ...
0
votes
0answers
24 views
Why two counts in the same query are not working
I have four tables.
Category, Product, Sale and Review. Category has one or more products, a product has one or more review and a product is in one or more sales.
Now I need to know how many products ...
1
vote
1answer
24 views
Execute queries at some previous point in time
Is it possible to "remember" some point in time, and give it an identifier, so that I can later reference it in queries and get the results from the database as it was then?
If such a mechanism ...
0
votes
1answer
26 views
Postgres LEFT JOIN and COUNT
I'm having a bit of trouble debugging a SQL query and would really appreciate some help.
Here is the query:
SELECT p.id, p.type, p.submission_id,
p.title, p.description, p.date, extract('epoch' ...
0
votes
1answer
17 views
Extract query parts from a SelectQuery (or equivalents)?
I have the following situation:
Class A extends B. Other classes, such as C, D, etc. also extend B In Class B, I would like to situate logic with regard to general information common to A, C, and D.
...
0
votes
0answers
27 views
Why array_agg() is returning empty array in postgresql?
I have an integer type column named as start. I want to make an array by the values of this column. It seemed to be very easy and I used array_agg(), but it is giving empty array as output. Following ...
0
votes
2answers
32 views
C# vs SQLEditor — Npgsql.PostgresException 42703: column “username” does not exist
So on my behind code in C# I have a Select Statement:
Select username from vw_members where username = 'SomeValue'
But that returns this error on my aspx page:column "username" does not exist
If I ...
0
votes
2answers
20 views
PL/pgSQL - return single record from FUNCTION
I am creating a SP using PL/pgSQL:
CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_year INT, p_uid INT)
RETURNS TABLE (
field1 INT,
field2 INT
)
AS $datarows$
DECLARE var_r record;...
1
vote
2answers
30 views
Format JSON Postgresql
I'm using inner join to join 3 tables, Owner, Store and Machine.
I'm trying to view output JSON from multiple tables like this:
SELECT ow.*, st.*, ma.*
FROM owner ow
INNER JOIN st.store ON ow....
0
votes
1answer
24 views
Need one table count for all record and status count for activate and deactivate separately (SQL)
I need one table count for all record and status count for activate and deactivate separately. This is what I have so far. Can anyone help?
SELECT id COUNT(*),(SELECT id COUNT(*),(SELECT COUNT(*) ...
0
votes
1answer
12 views
Error executing Postgresql query, using Python3.4, SQLAlchemy 1.1.3
i'm trying to update some records in an old "PostgreSQL 8.3.19" database (server_encoding='UTF8'), and i get this error:
(psycopg2.DataError) invalid byte sequence for encoding "UTF8": 0x8d
HINT: ...
1
vote
1answer
31 views
How to use 'ANY' function with JOOQ
I currently use JOOQ to get data from postgresql DB. Then I have faced a problem to get data which data-type is array type.
Current condition is like below:
Table schema is:
CREATE TABLE ...
0
votes
3answers
62 views
How to get data from 2 tables based on search keyword rails
I have 2 tables schools and private_schools and they are not related to each other. What I need to do is to search the data from both tables to populate the results.
Both tables has the attribute ...
-1
votes
0answers
17 views
Copying data from JSON file into a Database (PostgreSQL)
The Following json file
{"deptno":70,"dname":"HUMAN RESOURCES","loc":"ISTANBUL"}
I want to import a table called scott.dept
Tried this:
copy scott.dept
from '/temp/jason.json'
credentials '<aws-...
0
votes
0answers
30 views
PosgreSQL query (EF + Devart) fails in application, but works in PgAdmin
I have a problem with one of my SQL (PostgreSQL) queries generated by Entity Framework with Devart connector. After exactly 60 seconds (every time), I get an error:
System.Data.Entity.Core....
2
votes
0answers
24 views
How to refer OLD record in trigger when statement is INSERT ON CONFLICT DO UPDATE?
I am writing a trigger like this,
CREATE OR REPLACE FUNCTION insert_check()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.id = '' OR NEW.name = '' THEN
RAISE EXCEPTION 'id or name cannot be empty ...
0
votes
0answers
54 views
Calculate working hour between two date without Week end
I use PostgreSQL and I want to calculate the working hour without Sunday in pure SQL.
The result need to be in minute.
The data are for example :
begin_date, end_date
2016-12-30 08:58:31,2016-12-...
0
votes
0answers
13 views
postgresql text type to date
so I am pretty new to this, but I would like to convert a text data_type into a date type (preferably into something like this yyyy-mm-ddThh:mi:ss.mmmZ date/time with timezone).
Now, I have found the ...
0
votes
0answers
12 views
Connecting to postgresql database remotely
how to Connect postgresql using bookshelf.js from a remote system instead of using "host: localhost"
0
votes
1answer
28 views
Rails: How to check connection with Postgresql
I wrote application in Rails. It use Postgresql via VPN. Sometimes VPN is going down and database is not available. How I can check connection with database to avoid error on web page ?
Error :
PG::...
0
votes
1answer
20 views
Get the start and end date by passing week number and year
I am using EXTRACT(WEEK from doc_date) to extract the week number from a given datetime.
Is it possible to extract the start and end date by passing week number and year from Postgres?
-1
votes
1answer
30 views
invalid input syntax for type boolean in if condition
I am trying to create the following function in Postgresql
CREATE OR REPLACE FUNCTION emat_proc_ad_user_login(user_id_in character varying, password_in character varying)
RETURNS character ...
1
vote
2answers
51 views
Turning arbitrarily many rows into columns in PostgreSQL
I have a table in Postgres that was designed to capture information in unstructured form and rebuild it. I need to re-apply some structure when exporting data from that table and am struggling.
...
0
votes
1answer
18 views
how detect MSVCR120.dll is missing with java project
i'm from indonesian, and i can't speak english
i plan run postgresql binary with my project (With JAVA)
for run server postgresql, i have 2 way :
1. with *.bat file
2. include in my java project
...
