DISQUS

kL · 1 year ago

"Remove all instances of NOT NULL"
WTF!? This can seriously break many schemas. Don't do this.

Postres supports multiple-row inserts only in latest versions (8.3+ IIRC).

another useful replacement may be CURRENT_TIMESTAMP with NOW() and timestamp with timestamp without time zone, etc.

Greg Allard · 1 year ago

@kL
Thanks for the comment. I updated the post to cross out the NOT NULL part, I think I meant that only for the auto_increment part, but messed it up.

I'll check out the newer versions to see what's been changed. I was on 7.4.7.

Will · 1 year ago

- datetime in MySQL is equal to timestamp in PostgreSQL. There is no equivalent of MySQL's timestamp data type in PostgreSQL

- An enum column in MySQL is equal to a varchar with a check constraint in PostgreSQL, for example:
column_name enum('foo', 'bar') is equal to column_name varchar(3) check (column_name IN ('foo', 'bar'))

- The blob datatype in MySQL is equal to a bytea datatype in PostgreSQL

Also, when truncating a table, I do not believe PostgreSQL resets the sequence for serial columns, so it will need to be done manually.

Obviously there are more differences when moving to PostgreSQL, but these are most of the big ones for CREATE TABLE statements.

Just a little FYI. I wrote these all out, but forgot my email. Your blog is very unfriendly to comments without an email, as in I lost my comments.

Greg Allard · 1 year ago

I removed the requirement for e-mail. I had no idea WordPress handled it so poorly since I was always logged in.

gilbert · 1 year ago

can you give me the sql equivalent of this to postgresql, i'll be using phppgadmin thanks...

CREATE TABLE manila_area_tbl
(
area_no tinyint(4) NOT NULL auto_increment,
area_name varchar(65) NOT NULL,
disp_flag char(1) NOT NULL default '1',
disp_no tinyint(4) NOT NULL,
del_flag char(1) NOT NULL default '0',
PRIMARY KEY (`area_no`),
KEY disp_no` (`disp_no`),KEY `del_flag` (`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=25;

Greg Allard · 1 year ago

I noticed another case I may have missed, so I edited the post to catch when the key may be a smallint.

Try running these 4 queries.


CREATE TABLE manila_area_tbl
(
area_no SERIAL,
area_name varchar(65) NOT NULL,
disp_flag char(1) NOT NULL default 1,
disp_no smallint NOT NULL,
del_flag char(1) NOT NULL default 0,
PRIMARY KEY (area_no)
) ;

CREATE INDEX disp_no ON manila_area_tbl (disp_no);

CREATE INDEX del_flag ON manila_area_tbl (del_flag);

ALTER SEQUENCE manila_area_tbl_area_no_seq RESTART WITH 25;

Emilien · 1 year ago

Hello,
do you know how to do something like this ?

ALTER SEQUENCE univers_id_seq RESTART WITH (SELECT max(id) + 1 FROM univers);

Thanks
Emilien

Greg Allard · 1 year ago

If that query isn't working, try moving the +1 out of the select statement like this:

ALTER SEQUENCE univers_id_seq RESTART WITH (SELECT max(id) FROM univers)+1;