Questions tagged [postgresql]

43153 questions
1

votes
0

answer
97

Views

Docker-compose: bind Celery to Postgres database

My Docker app runs with Flaskas backend and Celeryas an asyncronous task manager. Task results are then dumped in a SQLalchemy database owned by Postgres. However, I am not being able to make Celery interact with Postgres. STRUCTURE docker-compose-dev.yml web/ entrypoint.sh celery_worker.py migrati...
data_garden
2

votes
0

answer
19

Views

What's the most optimal query in PgSql to find the nearest neighbor in a huge dataset?

I have a huge table (about 40 Millions rows) called nearest_spot representing lines (in the linestring format) and the closest spot they're to (there is about 1500 different spot, stored in another table). The nearest_spot table comes like this : data_id || spot_id || spot_name || link_geom Where d...
Adrien
1

votes
1

answer
684

Views

How to use postgis type Geography with spring-boot and postgresql?

I have a Spring-boot project with hibernate. Also, I have a postgresql database with postgis extension. I really want to make use of the postgis extension geography type. Unfortunately, it seems that Hibernate does not support the geography type, only type geometry, which is unsufficient for my need...
Ville Miekk-oja
1

votes
1

answer
908

Views

Postgres - Can not 'SELECT' from tables even though my role/user has been granted 'READ'

I have an admin role/user and a developers role on postgres. The developers role enherits attributes from a 'readaccess' role which I created for convenience in the future. If I run \du I get: Role name | Attributes | Member of ------------...
x3nr0s
1

votes
2

answer
518

Views

two phase commit in nodeJS application

I am currently working on a nodeJS application that will soon need to write simultaneously on two PostgreSQL databases. This is meant for redudancy purposes: the writing operations must be made in the same time on both databases, or rollbacked if there is anything wrong with one of the databases. In...
Pikuni
0

votes
4

answer
15

Views

auto increment field in select query statement

I have a table with 2 columns, name and id with these following values Name Student_ID jack 231 dave 425 mike 001 lily 128 liam 358 I would like to add a new column to this result with auto incremented sequence values. I have tried using the below query. But i g...
0

votes
0

answer
9

Views

is calculated table possible?

Is it a good idea to create a calculated table which contains the results of complex calculation? For examples, the calculation involves regression and prediction etc. As the results will be used in future analysis frequently, I prefer doing the calculation once when new data come in instead of calc...
Hawii Hawii
1

votes
3

answer
185

Views

How to insert 780k records Spring Data Postgres

CSV file will be uploaded in Amazon S3 bucket. Current Implementation: Reading CSV file using OpenCSV utility's readAll which returns List Map every column value to an entity object Create a List object (Current Size :: 15000) Invoke Spring Data CRUD Repository saveAll(List objects) Out of 780k reco...
Phaneendra
1

votes
1

answer
774

Views

Why do we need template0 and template1 on postgresql?

I'm a beginner on postgresql. I have faced many difficulties while installing and connecting the database with the pgadmin, I learned a lot of things from the mistakes I do. Now my doubt is why should the \l shows the template0 and template1, I searched on the web but unfortunately I didn't find the...
Ruth-The Glix
1

votes
2

answer
1.7k

Views

Golang: Connect with postgreSQL schema

I am looking to connect and query to a PostgreSQL. But I only want to connect to a particular Schema. As per the doc (JDBC) we can use jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema or update As of 9.4 you can specify the url with the new currentSchema parameter like so: jdbc:postg...
Abhishek Soni
1

votes
2

answer
970

Views

Using files generated by other recipes in Yocto

Note: The general question is in bold at the end of this post. I'm trying to build PostGIS 2.2.7 with Yocto (Rocko) for my Linux i.MX6 based embedded system. First of all, I have installed PostgreSQL 9.4.15 from OpenEmbedded Layers (https://layers.openembedded.org/layerindex/recipe/5558/) and all th...
danlor
1

votes
1

answer
248

Views

How to change column type from oid to bytea in PostgreSQL

At first place, I got this error: org.postgresql.util.PSQLException: ERROR: column xxxx is of type oid but expression is of type bytea Hint: You will need to rewrite or cast the expression. Position: 318 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182) ....
Spider
1

votes
2

answer
51

Views

Update a column with values of the same column in the same table

In postgres I would like to update as below. My table product has these columns uuid sold product_id variant_id data I would like to update the uuid of all the sold=true records with the uuid of the records with that product_id and variant_id. For example If this is my table I would like to update t...
Surya
1

votes
1

answer
13

Views

problem using ORDER BY in postgres with UNION clause

I am trying to run this query in postgresql SELECT DISTINCT users.'uid', tax.'tid' FROM tax LEFT JOIN users ON tax.'uid' = users.'uid' UNION SELECT DISTINCT users.'uid', tax.'tid' FROM tax LEFT JOIN users ON tax.'uid' = users.'uid' ORDER BY users.'uid', tax.'tid' but I come across this error miss...
Ari7
0

votes
0

answer
14

Views

DBD-Oracle-1.76 installation issue

I am setting up a new server for POSTGRESQL, so we have to migrate from ORACLE to POSTGRES while installing DBD-Oracle-1.76 package. However, we are getting error even though we have exported ORACLE_BASE, ORACLE_HOME, LD_LIBRARY, LD_LIBRARY_PATH. Here is what I have tried: make rm -f blib/arch/auto...
Abhinav
1

votes
1

answer
127

Views

How to analyze poor performance from Azure PostGreSQL-PaaS

I'm experiencing poor performance from Azure PostGreSQL-PaaS and need help with how to proceed. I'm trying out Azure PostGreSQL-PaaS in a project. I'm experiencing an intolerable performance from the database (or at least it seems like the database is the problem). Our application is running in an A...
Gunnis
1

votes
2

answer
48

Views

Making a ST_Distance query scalable under high loads?

I'm running a database server that contains a large table of business addresses. An app is connected to it that takes in user co-ordinates and the type of business they are looking for and returns the business nearest to that user. Originally, my app used a simple SELECT statement to find the neares...
GreenGodot
1

votes
1

answer
19

Views

Does PostgreSQL have any column which maintains uniqueness between table rows like as oracle has rowId and rowNum?

In oracle DB we can display rowid & rownum with table column, but in PostgreSQL does anything can we display along with table column? Ex: select rowid, rownum,id_mytable from mytable;
Anand Kumar
1

votes
1

answer
28

Views

Postgres string_agg function not recognized as aggregate function

I am attempting to run this query SELECT u.*, string_agg(CAST(uar.roleid AS VARCHAR(100)), ',') AS roleids, string_agg(CAST(r.role AS VARCHAR(100)), ',') AS systemroles FROM idpro.users AS u INNER JOIN idpro.userapplicationroles AS uar ON u.id = uar.userid INNER JOIN idpro.roles AS r ON r.id =...
Danny Ellis Jr.
1

votes
2

answer
51

Views

SQL subquery using group by item from main query

I have a table with a created timestamp and id identifier. I can get number of unique id's per week with: SELECT date_trunc('week', created)::date AS week, count(distinct id) FROM my_table GROUP BY week ORDER BY week; Now I want to have the accumulated number of created by unique id's per week, som...
neu242
1

votes
1

answer
44

Views

How to add an element to a json field array in Postgres

I'm trying to append data to an array that belongs to a json field in postgres. While using pgAdmin I know the following query works. ~ UPDATE lesson SET data = jsonb_set (data, '{pages, 999999}', '{'pageNum': 2, 'pageType': 'voc'}', True) WHERE id = 2; I am simply trying to get the above query to...
Joseph Vargas
1

votes
2

answer
40

Views

Get Sum for the column based on a condition

I am working on a SQL Query. Query is something like below: Select a.field1, b.field2, c.field3, c.field4, b.filed5, a.field6, d.field7 from a Inner join b on a.field1 = b.field1 right join c on b.field2 = c.field3 left join d on d.filed3 = a.field1 where some conditions; The output of the a...
ging
1

votes
2

answer
51

Views

How to search in array with LIKE operator

id | name | ipAddress ----+----------+------------------------- 1 | testname | {192.168.1.60,192.168.1.65} I want to search ipAddress with LIKE. I tried: {'$mac_ip_addresses.ip_address$': { [OP.contains]: [searchItem]}}, This one also: {'$mac_ip_addresses.ip_address$': { [OP....
YOGESH MISHRA
1

votes
3

answer
92

Views

How to enforce by constraint that a referenced row also has a given type

I have multiple tables B,C,D ... referencing one another table A. Let's just look at A and B: CREATE TABLE A ( ID int PRIMARY KEY , TYPE enum_type ); CREATE TABLE B ( A_ID int REFERENCES A(ID) ); Is it possible to check that A has always one specific TYPE when referenced by table B? every row in A r...
Cyborg-X1
1

votes
3

answer
52

Views

how to get two values from MAX() aggregation function in PostgresSQL

Now i have a query that i'll use the MAX() aggregation function on it . i made it WITH t2 AS( SELECT customerid , sum(total) sumTotal FROM invoice GROUP BY 1 ORDER BY 1 ) SELECT c.customerid customerid , c.firstName firstName , c.lastName lastName , c.country customerCountry , MAX(sumTotal) MaxSu...
Bassam Abdeltwab
1

votes
2

answer
58

Views

How can i change existing column as Identity in PostgreSQL 11.1

I would like to changes my existing column as Auto Identity in a Postgres Database. I tried with below script but it won't worked. Let me know if you have solution for the same I don't want to use postgres SEQUENCE. I would like to use GENERATED ALWAYS AS IDENTITY. ALTER TABLE public.patient ALTER...
Nayan Rudani
1

votes
3

answer
42

Views

Postgres - Searching for a particular value across columns in a table

I have a table that list of all fruits bought by a customer at a store by customer: | cust_name | fruit1 | fruit2 | fruit3 | |-----------|--------|---------|--------| | cust_a | apples | oranges | pears | | cust_b | pears | | | | cust_c | | | apples | I am t...
scott martin
1

votes
1

answer
25

Views

PostgreSQL: When casting an integer to a non-integer type to force floating point division in PostgreSQL, which number type should I use?

I know there are many integer division questions on StackOverflow, but I did not see this one. Similar to many programming languages, PostgreSQL performs integer division if both operands are integers. If one has: SELECT s.id AS student_id, COUNT(DISTINCT(bc.book_id)) / COUNT(c.id) AS average_books_...
Peter Jirak
1

votes
2

answer
30

Views

How to update same line than insert/update in plpgsql without reaching max_stack_depth

My problem is I reach the limit of the stack. And the message error says “You should increase max_stack_depth” and shows me the line that I use to update another column. I encounter this error after an update request (code below). I know my problem may look like others questions but none of them...
LyessD
1

votes
2

answer
25

Views

Build a learning platform - link exercise to its lesson - onetoone field or foreign key?

I'm trying to build a small project - an e-learning project. I'm trying to bind some exercises to its lesson id. I've read the django docs and I don't know if I should use a OneToOne field or a Foreign Key. I've tried the idea with the foreign key, as I feel like this is the right answer. lessons -...
1

votes
1

answer
20

Views

Add columns but keep a specific id

I have a table 'Listing' that looks like this: | listing_id | amenities | |------------|--------------------------------------------------| | 5629709 | {'Air conditioning',Heating, Essentials,Shampoo} | | 4156372 | {'Wireless Internet',Kitchen,'Pets all...
vkatsou
1

votes
1

answer
19

Views

Enforcing index scan for multicolumn comparison

I know that index internally is B-tree or similar tree structure. Suppose index is built for 3 columns (a,b,c), I want Postgres to: find key [a=10, b=20, c=30] in that B-tree, scan next 10 entries and return them. If the index has only one column the solution is obvious: select * from table1 where...
Yuri Yaryshev
1

votes
2

answer
34

Views

Double precision to varchar postgresql

I have to modify a table with fields of data types double precision to varchar, the alter was executed successfully, however the data change, and I can not understand what postgres does when it comes to making the conversion in both directions. Postgres version: PostgreSQL 10.4, compiled by Visual C...
Dagon
1

votes
1

answer
18

Views

How to extract time cost from Postsql explain in python

I output the content from explain command of postgres for my sql query into a .txt file. Here is the first line I got from explain command of postgres Bitmap Heap Scan on r (cost=3656.95..15795.10 rows=6094 width=39) I am trying to write a python script to extract the time cost for getting all rows...
MC X
1

votes
1

answer
37

Views

Postgres: Retrieve records Sequentially at 5 seconds interval

I have a postgres database table with nearly 700k records. I would want to have a python script that fetches the records one-by-one at 5seconds interval until the last record. How do I go about it?
arilwan
1

votes
1

answer
18

Views

Concatenate result of a SELECT into an EXECUTE

I am trying to recreate a view from a backup of the definition using EXECUTE in an anonymous code block (DO $$ ... BEGIN... END$$), but I cannot get it to work. This is approximately what I have so far: EXECUTE 'CREATE VIEW image AS (' || (SELECT definition FROM tmp_view_backup WHERE viewname = 'ima...
oligofren
1

votes
1

answer
54

Views

Conditional foreign key

I have the following table create table if not exists pgroup ( id uuid primary key default gen_random_uuid(), label varchar not null, is_role boolean default false ); I want to create a table like the following : create table if not exists grouprole ( groupId uuid not null references pgroup(id) `whe...
BlueMagma
1

votes
2

answer
63

Views

SQL group by without aggregation

I'm using PostgreSQL, and I have the following table (simplified for the question): CREATE TABLE SMSNotification ( enduser int, message int, FOREIGN KEY (enduser) REFERENCES EndUser (id), FOREIGN KEY (message) REFERENCES Message (id) ); Now, I'd like to batch send the messages. Hence, I'd like to gr...
Aadit M Shah
1

votes
2

answer
37

Views

Get 1 Year results from TimescaleDB

I have a table with data for IoT Devices, sending measurements every hour. Have to get reports for Today, Week, Month and Year. Using timescaleDB i got some 'ok' results for Today, Week and Month. For example Monthly: SELECT time_bucket('4 weeks', measured_at) AS month, MAX(CAST(data->>'1_8_0' AS DO...
opHASnoNAME
1

votes
2

answer
40

Views

Join on value from object in jsonb array

I have a table that looks like this: stone_data | ... ---------------+---- {[...], [...]} | ... My stone data is jsonb and looks like this: [{ 'Quantity': 2, 'SKU': 'abc123' }, { 'Quantity': 1, 'SKU': 'def456' }] I then have another table that looks like this: sku (string) | ... | ... ----------...
Brandon

View additional questions