PostgreSQL – assign exact date based on day of month
I don't even know where to start with this one, or if it's even possible to do in PostgreSQL (or if I'll just need to handle it in my code) I have a table of Bills, which specify which day…
Mysql concatenate parents and children as string
This is my database on mysql and I need extract concatenating all the parents and all children. id | name | parent_id | has_children 100017 | Roupas Femininas | 0 | 1 100018 | Meias | 100017 | 1 100419…
Postgresql – is it possible for a query to return the wildcard that matches a given string?
I have the following table in postgres: Table "public.items" Column | Type | Collation | Nullable | Default -------------+--------------------------+-----------+----------+--------------------------------------- id | integer | | not null | nextval('items_id_seq'::regclass) wildcard | character varying(255) | | not null | The wildcard column…
Mysql – Why does my function to collect data from database is not working
I am currently working on my first Java EE project. I am trying to display data from a database I created, but nothing is showing. So, I wonder if I did something bad in my connection or something else. I…
How can I convert a varchar into a date in MySQL Workbench?
Probably it's super simple but i've been stuck some hours on this. I have a column called "Publish_Date" which is a varchar, but my date shows like this: 17.01.11 (year.day.month) and I want to convert it to a date (at…
"Non-repeatable read" and "phantom read" occur with 'SERIALIZABLE' isolation level (MySQL)
I experimented if non-repeatable read and phantom read occur or not with SERIALIZABLE on MySQL but against my expectation, both non-repeatable read and phantom read actually occurred with SERIALIZABLE. These below are the definitions of non-repeatable read and phantom read:…
Adding a futuristic time inside a column in a table in MySQL
CREATE TABLE products ( id int not null auto_increment PRIMARY KEY, Name varchar(30) not null, Reliability int default 0, date timestamp default now(), expiration timestamp default (now()+31536000) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; I'm trying to add an expiration date for products…
Delete rows without unique id in MySQL
I have a table with the following columns: ClientID, Amount, etc. The problem is that some clients have two or more rows with Amount = 0, and I want to remove these rows. I've searched some information but only found…
MySql Delete Data older than 1 month but leave one entry per week and id (the one with the lowest value)
This is the table I am working with: +---------------------+----------- | Field | Type | +---------------------+--------------+ | ID | binary(17) | | MiscSensor_ID | binary(17) | | rawValue | varchar(100) | | RawValueUnitType_ID | int | | timestamp | timestamp |…
Mysql – Creating SQL Tables for a Game's Leaderboard
Newbie here having trouble wiring up some tables for a game's leaderboard. Would appreciate help! I have 3 tables; here's an ER diagram: ┌─────┐ ┌─────┐ │ IPs ├─||─────────────────|<─┤Names│ └──┬──┘ └──┬──┘ │ │ │ │ │ │ │ │ │ │…