I'm switching from MySQL to PostgreSQL and I was wondering how can I have an INT
column with AUTO INCREMENT
. I saw in the PostgreSQL docs a datatype called SERIAL
, but I get syntax errors when using it.
Yes, SERIAL is the equivalent function.
CREATE TABLE foo (
id SERIAL,
bar varchar
);
INSERT INTO foo (bar) VALUES ('blah');
INSERT INTO foo (bar) VALUES ('blah');
SELECT * FROM foo;
+----------+
| 1 | blah |
+----------+
| 2 | blah |
+----------+
SERIAL is just a create table time macro around sequences. You can not alter SERIAL onto an existing column.
You can use any other integer data type, such as smallint
.
Example :
CREATE SEQUENCE user_id_seq;
CREATE TABLE user (
user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
);
ALTER SEQUENCE user_id_seq OWNED BY user.user_id;
Better to use your own data type, rather than user serial data type.
CREATE SEQUENCE
postgresql.org/docs/8.1/interactive/sql-createsequence.html ). HOWEVER, I'm not quite sure why you changed the owner.
If you want to add sequence to id in the table which already exist you can use:
CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');
"Table"
and"table"
then just leave it unquoted and canonicalize it totable
. The convention is simply never to use quotes in Pg. You can, if you want, use mixed case names for appearance, just don't require it:CREATE TABLE fooBar ( .. ); SELECT * FROM fooBar;
will work, as willSELECT * FROM foobar
.INSERT INTO user
without quotes, please.