Mysql – insert 80 data records into your SQL database with the same ID number
I'd like to make a sql table something like this. I want to insert 80 data records in my SQL databse with the same ID number. How can i overcome this sql database?
Postgresql – Using rank() in Postgres
I have the following tables (simplified) in Postgres: CREATE TABLE party( id int PRIMARY KEY, family_name varchar(50) NOT NULL ); CREATE TABLE election( id int, country_name varchar(50) NOT NULL, e_type election_type NOT NULL, e_date date NOT NULL, vote_share numeric, seats…
Postgresql – Remove jsonb_build_object from the output
I am creating a jsonb object using jsonb_build_object in PostgreSQL. The problem is that it adds jsonb_build_object column in the output. Please refer to the screenshot and query below: select jsonb_build_object( 'country_name', country_name, 'country_code', country_code, 'currency_code', currency_code ) from table…
MySQL query pivot where all options exist
I have a MySQL table entries with a primary key id. Also I have a pivot table entries_tags with foreign keys entry_id and tag. tag is plain text by the way, I know this should be normalized but that is…
Mysql – How do you query a main table with known duplicates that also have tags?
I have a main table called pics that has an ID number and the origin site from which it was pulled from. I also have another table called tags that has a list of all the tags that were also…
Postgresql – Why does my UPDATE using a CTE affect all rows?
I have a task table with 1000 rows in my Postgres 12 database. I need to get last 100 rows non-executed, set column executed to 1 and return that selected rows. If I do code below, it updates all 1000…
Postgresql – POSTGRES: How to calculate age from dob in a DB that allows only select statements (no data manipulation)
My table looks like this: Name Date of Birth John 02-02-1990 15:30 Esther 05-05-1980 13:30 Mark 07-10-1980 12:30 I want a query that returns this. Age Count 33 1 43 2 I've seen other solutions like what we have here…
Efficiently find common ancestor in postgresql directed acyclic graph
Given a directed acyclic graph shaped like this: "Branch A" "Branch B" branch-a-head-id branch-b-head-id --- | | ^ id-Z id-C | | | | id-Y id-B | | | "Branch A" id-X id-A and | | "Branch B" branch-a-first-id branch-b-first-id…
Postgresql – The index that I have created for a table does not work
I have a table CREATE TABLE cities ( id serial, name character varying(30) ); Then, some rows were added. In order to make the queries faster, an index was defined: CREATE INDEX cities_name_idx ON cities (name); SET enable_seqscan TO off;…
Postgresql – Get the next 10 values of the SEQUENCE at once
I want to get the next 10 values of the SEQUENCE at once. I can get those individually by calling SELECT setval('myseq', ...); -- Next nextval will return 43 10 times back to back. On Microsoft SQL server, I can…