Alter data type of a column to serial
In pgsql, is there a way to have a table of several values, and choose one of them (say, other_id), find out what its highest value is and make every new entry that is put in the table increment from that value.
I suppose this was just too easy to have had a chance of working..
ALTER TABLE address ALTER COLUMN new_id TYPE SERIAL ____________________________________ ERROR: type "serial" does not exist
Thanks much for any insight!
A quick glance at the docs tells you that
The data types smallserial, serial and bigserial are not true types but merely a notational convenience for creating unique identifier columns
If you want to make an existing (integer) column to work as a "serial", just create the sequence by hand (the name is arbitrary), set its current value to the maximum (or bigger) of your current
address.new_id value, at set it as default value for your
To set the value of your sequence see here.
SELECT setval('address_new_id_seq', 10000);
This is just an example, use your own sequence name (arbitrary, you create it), and a number greater than the maximum current value of your column.
Update: as pointed out by Lucas' answer (which should be the acccepted one) you should also specify to which column the sequence "belongs to" by using
CREATE/ALTER SEQUENCE ... OWNED BY ...
Change Column's Data Type, This tutorial shows you step by step how to change the data type of a column by CREATE TABLE assets ( id serial PRIMARY KEY, name TEXT NOT NULL, Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference. The "Persons" table will now look like this:
Look into postgresql documentation of datatype serial. Serial is only short hand.
CREATE TABLE tablename ( colname SERIAL );
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
Alter column to type serial, Hi all,. Would it be possible (or reasonable) to add support for changing the type of a column to serial or bigserial (yes, yes, I know they're not Re: Alter column to type serial at 2010-11-04 14:04:59 from Tom Lane Re: Alter column to type serial at 2010-11-04 16:13:04 from Alvaro Herrera Browse pgsql-hackers by date
This happened because you may use serial data type only when you are creating new table or adding new column to a table. If you'll try to ALTER existing table using this data type you'll get an error. Because serial is not a true data type, but merely an abbreviation or alias for a bit longer query.
In case you would like to reach the same effect, as you are expecting from using serial data type when you are altering existing table you may do this:
CREATE SEQUENCE my_serial AS integer START 1 OWNED BY address.new_id; ALTER TABLE address ALTER COLUMN new_id SET DEFAULT nextval('my_serial');
First line of query creates your own sequence with name my_serial. OWEND BY statement connects newly created sequence with exact column of your table. In your exact case table is address and column is new_id. START statement defines what value should this sequence start from.
Second line alters your table with new default value, which will be determined by previously created sequence.
It will brings you to the same result as you were expecting from using serial.
How can I change an existing type from "bigint" to "bigserial , bigserial is not a type. It's a pseudo-type, a notational convenience that is resolved to type bigint internally, plus a sequence, a column default, a dependency very little to do with ALTER COLUMN TYPE --- most of the things it would do are not that. The fact that serial was bolted on as a fake type is a wart that maybe we shouldn't extend in this particular fashion.
Change existing column in PG to auto-incremental primary key , That essentially what serial does. See the manual for details: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL. First, specify the name of the table to which the column you want to change belong in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword. It is possible to use either SET DATA TYPE or TYPE.
Change SERIAL to IDENTITY in PostgreSQL, ERROR: identity column type must be smallint, integer, or bigint. SQL state: 22023. The biggest data type possible is bigint (8 bytes), that has The SERIAL data type stores a sequential integer, of the INT data type, that is automatically assigned by the database server when a new row is inserted. The default serial starting number is 1, but you can assign an initial value, n , when you create or alter the table.
Alter Table by Changing Column Datatype in PostgreSQL , How to Combine the PostgreSQL Change Column Data type Statement with the 'Action' 'SET DATA TYPE' hw_id serial PRIMARY KEY,sp To transform an existing serial column into a bigserial (or smallserial), all you need to do is to ALTER the data type of the column. Sequences are generally based on bigint, so the same sequence can be used for any integer type. To "change" a bigint into a bigserial or an integer into a serial, you just have to do the rest by hand: