I am new in SQL and trying to understand Foreign key syntax. I know this was asked in multiple questions but each question I found did not seem to teach me what I am doing wrong here. This is my SQL code: And this is the error I am getting: Answer You need to add fields in your tables to
Tag: postgresql
How to get maximum number of concurrent events in postgresql?
I have a table named events like this: These events could have overlaps, and I want to know maximum number of overlapping events that have occurred over a time span. For example, in a situation like this: The answer is 2, because at most 2 events overlap at 10:30 until 10:35. I’m using Postgres 9.6 Answ…
pgloader not importing data from MySQL to Postgres
I tried the following command and it returns no errors but the data is not imported in my postgres database. Database is already created in Postgres. This is the result: When I login to psql to look for the data, its not there. For example, for the table users 2 records were supposed to be imported as mention…
How Do I use or properly return a value from my PostgreSQL function?
========================================= How do I use res in the parent function? Answer don’t specify both OUT and function returns: if you want to use the return of function ,use select into VAR, perform will just execute function discarding its output: finaly: https://www.postgresql.org/docs/current…
Postgres where clause over two columns from subquery
Database: Postgres Table name: records Has 4 columns Year | Dept | Expense | Month So per year there can be up to one record for each month / per department. I have a sub-query which returns me …
SQL Count digits in a string
I have a table of the following information. How would I edit the SQL query below to count the number of digits (i.e. [0-9]) in the strings? Answer One method is to get rid of all the other characters:
Unique constraint keys not invoked when a field is NULL
I have a table named AQI, with the following column Only one unique value of date, pollutant_code, source_id and time are able to exist, so I defined a unique constraint source_code_time_uq. And the upsert query like The upsert method work normally when all the field available, but when I put NULL in the time…
postgresql “idle in transaction” with all locks granted
A very simple delete (by key) on a small table (700 rows) every now and then stays “idle in transaction” for minutes (takes milliseconds usually) even though all the locks are marked as “granted”. What can I do to pinpoint what causes it? I’m using this select: which shows a lot …
jsonb LIKE query on nested objects in an array
My JSON data looks like this: given a text “foo” I want to return all the tuples that have this substring. But I cannot figure out how to write the query for the same. I followed this related answer but cannot figure out how to do LIKE. This is what I have working right now: Instead of passing the…
Union of arrays as aggregate function
I have the following input: I want the following output: I am grouping by name. For each group, the count should be aggregated as the max and the options should be aggregated as the union. I am having troubles figuring out how do the the latter. Currently, I have this query: http://rextester.com/YTZ45626 I kn…