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
6 views
Postgres pattern matching search by minimum column appearances
I'm curious if the following is even possible, and I'm looking to pointed in the right direction.
I have a schema like the following (please note this is just an example)
CREATE TABLE example (id ...
0
votes
1answer
14 views
PostgreSQL jsonb nested pattern matching text search across multiple rows
as stated in the title, I am attempting to do a query that preforms a full text search on multiple rows, agains a jsonb data type, with nested data, the problem is as follows:
CREATE TABLE books (id ...
0
votes
0answers
10 views
Meteor-postgres PACKAGE_DIRS
I'm about to use meteor-postgres (https://meteor-postgres.readthedocs.io). As i can see it has it's own implementations of "accounts-password", "accounts-base", "accounts-ui" and mayby other packages. ...
0
votes
1answer
30 views
What does “H” in “HStore” mean in PostgresDB data type name?
I tried searching and after some search here I decided to reveal this secret with YOUR help.
0
votes
0answers
11 views
Apollo/GraphQL: Field Type to Use for Timestamp?
I'm storing a value to a postgres field that is of type timestamp with time zone. I was defining the field as an int in my Apollo schema, but I'm getting this error message in the resolver:
column "...
1
vote
1answer
19 views
PostgreSQL join with similar address
I am trying to join data from disparate sources. The only common field to join is address. In table 1 , address has extra data (representing neighborhood) between street and state. Is there a way to ...
1
vote
0answers
19 views
How to maintain maximum number of records in a table. Postgres
I have the following problem, I have a table with a foreign key and a creation date column.
At the time a new record is inserted, I need to check how many records exist with the same foreign key, and ...
0
votes
0answers
15 views
org.hibernate.MappingException: No Dialect mapping for JDBC type: 2002 when I'm trying to sorted FullTextSearching select
I receive this exception when I try to sorted FullTextSeaching:
Query query = em.createNativeQuery("SELECT p FROM persons p, plainto_tsquery(:keyWord) q WHERE q @@ p.tsvector ORDER BY ts_rank(p....
-1
votes
0answers
15 views
PostgreSQL: ARRAY(subquery)?
Where is the PostgreSQL documentation for ARRAY(subquery)?
I've seen it called "the ARRAY function" but didn't find it in the list of PostgreSQL function names returned by: SELECT proname FROM ...
0
votes
2answers
21 views
Inserting data into database PHP without loading new page
I am struggling with inserting data into my database. The thing is that if I do "form action = "adduser.php" method = "post" then it works. However, it loads a new page and I don’t want that.
...
0
votes
1answer
22 views
How to split data from one column to three columns in another table? SQL
I have table (inf) with two columns id_student and hobbies like this:
ID_student = 1
Hobbies = "music, cooking, bassguitar"
and I want to copy the hobbies by splitting them to another ...
0
votes
1answer
23 views
Rails 5: query has_many / belongs_to namespaced models
I have has_many / belongs_to relationship:
models/media/media.rb
module Media
class Media < ApplicationRecord
has_many :positions, dependent: :destroy, inverse_of: :media, class_name: '...
0
votes
1answer
18 views
Postgresql unaccent() equivalent in MySQL
i have to compare two strings in a query like following:
SELECT *
FROM MY_TABLE
WHERE column LIKE '%keyword%';
But i want to compare unaccented values of both column and keyword. Is there an ...
0
votes
0answers
14 views
org.postgresql.util.PSQLException: ERROR: relation “dish” does not exist
I created java(spring) app on heroku, connected postgresql to it. When I make request on app, log shows
WARN 4 --- [io-14883-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, ...
0
votes
2answers
38 views
Postgres 9.5 - Querying array length of a nested JSON element
I have a Postgres table that contains a JSON field, which identifies the images associated with the given record. The contents of the field look like this:
{"photo-verification":
{"photos": [
...
0
votes
1answer
23 views
Get the value from nested JSON in Postgres
I have a table called "Audio" with a column "transcript" as the following:
{"transcript": [
{"p": 0, "s": 0, "e": 320, "c": 0.545, "w": "This"},
{"p": 1, "s": 320, "e": 620, "c": 0.825, "w": ...
-1
votes
1answer
13 views
How are solr and postgreSQL connected in ckan?
I can't find how solr link to postgreSQL in ckan.
I wrote solr_url = http://127.0.0.1:8983/solr, and sqlalchemy.url = postgresql://ckan_default:123456@localhost/ckan_default in my development.ini ...
1
vote
2answers
18 views
insert into postgres db with for php statement
I'm writing a code with this function:
function foo() {
$input = array('bar');
for ($i=0;$i<1000;$i++) {
$db=connection_pgsql() or die('Connessione al DBMS non riuscita');
...
3
votes
1answer
35 views
Is it possible to add or remove columns without a migration file?
I'm integrating Facebook login for my app. I have a model called "User", and its supposed to have two columns: "provider" and "uid". Everything's fine on development, but I just noticed that on ...
1
vote
0answers
27 views
Debugging slow Postgresql 9.3 COMMITs
With slow query logging turned on, we see a lot of COMMITs taking upwards of multiple seconds to complete on our production database. On investigation, these are generally simple transactions: fetch a ...
1
vote
1answer
15 views
Having an issue inserting data into Postgresql using Npgsql and VB.net
Can someone please look at my code and possibly point me to why it is not allowing me to insert data into my Postgres database? I'm creating a Comic Book database for my collection.
Everytime I click ...
0
votes
0answers
22 views
Delete from database (PostgreSQL) using php and ajax
I have a list of options (in this case: hotel names), which is populated from a database (PostgreSQL). What I want to happen is:
User selects an option --> user presses delete button --> selected ...
0
votes
1answer
22 views
How to modify multiple rows of a single column in postgresql?
I have following columns and data set to my table. I need to fill the multi column as id*number (i.e. the first value in multi column would be 1*1025=1025, second value would be 2*2587=5174 and so on. ...
0
votes
1answer
19 views
How to write a raw SQL query in Django QuerySet?
I want to use following SQL query in Django but I was unable to use that after trying directly with raw SQL. The SQL works directly while executing on PostgreeSQL query window but on Django did not ...
0
votes
1answer
13 views
Count on this LEFT OUTER query is returning 1 when I want 0
I have the following query:
SELECT
l.id, l.name, l.store_id, COUNT(1) as reviewCount
FROM
locations l
LEFT OUTER JOIN
reviews r ON l.id=r.location_id
GROUP BY
l.id
ORDER BY
...
0
votes
1answer
38 views
postgreSQL: How Select the nearest date that is not null
I got a date that I want to find the all records in the past that got the same month and day.
The problem accrues when there is no such date in the same year. For example, the 29th February.
My goal ...
0
votes
0answers
13 views
docker-compose rails app doesn't find db when using up but does when doing run
I have a simple Rails application that I'm trying to run using Docker. When I run docker-compose up it runs but when I load a page it tells me that my db doesn't exist. Even though when I run docker-...
1
vote
1answer
19 views
In PHP PDO how to get “RETURNING” clause values of PostgreSQL upsert query
I have this upsert query written in postgreSQL
$statement = 'INSERT INTO "CharactersUnlockToBuyLevels"
("CharacterId", "LevelId", "AmountToBuy", "EagleStatueId", "Location",
...
0
votes
1answer
13 views
PostgreSQL Connection String with ODBC driver in C#, Keyword not supported: driver
Lately I've been trying to connect a PostgreSQL 9.6 database on my C# project but I've been struggling setting up the connection string correctly due to "Keyword not supported:driver".
After having ...
1
vote
1answer
11 views
Difference between SDO_CONTAINS and SDO_RELATE with MASK=CONTAINS?
What is the difference between following oracle spatial query? Does both function return same result? I am confused with MASK=CONTAINS
SDO_CONTAINS (col1_geometry,col2_geometry )='TRUE'
SDO_RELATE (...
0
votes
1answer
21 views
I am writing postgresql data to file using bash and psql. I need the column headers written to the output file
Script:
#!/bin/bash/
PGPASSWORD='*******' psql -h host_name server_name user_name -t -A -F "," -c "select b.id AS booking_id, b.hotel_id as hotel_id, b.invoice_no as invoice_no, b.guest_id as ...
0
votes
1answer
24 views
PostgreSQL matrix transformations
I have a PostgreSQL table called test which has 2 columns - (1) id & (2) matrix as follows: -
create table test (id integer,
matrix double precision[]);
insert into test (id, ...
1
vote
1answer
40 views
Boolean column in multicolumn index
Test table and indexes:
CREATE TABLE public.t (id serial, cb boolean, ci integer, co integer)
INSERT INTO t(cb, ci, co)
SELECT ((round(random()*1))::int)::boolean, round(random()*100), round(random(...
-1
votes
1answer
20 views
Is there any options to use SQL Server tables in postgresql?
I want to see and query data from SQL Server tables in postgresql. How can I do it? I'm using SQL Server 2014, POSTGRESQL 9.4. Windows 10
0
votes
1answer
31 views
CakePHP cannot connect to Postgres. Pgadmin4 can however
CakePHP3 cannot connect to my PostgreSQL database.
My setup is the following:
Windows 10 host
CentOS7 Virtualbox VM guest
PostgreSQL 9.6
Apache 2.4
PHP 7
Error:
CakePHP is NOT able to connect to ...
2
votes
1answer
99 views
How to improve PostgreSQL performance on INSERT?
I have written a Node.js application that writes lots of records to a PostgreSQL 9.6 database. Unfortunately, it feels quite slow. To be able to test things I have created a short but complete program ...
-5
votes
0answers
34 views
I have up my web into the server, it worked fine locally but it doesn't work once up to the server and giving following error
It says, root cause as follow,
javax.servlet.ServletException: org.postgresql.util.PSQLException: The connection attempt failed.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(...
0
votes
0answers
21 views
sqlalchemy query computed column as model property
Given the following query, is there any way I can have the number_of_kids as property of returned models so I can use model.number_of_kids notation?
Not that I don't want to load the whole list of ...
0
votes
0answers
16 views
Deleting or update rows based on multiple tables
I'm trying to find a way to remove all "parents" from a team where none of their kids are on the team any longer.
I have a PostgreSQL 9.5 table called parent_child like so:
Column | ...
0
votes
1answer
15 views
Post method always returning 503: timeout message while using nodejs to connect to Postgre SQL db
I am running a nodejs app on Heroku and using Postgre SQL db. I am using a post method add_user to add some data to the table users. Everything works fine and data is correctly entered in the table ...
0
votes
1answer
20 views
How to calculate area around a geometry using postgis?
I want to convert the following oracle spatial function into postgis spatial function to calculate an area around a geometry
sdo_geom.sdo_area(geometry, 0.005, 'unit=SQ_MILE')
1
vote
0answers
19 views
Setup Visual Studio's Server Explorer to work with postgresql
I'm trying to setup Visual Studio's Server Explorer to connect to a postgresql database. I am using ASP.NET Core & EF Core with npgsql.
This feature works fine with SQL Server but there's no ...
1
vote
0answers
10 views
How to solve Php Error Cannot instantiate abstract class sfDoctrineRecord on Symfony 1.5.9?
I have a problem when I want to use the command on terminal (linux)
php symfony doctrine:build --all --and-load
The terminal displays the next message:
>> doctrine generating sql for models
...
-1
votes
1answer
20 views
Can someone please show me how to upload multiple files with Django into a PostgreSQL database?
I've been trying to figure this out for weeks to no avail unfortunately. Even after looking through hundreds of web pages and tutorials, I still can't figure it out.
1
vote
1answer
12 views
Best way to query postgres db from django to list all users (tab1) which have active book borrows (tab2)?
I have a simple application in Django. The simple logic is kind of book library.
Tab1. stores the users:
class Readers(Model):
last_name = CharField(...)
first_name = CharField(...)
...
0
votes
2answers
64 views
How to join these tables in SQL
I have the following tables:
match
id | rival_team
----------------------
| 1 | chelsea fc
| 2 | real madrid
player
ID | name | last_name |
---------------------...
0
votes
1answer
8 views
Naming for 'id' column in massive.js
I have an existing table in which the primary key column is called gid instead of id. When I use a massive document query like the one below I get Error: column "id" does not exist. Can I specify that ...
1
vote
1answer
19 views
Postgresql query to get sum of tree
Hi guys I would like to ask about postgresql and what could be the best query to get sum of column when you have table of elements that has some descendants of more levels ie.
id &...
0
votes
1answer
30 views
SQL update records with ROW_NUMBER()
I have a table called 'cards', which has a column called 'position'
How can I update/set the 'position' to equal the row number of each record, using ROW_NUMBER()?
I am able to query the records and ...
1
vote
1answer
24 views
Slow Postgres 9.3 queries
I'm trying to figure out if I can speed up two queries on a database storing email messages. Here's the table:
\d messages;
Table "public.messages"
Column | ...
