Tag: postgresql

Are nested SQL queries atomic?

I have some micro service (almost) system where each program has its own port. Moreover, not all ports in the system are available. For simplicity, I decided to keep a list of free ports in a table in PostgreSQL. Accordingly, I have this table: Respectively, if the value in the service column is null, then th…

Is it possible to get data of two different rows in one row (postgres)

postgresql sql

I have this table whereby the data of the same customer id is in a new row: Is it possible to get a one row of the data like below: I tried joining but then i get the same as the table where it prints out two data instead of one. Might be my join isn’t correct. Appreciate the help.

SQL returning 1 single column value on a multiple row

postgresql sql

This is my query so far. But what I need is to return one single value of a column like this. But in my query so far, this is the returning output. This my query. How can I get rid of the double values? Answer You can use analytical function as follows: Let’s say the column you highlighted is sol.actual…

Should the column used to order results be included in the index of a postgresql table?

database indexing postgresql sql sql-order-by

I am creating indexes for a PostgreSQL database. I would like to know whether the column(s) used to order results in a PostgreSQL statement should be included in the index. Assume I have created a …

Select Last Rows with Distinct Field

distinct postgresql sql top-n

I have a table with the following schema: In this table only the id field is unique. I’m concerned with getting the rows containing the last X distinct itemid, ordered by date. For example, in the sample above, if I’d like to get the last 3 distinct itemid, I’d be getting the first 4 rows, s…

Insert new records with several ids from another table

postgresql sql

I have a table, which has 9 records with id = 1 – 9 (for example, there can be more than 20 ids). I have one varchar value = ‘premium’. I need to insert these values to another table, after this action I should have 9 records with id from the first table and ‘premium’ varchar in …

Postgres & Rust R2D2: How to get array_to_json as text/string without the escaped double quotes?

postgresql postgresql-9.5 rust rust-cargo sql

I have the following SQL: This gives me following in PSQL: When I put a ::text: I still get the same: However on my rust app’s side using r2d2_postgres, I get an escaped string instead: Rust code: Outputs: How I can I prevent the escaping of double quotes? Answer The quotes are only escaped during print…

How to re-run Diesel migrations?

postgresql rust rust-diesel sql

I’m using Diesel with PostgreSQL. I added my migrations, and they worked fine, outputting everything in the schema.rs file. Until I noticed that I was missing the created_at fields on some of my migrations. I edited the SQL and ran diesel migration run. Nothing happened, no errors, no success. Is there …

PostgreSQL Transform Geometries

geometry postgresql sql

I have a table full of Easting/Northing points that I want to transform into a column in SRID:27700. I’m using Postgres with postgis installed. I’m trying this: Of course this returns the locations in easting/northing, but when I try to first transform the points using ST_Transform, it returns SQL…

Convert PostgreSQL COUNT … FILTER query to SQL Alchemy

flask-sqlalchemy postgresql sql sqlalchemy

I’m new to SQLAlchemy, and I would like to convert this PostgreSQL query: I have already tried this: Thank you in advance for your help Answer Thanks to @IljaEverilä’s comment, here is a more direct answer: (Original answer) I’m not sure if SQLAlchemy’s postgresql dialect specifically …