I have a three tables: topics, sentences, and vocabulary. Sentences and vocabulary both have a belongsTo topic_id, but not all topics necessarily have both vocabulary and sentences. I want to get a count of all topics that have both sentences and vocabulary. I have it working if I do one table at a time: The …
Tag: postgresql
Unable to grant all privileges to new user on Postgres
I’m creating a new user in a Postgres database (AWS RDS) and trying to grant all privileges on all tables in public schema of an existing database to that user. It is failing with below error. I even tried after logging in as the rds_superuser (postgres) and got the same error. psql (13.3, server 10.15)…
How to query Json field so the keys are the column names and the values are the column fields
PostgreSQL database name is contacts_contact and the column I’m interested in is called “fields” ‘fields’ column looks like this in my database: And a second record: The ‘fields’ column has fields that look like the two JSON data I shared. It is pretty much responses …
Is there any way to join two queries without common columns
Could anyone help me to join two tables without common columns? I have two tables: Tab1 Column1 Column2 K1 a K2 b Tab2 Column1 Column2 K3 c K4 d K5 e The result of select should be: Table1.Column1 Table2.Column1 K1 K3 K1 K4 K1 K5 K2 K3 K2 K4 K2 K5 How do I do that? Answer Fiddle -> https://www.db-fiddle.co…
Why current_date() incurr errors in postgres sql
I have sql like following. But it returned following errors Syntax error at or near Line 19, Position 35 This error was caused by current_date(). How can I avoid this and how can I get current date ? If someone has opinion,please let me know Thanks Answer date_sub() function is only available in Mysql , and c…
How to convert a timestamp field to int8? Or just drop the column and make a new one?
I have a PostgreSQL table with a column of type timestamp. I had included this column a while ago just in case I wanted to use it for something in the future. I am now looking to convert it into an int8 and use it as an epoch time column. All rows of the table have this column set to
SQL total for each row combination
Are there any types of aggregate functions that could help calculate the following? If I have the following data, I want the sum total for each duplicated row. The output would provide total number each row occurs: Answer Why not good old group by and count?
Can I query a aggregated query and a specific row’s query when using subqueries?
I am new to SQL and I wanted to return the results of a specific value and the average of similar values. I have gotten the average part working but I’m not sure how to do the specific value part. For more context, I have a list of carbon emissions by companies. I wanted the average of a industry based
How to create a sequence in a specific format in postgresql?
I want to automatically generate an unique entity field in Postgresql in following format: “ССDDD” where “C” is a character and “D” is a digit. For example “FG1752” or “HK9273”. How can this be implemented? Answer here is one way in mysql : in postgr…
PostgreSQL simply transpose/convert rows to columns
I read some SO posts like sql server 2008 r2 – Transpose rows to columns – Stack Overflow or sql – Postgres – Transpose Rows to Columns – Stack Overflow, but they are too complicated task to what I want to implement, and what I want to is pretty simple task. For example, assume I…