PostgreSQL – How to Insert ‘0’ Value into Serial

insertpostgresqlsequence

I have a serial id in my application table. However, I would like to insert a single 0 id record to mean global.

Is there a way I can insert this into my table without affecting the counter?

Best Answer

Sure, just do it. The counter only gets incremented when you call a Sequence Manipulation Function. In the case when you don't explicitly specify a value, the DEFAULT triggers a call to nextval(sequence_name)

CREATE TABLE foo ( id serial );
INSERT INTO foo (id) VALUES (0);

PostgreSQL 10+

It should be noted, that serial is the old and antiquated method of doing this. You should be using the standardized IDENTITY COLUMNS

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
INSERT INTO foo (id) VALUES (0);

Related Solutions

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

SQL Server 2008 XML – Update, Insert, Inject Nodes

You just need to specify the first SiteInfo node using [1]

update [dbo].[Sites] 
SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])') 
where Siteid = 1

Best Answer

PostgreSQL 10+

Related Solutions

Sql-server – Combine – Select Into, with Insert Into Select

SQL Server 2008 XML – Update, Insert, Inject Nodes

Related Question