Is there a way in PostgreSQL to access fields without explicitly providing the column name? For example, instead of the statement: there’s a (possible) alternative of: Answer There is no such syntax in Postgres’ SQL. You could always resort to having code that dynamically constructs the query for …
Tag: postgresql
sql table comparisons – postgres
I have two tables with some columns being the same: TABLE A TABLE B I’d like to return the following: How do I check what rows from table B are in table A and also return Col4 (from table B) where they match in the query. I was using EXCEPT which worked great but now I need to have the
Pivot table without crosstab/tablefunc
I have a table like this INPUT I want to be able to pivot this table in the following manner OUTPUT All the articles that I can find online pivot tables based on a second column. My ultimate goal is to get one records per ID. FINAL OUTPUT Here the values of the size, python, cpp, non-code columns are aggregat…
In SQL get all rows with same value in column A that have only non-null values in column B
My SQL table has multiple columns, among them A and B. In A there’s some sort of identifier of some arbitrary object (say, a user or an item). Each object instance identified by A can have multiple entries in the database (A is not unique). Each object furthermore has a property B that is also stored in…
Querying JSONB array value for sub values?
I have a JSONB Object: It is stored in a jsonb column of a table So i was trying to get a count by name of devices which have interfaces that are not ‘up’. Group By is used for developing counts by naame, but i am having issues querying the json list for values. MY first Attempt was: Some surround…
Django: Really slow RunPython migration on related models
I am trying to optimise the speed of a migration on a large table (250K objects). The goal is to add to each row a user field based on the user of an object related to this row: I tried using an F expression but sadly django won’t allow relations in them. Note that I am mostly a beginner in
manage reference table with foreign keys on new incoming data in other table
I have a reference table (in Postgres) and multiple other tables which can reference data in/from this table. It would make sense to use foreign keys to manage this, but I was wondering how one could manage updating the entries in the reference table in the best way when new values come in. I guess it could b…
How to divide with each other the two queries with different conditions?
There is a table in which the managers column and the status column. How to calculate the total status of Fully, the divided total of records with all statuses except N / A for each manager? I tried to portray in this way, but nothing came of it First Query Second Query Need to recieve two columns with name a…
Select query to show missing values per day as 0
I have one table with product id’s and another with daily data for these products. When there is no data I want to return 0 instead of no value at all. I tried using a left join but this doesn’t give the resultset I want since the table I can join on has product id’s only, not per day. I
Doing a union on two tables, but renaming columns that may have the same name in postgres and dropping others
I am doing a union on two tables that have some of the same column names. So, say they look something like this: Here is what I am currently doing: Currently I obviously get repeated columns. So here is what I want to do: 1- The table2’s columns name and details, I would like to rename to t1_name and t2…