I have the following database relationship and query for PostgreSQL below: create table gender( gender_id int primary key, gender char(10)) create table student( student_id int primary key, first_name char(50) not null, last_name char(50) not null, gender_id int not null, constraint…
I have the following movie database relationship diagram and used the queries below to create the tables and its keys as shown below: The following are the codes that I used to create these 4 tables and inserted the data:…
Context I have the table "user_numbers" with the following records: id user_id number 1 3 123 2 3 666 3 4 123 4 5 666 5 6 555 I want to build query that find all user numbers and all…
I have json object in the below format in a table a column json_data { "body": { "emp number": "***", "id": "**", "details": [ { "location": "***", "designation": "***", "employee type": "***" } ] } details is an array and…
I need to write the following query. Suppose we have a table structure like this: main_table: id PRIMARY KEY column1, column2, column3 table_1: id PRIMARY KEY main_table_id REFERENCES main_table NOT NULL, column1, column2, column3 table_2: id PRIMARY KEY table_1_id REFERENCES…
With Postgres, I've created many tables that have foreign key references but they've always had a 1:1 relationship. Now I'd like to do something a little different: CREATE TABLE public.shared_media ( share_id uuid NOT NULL UNIQUE DEFAULT uuid_generate_v4(), media_ids uuid[]…
I have the following function which I want to pass anyelement and it should return whether it is a valid URL by matching the regex: CREATE OR REPLACE FUNCTION isURL(data anyelement) RETURNS BOOLEAN AS $$ BEGIN RETURN data::text ~ '^https?://[-a-zA-Z0-9@:%._+~#=]{2,255}.[a-z]{2,6}(/[-a-zA-Z0-9@:%._+~#=]*)*(?[-a-zA-Z0-9@:%_+.~#()?&//=]*)?$';…
In AWS account I created VPC1 using option “create vpc and more” so it creates the entire package with subnets route tables etc automatically. I used default 10.0.0.0/16 cidr block. I created simple RDS Postgres instance and attached security group…
There are two tables in my project: Legacy table col id col B col C x y z a b c New table (slightly different schema) col id col B another col x y bla As you can see, the…
In postgreSQL, we can use filter such as- SELECT column_name, SUM(sales) FILTER (WHERE year = 2022) AS total_sales_2022, SUM(sales) FILTER (WHERE year = 2023) AS total_sales_2023 FROM sales_table GROUP BY column_name; How can we implement this in Apache age? Anyone…