Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to INSERT empty line into SEQUENTIAL table from PHP

How to INSERT empty line into SEQUENTIAL table from PHP

From
Stefan Schwarzer
Date:
Hi,

rahter dump question, I guess....

But I have a table with a sequential index field, into which I would
like to add from time to time another line (via webbrowser), which in
turn, stays first empty, before it's being filled in later (via
webbrowser).

Because the ID field is sequential and indexed, I can't use

    INSERT INTO table_xx (ID, field2, field3, field4) VALUES ('', '', '',
'');

neither (skipping ID because it should be filled in automatically):

    INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');

How am I supposed to do it?

Thanks for any hints,

Stef

Re: How to INSERT empty line into SEQUENTIAL table from PHP

From
"A. Kretschmer"
Date:
am  Mon, dem 16.06.2008, um  8:33:27 +0200 mailte Stefan Schwarzer folgendes:
> Hi,
>
> rahter dump question, I guess....
>
> But I have a table with a sequential index field, into which I would
> like to add from time to time another line (via webbrowser), which in
> turn, stays first empty, before it's being filled in later (via
> webbrowser).
>
> Because the ID field is sequential and indexed, I can't use
>
>     INSERT INTO table_xx (ID, field2, field3, field4) VALUES ('', '',
>     '',  '');
>
> neither (skipping ID because it should be filled in automatically):
>
>     INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');

insert into table_xx (id, field2, ...) values (NULL, ...)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: How to INSERT empty line into SEQUENTIAL table from PHP

From
Julius Tuskenis
Date:
Hi, Stefan.

your second example should work for you.
INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');
(keeping in mind, that your ID column is of type serial or has DEFAULT
NEXTVAL('some_sequence') ).
You can also insert values yourself:
INSERT INTO table_xx (ID, field2, field3, field4) VALUES
(NEXTVAL('some_sequence'), '', '', '');

Julius Tuskenis



Stefan Schwarzer rašė:
> Hi,
>
> rahter dump question, I guess....
>
> But I have a table with a sequential index field, into which I would
> like to add from time to time another line (via webbrowser), which in
> turn, stays first empty, before it's being filled in later (via
> webbrowser).
>
> Because the ID field is sequential and indexed, I can't use
>
>     INSERT INTO table_xx (ID, field2, field3, field4) VALUES ('', '',
> '', '');
>
> neither (skipping ID because it should be filled in automatically):
>
>     INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');
>
> How am I supposed to do it?
>
> Thanks for any hints,
>
> Stef
>
>

Re: How to INSERT empty line into SEQUENTIAL table from PHP

From
Stefan Schwarzer
Date:
It doesn't work like this:

    INSERT INTO page_input (id, page, text_en, text_fr, text_es, text_ar)
VALUES (NULL, '', '', '', '', '')

    --> ERROR: null value in column "id" violates not-null constraint

nor like this:

    INSERT INTO page_input (page, text_en, text_fr, text_es, text_ar)
VALUES ('', '', '', '', '')

    --> ERROR: duplicate key value violates unique constraint "id_unique"

nor like this:

    INSERT INTO page_input (id, page, text_en, text_fr, text_es, text_ar)
VALUES (nextval('page_input_id_seq'::regclass), '', '', '', '', '')

    --> ERROR: duplicate key value violates unique constraint "id_unique"



On Jun 16, 2008, at 8:54 AM, Julius Tuskenis wrote:

> Hi, Stefan.
>
> your second example should work for you.
> INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');
> (keeping in mind, that your ID column is of type serial or has
> DEFAULT NEXTVAL('some_sequence') ).
> You can also insert values yourself:
> INSERT INTO table_xx (ID, field2, field3, field4) VALUES
> (NEXTVAL('some_sequence'), '', '', '');
>
> Julius Tuskenis
>
>
>
> Stefan Schwarzer rašė:
>> Hi,
>>
>> rahter dump question, I guess....
>>
>> But I have a table with a sequential index field, into which I
>> would like to add from time to time another line (via webbrowser),
>> which in turn, stays first empty, before it's being filled in later
>> (via webbrowser).
>>
>> Because the ID field is sequential and indexed, I can't use
>>
>>    INSERT INTO table_xx (ID, field2, field3, field4) VALUES ('',
>> '', '', '');
>>
>> neither (skipping ID because it should be filled in automatically):
>>
>>    INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');
>>
>> How am I supposed to do it?
>>
>> Thanks for any hints,
>>
>> Stef
>>
>>

Re: How to INSERT empty line into SEQUENTIAL table from PHP

From
Julius Tuskenis
Date:
I guess you get duplicate value error because you have previously
inserted ID column with your own values. Check the sequence
page_input_id_seq if it points to the right next value. If not - correct
it to a MAX(ID) +1

Julius Tuskenis



Stefan Schwarzer rašė:
> It doesn't work like this:
>
>     INSERT INTO page_input (id, page, text_en, text_fr, text_es,
> text_ar) VALUES (NULL, '', '', '', '', '')
>
>     --> ERROR: null value in column "id" violates not-null constraint
>
> nor like this:
>
>     INSERT INTO page_input (page, text_en, text_fr, text_es, text_ar)
> VALUES ('', '', '', '', '')
>
>     --> ERROR: duplicate key value violates unique constraint "id_unique"
>
> nor like this:
>
>     INSERT INTO page_input (id, page, text_en, text_fr, text_es,
> text_ar) VALUES (nextval('page_input_id_seq'::regclass), '', '', '',
> '', '')
>
>     --> ERROR: duplicate key value violates unique constraint "id_unique"
>
>
>
> On Jun 16, 2008, at 8:54 AM, Julius Tuskenis wrote:
>
>> Hi, Stefan.
>>
>> your second example should work for you.
>> INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');
>> (keeping in mind, that your ID column is of type serial or has
>> DEFAULT NEXTVAL('some_sequence') ).
>> You can also insert values yourself:
>> INSERT INTO table_xx (ID, field2, field3, field4) VALUES
>> (NEXTVAL('some_sequence'), '', '', '');
>>
>> Julius Tuskenis
>>
>>
>>
>> Stefan Schwarzer rašė:
>>> Hi,
>>>
>>> rahter dump question, I guess....
>>>
>>> But I have a table with a sequential index field, into which I would
>>> like to add from time to time another line (via webbrowser), which
>>> in turn, stays first empty, before it's being filled in later (via
>>> webbrowser).
>>>
>>> Because the ID field is sequential and indexed, I can't use
>>>
>>>    INSERT INTO table_xx (ID, field2, field3, field4) VALUES ('', '',
>>> '', '');
>>>
>>> neither (skipping ID because it should be filled in automatically):
>>>
>>>    INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');
>>>
>>> How am I supposed to do it?
>>>
>>> Thanks for any hints,
>>>
>>> Stef
>>>
>>>
>
>

Re: How to INSERT empty line into SEQUENTIAL table from PHP

From
Stefan Schwarzer
Date:
True indeed...  With

SELECT setval('page_input_id_seq', 89);

it works then with this:

INSERT INTO page_input (id, page, text_en, text_fr, text_es, text_ar) VALUES (nextval('page_input_id_seq'::regclass), '', '', '', '', '')

Thanks a lot!!

Stef


I guess you get duplicate value error because you have previously inserted ID column with your own values. Check the sequence page_input_id_seq if it points to the right next value. If not - correct it to a MAX(ID) +1

Julius Tuskenis



Stefan Schwarzer rašė:
It doesn't work like this:

   INSERT INTO page_input (id, page, text_en, text_fr, text_es, text_ar) VALUES (NULL, '', '', '', '', '')

   --> ERROR: null value in column "id" violates not-null constraint

nor like this:

   INSERT INTO page_input (page, text_en, text_fr, text_es, text_ar) VALUES ('', '', '', '', '')

   --> ERROR: duplicate key value violates unique constraint "id_unique"

nor like this:

   INSERT INTO page_input (id, page, text_en, text_fr, text_es, text_ar) VALUES (nextval('page_input_id_seq'::regclass), '', '', '', '', '')
       --> ERROR: duplicate key value violates unique constraint "id_unique"



On Jun 16, 2008, at 8:54 AM, Julius Tuskenis wrote:

Hi, Stefan.

your second example should work for you.
INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', ''); (keeping in mind, that your ID column is of type serial or has DEFAULT NEXTVAL('some_sequence') ).
You can also insert values yourself:
INSERT INTO table_xx (ID, field2, field3, field4) VALUES (NEXTVAL('some_sequence'), '', '', '');

Julius Tuskenis



Stefan Schwarzer rašė:
Hi,

rahter dump question, I guess....

But I have a table with a sequential index field, into which I would like to add from time to time another line (via webbrowser), which in turn, stays first empty, before it's being filled in later (via webbrowser).

Because the ID field is sequential and indexed, I can't use

  INSERT INTO table_xx (ID, field2, field3, field4) VALUES ('', '', '', '');

neither (skipping ID because it should be filled in automatically):

  INSERT INTO table_xx (field2, field3, field4) VALUES ('', '', '');

How am I supposed to do it?

Thanks for any hints,

Stef





Re: How to INSERT empty line into SEQUENTIAL table from PHP

From
Tino Wildenhain
Date:
A. Kretschmer wrote:

>
> insert into table_xx (id, field2, ...) values (NULL, ...)

I'd say it should be DEFAULT instead of NULL :-)

T.
Attachment