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 ...
4
votes
5answers
7k views
Prevent duplicate values in LEFT JOIN
I faced a situation where I got duplicate values from LEFT JOIN. I think this might be a desired behavior but unlike from what I want.
I have three tables: person, department and contact.
person :
...
0
votes
3answers
27 views
Sequelize - How do I “select” instances of a model if a relation exists to another model
I'm using PostgreSQL in a Node.JS app and I'm using SequelizeJS as an ORM. I have the following query:
Let's say I have two models:
User(name,email)
Category(name,createdBy)
Category.createdBy is a ...
-2
votes
0answers
9 views
postgres: Multiply column of table A with rows of table B
Fellow SOers,
Currently I am stuck with the following Problem.
Say we have table "data" and table "factor"
"data":
---------------------
| col1 | col2 |
----------------------
| foo | ...
0
votes
1answer
18 views
Can't use to_date() on aliased column
I'm trying to sort by group and sort by month. Since I am using to_char(date_field, 'Month') as month to get the month as a string instead of a date. This causes the results to have the months ...
0
votes
2answers
21 views
Rails JSON query combined with other data types
I have a Contact model with a string and json attribute
the string attribute stores the phone_number and the json attribute stores some other data.
I want to run a query like this
Contact.exists?(...
0
votes
1answer
24 views
Trigger function to check if student has classess
I am making a database in Postgres that register students for classes.
I am stuck at creating function checking if a student that want to enroll for a course isn't having any other courses in that ...
0
votes
0answers
12 views
Postgres, could not create database and list existing databases
I'm running postgres on a virtual machine in Azure and i found a lot of abnormalities, like if the version of postgres is specific for a cloud platform (i had to log in as postgres-xc instead of ...
azure
1
vote
2answers
28 views
PostgreSQL - Insert data into multiple tables simultaneously
I am having a data insertion problem in tables linked by foreign key. I have read in some places that there is a "with" command that helps in these situations, but I do not quite understand how it is ...
-1
votes
0answers
8 views
Rails: Converting epoch value to datetime in india timezone
I have stored epoch value of datetime in my database. I want to find datetime from the epoch value. to find datetime from epoch i am using postgresql to_timestamp function.
Below is my ruby on rails ...
4
votes
2answers
52 views
Restore database state after integration test using Docker?
We are using PostgreSQL along with EAR deployed to JBoss. During the build process we have a development database dump that is used then in integration test: special artifact is deployed and tests ...
64
votes
8answers
62k views
List stored functions that reference a table in PostgreSQL
Just a quick and simple question: in PostgreSQL, how do you list the names of all stored functions/stored procedures using a table using just a SELECT statement, if possible? If a simple SELECT is ...
0
votes
1answer
16 views
Thymeleaf does not see reference from Controller
I'm trying to show all Customers from my local database(PostgreSQL) on the html page using Thymeleaf. My project is using Spring Boot.
Connection and getting data from database is not the point of ...
0
votes
2answers
31 views
Postgres Dynamic Query
I have scenario were I have a master table which stores db table name and column name, I need to build dynamic query based on that.
CREATE TABLE MasterTable
(
Id int primary key,
caption ...
0
votes
2answers
42 views
Converting from VARCHAR to DATE
I have a db where I have converted 2 date columns to varchar for the purpose of getting 1 column substringed into another. However, now I don't seem to be able to convert the datatype when I try to ...
5
votes
2answers
4k views
How to use Spring StandardPasswordEncode and Get Salt Generate?
How do I encrypt a password insert it into the db and after the comparison when he will want to connect?
I would use StandardPasswordEncoder Spring security 3.1.4 to encrypt my password and insert ...
0
votes
0answers
10 views
Node.js Connects to RDS Postgresql and runs query from my local machine but will not work running in Lambda
I am looking for an AWS expert here - I have a Lambda function created and packaged appropriately using Claudia with the PG npm package for connection to an RDS Postgresql instance.
When the ...
2
votes
2answers
99 views
Calculating levenshtein distance between two strings
Im executing the following Postgres query.
SELECT * FROM description WHERE levenshtein(desci, 'Description text?') <= 6 LIMIT 10;
Im using the following code execute the above query.
public ...
144
votes
11answers
138k views
psql: FATAL: role “postgres” does not exist
I'm a postgres novice.
I installed the postgres.app for mac. I was playing around with the psql commands and I accidentally dropped the postgres database. I don't know what was in it.
I'm ...
0
votes
0answers
12 views
pgAdmin 4 does not show result after query execution (postgreSQL)
I have a big SQL file which have some queries. I wish to know how many time takes the execution of the entire file.
I open the file in "Query Tool" in pgAdmin 4 and it takes 1 minute to execute "...
0
votes
0answers
20 views
Vault project for storing private keys
There is RESTful web-service which runs on Tomcat and takes private key from PostgreSQL database to sign some data. I am considering to use Vault project for private key management. As I understand, ...
0
votes
0answers
19 views
Update json array in postgres
I have a data field that looks like this :
{ "field1" : [{"name":'name1',"value1":true},
{"name":'name2',"value2":false}
],
"field2" : [{"name":'name1',"value1":true},
...
1
vote
0answers
23 views
Rails Capistrano first deploy error with postgres in migration task
I'm running:
cap production deploy
Everything works fine until this step:
** Execute deploy:migrating
01:05 deploy:migrating
01 ~/.rvm/bin/rvm default do bundle exec rake db:migrate
01 == ...
0
votes
0answers
13 views
join function call and pass result of aggrete function as an argument
Suppose I have a classroom and students. Suppose I want to filter students by age and pass results to function joined on classromId. This function is called once per class. It would be great if I ...
6
votes
0answers
430 views
Cannot generate C# class from PostgreSQL view using Npgsql + EF6 code first
I am trying to use entity framework code first method to connect to PostgreSQL database, and when I use entity data model wizard in visual studio to generate C# class from database, it can generate ...
-1
votes
1answer
31 views
syntax error at or near “as”
Here is an error I got:
syntax error at or near "as"
CASE WHEN ver_desc_txt = 'BEST OFFICIAL PROJECTION' THEN round(sum(d.spcl_prc_grs_sls_amt::double precision / f.exch_rate)::numeric, 2) as ...
0
votes
1answer
12 views
Sequelize - function on column in a where clause
I have a Restaurant model with a property 'location' of the type GEOMETRY("Point"). I'm trying to write a query with sequelize that will give me all the restaurants in a certain radius:
models....
0
votes
2answers
11 views
How to deploy a rails app to heroku using C9
I am trying to deploy my rails app to heroku. However, it seems that there is a long process to do. I have to change my db but I do not know how! I also want to know how to push to heroku please!
...
0
votes
0answers
21 views
Generation of duplicate unique constraint names
when defining a unique constraint for a domain class, grails generates for the database a constraint-name which is based on the property name.
When using Postgres as a database the name will be '...
30
votes
10answers
20k views
Import psycopg2 Library not loaded: libssl.1.0.0.dylib
When I try to run the command:
import psycopg2
I get the error:
ImportError: dlopen(/Users/gwulfs/anaconda/lib/python2.7/site-packages/psycopg2/_psycopg.so, 2): Library not loaded: libssl.1.0.0....
0
votes
1answer
27 views
PostgreSQL built-in indexes vs. manually created: Performance aspect
I am trying to design an optimal index configuration for our application PostgreSQL DB and found that creating index manually for PK column seriously increases estimated performance. Initial idea came ...
0
votes
1answer
30 views
Access array of timestamp from composite-type argument in Postgres C Function
I have a user-defined type mytype :
CREATE TYPE mytype AS (
timestamps timestamp[],
last integer
);
I want to access values in timestamps field using C function. Here is what I have written ...
0
votes
1answer
21 views
PostgreSQL “Column does not exist” but it actually does
I'm writing a Java application to automatically build and run SQL queries. For many tables my code works fine but on a certain table it gets stuck by throwing the following exception:
Exception in ...
0
votes
1answer
42 views
Liquibase upgrade issue with MySQL
I am having problem in upgrading with liquibase. I have an application jbilling-community-4.1.1.
I want to migrate data from jbilling-community3.1.0 to jbilling-community4.1.1 but when I run this ...
0
votes
2answers
20 views
Explicit type conversion in postgreSQL
I am joining the two tables using the query below:
update campaign_items
set last_modified = evt.event_time
from (
select max(event_time) event_time
,result
from events
where ...
0
votes
1answer
28 views
Is there a way to estimate amount of memory required when collecting a large table from a database?
I have done some complicated computations inside my database using dplyr, containing one-to-many joins and then filtering results, so that I no longer really know whether my computer memory is ...
-2
votes
0answers
15 views
MySQL and Postgresql conection for data push and pull
I have two database one in MySql and other in Postgresql. We need to establish the connection between these Databse and perform push and pull of data between these DB.Please suggest if this is ...
1
vote
1answer
16 views
Triggers in Postgresql/postgis
I have a shapefile loaded into a postgis database. This shapefile is frequently updated by the source and thus my current process is:
Use shp2pgql with -a option to generate insert statements.
Run ...
0
votes
1answer
17 views
how to fetch the data which has only single entry in a column in the table in mysql?
I have a table named as scholar and it has a column studentid now i
want to fetch the data where studentid comes only once, one id can have multiple entry in the studentid column
below is my ...
0
votes
3answers
28 views
postgres many tables vs one huge table
I am using postgresql db.
my application manages many objects of the same type.
for each object my application performs intense db writing - each object has a line inserted to db at least once every ...
-1
votes
0answers
16 views
Is it possible to upload data to postgreSQL server using vba for excel?
I need to upload excel data to postgreSQL server using vba for excel. I need a button in excel so that if i just click on it the file gets uploaded to the server?
It would be great if someone can ...
0
votes
2answers
27 views
SQL cloning and converting a date column
(postgreSQL database)
I have a date column that appears to be in the format of 'yyyy-mm-dd' firstly I'd like to convert that said column to 'yyyymm'.
After that I'd like to clone that column to ...
0
votes
0answers
6 views
Londiste3 and pglogical replication on PostgreSQL Amazon RDS
I have two PostgreSQL databases, and one of the databases needs to replicate some of the tables from the other database (in real-time), we have been doing this with Londiste3 for quite some time now.
...
0
votes
0answers
16 views
Rails seed sql file that contains “COPY” statments
I am trying to seed sql file that looks like that:
COPY courses (id, name, "courseID", school_id, created_at, updated_at) FROM stdin;
1 Fundamentals of Programming CSCI150 1 2016-04-27 14:04:07....
4
votes
4answers
1k views
Partial matches with postgresql full-text search using texticle on Heroku
Trying to get searching working on Heroku using partial search
The following query generates an SQL error on Heroku, but works correctly in my locally version:
@events.search(params[:search]+":*")
...
0
votes
0answers
7 views
Automatically add (Alter) additional referential constraints of different schema
I have two tables T1 and T2: T1 T2 ---------- ...
0
votes
0answers
30 views
Automatically reference the foreign key of table with a primary key other table in different Schema?
I have two schema A and B with each having two tables p and q in Amazon Redshift.
A.p is defined as
CREATE TABLE A.p (
id int8 PRIMARY KEY,
val int8
)
and A.q as
CREATE TABLE A.q (
id ...
1
vote
1answer
17 views
docker postgres failed to start with specified port
I'm new to docker and I'm trying to run a postgres database docker with the following command :
docker run --name rva-db -e POSTGRES_PASSWORD=rva -e POSTGRES_DB=rva-db -d postgres -p 5432:5432
If ...
0
votes
1answer
19 views
Sorting of dates in order of date and then month such that records are sorted by date first and then by month
Can anyone please help me on sorting records(Postgres Database) as intended?
Following is result of a query on one of my table that i have sorted by date part of the whole date (ORDER BY extract(DAY ...
0
votes
0answers
9 views
Postgres - Trigger agregate data
I have a table in postgres looking like this:
id | dev_id | start_time | sample_ms | value
in this table i store data from a sensor, which could look like this:
1 | 23 | 2017-01-01 10:00:00 | 10000 ...
3
votes
2answers
215 views
Build a dynamic update query in psycopg2
I have to construct a dynamic update query for postgresql.
Its dynamic, because beforehand I have to determine which columns to update.
Given a sample table:
create table foo (id int, a int, b int, ...
