I am using PostgreSQL with Express.js I came across a problem, that when I update my database, with new entries, and delete old ones, no matter what, it keeps returning only the old ones (that were deleted several days ago…
Using Postgres and Postgis I was experiencing a very slow spatial join using st_equals. Out of curiosity, I tried using = instead, and the join was much faster. Using EXPLAIN, I noticed that the st_equals join uses a nested loop,…
Maven pom dependency: <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.json/json --> <dependency> <groupId>org.json</groupId> <artifactId>json</artifactId> <version>20230227</version> </dependency> Configuration Dialect: spring: jpa: properties: hibernate: dialect: org.hibernate.dialect.PostgreSQLDialect show-sql: 'true' hibernate: ddl-auto: update I have the following Entity: @Entity @Data @Getter @AllArgsConstructor @NoArgsConstructor @Builder…
Problem Statement: We will be getting a request of a number (11 digits) and have to efficiently lookup in the database and return a single row (last updated) based on range in which it fits. Also, I have already used…
I am implementing a Tile Server with the help of PostGIS. Running this query: SELECT ST_AsMVT(tile) FROM ( SELECT __FEATURES__, ST_AsMVTGeom( ST_Transform(__GEOM__, __SRID__), ST_Transform(ST_TileEnvelope(?, ?, ?), __SRID__), 4096, 256, false ) as geom FROM __SCHEMA__.__TABLE__ WHERE __GEOM__ && ST_Transform(ST_TileEnvelope(?, ?,…
What to do if there appear to be no users on the server: any attempt to connect to the server gives FATAL: role ".." does not exist, including the role "postgres". It's only possible to connect in single user mode…
I have customers and visits table, and I would like to know which of these two queries has the better performance: (I have indexes defined for those columns) Query 1 SELECT customers.id as id, COALESCE(v.count, 0) as visits FROM customers…
With this query: with tbl as ( select 1 ord, 'A' name from dual union all select 2 ord, 'A' name from dual union all select 3 ord, 'A' name from dual union all select 4 ord, 'B' name from…
I have a script with this command /usr/bin/psql postgres -h localhost -U postgres -c "GRANT CONNECT ON DATABASE "famdb-develop" TO fullwood;" When I ran it, I got this error: ERROR: syntax error at or near "-" LINE 1: GRANT CONNECT…
In the nobel quiz, there is a question "Write the code which would show the year when neither a Physics or Chemistry award was given'. My code is as following: SELECT yr FROM nobel WHERE subject NOT IN ('Chemistry','Physics') The…