Migration: Difference between revisions
Jump to navigation
Jump to search
| (15 intermediate revisions by the same user not shown) | |||
| Line 8: | Line 8: | ||
{| class="wikitable" | {| class="wikitable" | ||
! || Postgres || MariaDB | ! || Postgres || MariaDB | ||
|- | |- | ||
| Incremental | | | Incremental | ||
| <syntaxhighlight lang="sql"> | |||
CREATE TABLE public.testIncremental ( | |||
id1 serial NOT NULL, | |||
id2 int GENERATED ALWAYS AS IDENTITY NOT NULL, | |||
id3 int GENERATED BY DEFAULT AS IDENTITY NOT NULL, | |||
CONSTRAINT testincremental_pk PRIMARY KEY (id1) | |||
); | |||
</syntaxhighlight> | |||
| | |||
|- | |- | ||
| | | Text with emoji | ||
| <syntaxhighlight lang="sql"> | |||
CREATE TABLE public.testemoji ( | |||
id serial NOT NULL, | |||
text1 varchar(5) NULL, | |||
text2 text NULL, | |||
CONSTRAINT testemoji_pk PRIMARY KEY (id) | |||
); | |||
</syntaxhighlight> | |||
| | |||
|- | |||
| DateTime | |||
| <syntaxhighlight lang="sql"> | |||
-- Create the table with updated_at column | |||
CREATE TABLE users ( | |||
id SERIAL PRIMARY KEY, | |||
name VARCHAR(100), | |||
created_at TIMESTAMP DEFAULT NOW(), | |||
updated_at TIMESTAMP DEFAULT NOW() | |||
); | |||
-- Create a function to update the timestamp | |||
CREATE OR REPLACE FUNCTION update_updated_at_column() | |||
RETURNS TRIGGER AS $$ | |||
BEGIN | |||
NEW.updated_at = NOW(); | |||
RETURN NEW; | |||
END; | |||
$$ language 'plpgsql'; | |||
-- Create the trigger | |||
CREATE TRIGGER update_users_updated_at | |||
BEFORE UPDATE ON users | |||
FOR EACH ROW | |||
EXECUTE FUNCTION update_updated_at_column(); | |||
</syntaxhighlight> | |||
| | |||
|} | |} | ||
Latest revision as of 04:11, 1 September 2025
Table
Column
Data Types
| Postgres | MariaDB | |
|---|---|---|
| Incremental | CREATE TABLE public.testIncremental (
id1 serial NOT NULL,
id2 int GENERATED ALWAYS AS IDENTITY NOT NULL,
id3 int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
CONSTRAINT testincremental_pk PRIMARY KEY (id1)
);
|
|
| Text with emoji | CREATE TABLE public.testemoji (
id serial NOT NULL,
text1 varchar(5) NULL,
text2 text NULL,
CONSTRAINT testemoji_pk PRIMARY KEY (id)
);
|
|
| DateTime | -- Create the table with updated_at column
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Create a function to update the timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- Create the trigger
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
|