Обсуждение: Table with Field Serial - Problem
I have a table with only one Field ID type Serial Autonumeric and is a PK, i want insert a new record but it show me error Not null violation, but if i put a value the first INSERT work correctly but the next Insert it Show me error Unique violation,
So i try adding a new field in this table and put a value null to this field, and the ID Autonumeric work .
Exist a way to do it only with a field, i'm working with PHP???
On 10/31/2013 07:31 AM, Yostin Vargas wrote: > > I have a table with only one Field ID type Serial Autonumeric and is a > PK, i want insert a new record but it show me error Not null violation, > but if i put a value the first INSERT work correctly but the next > Insert it Show me error Unique violation, > > So i try adding a new field in this table and put a value null to this > field, and the ID Autonumeric work . > > Exist a way to do it only with a field, i'm working with PHP??? Some actual examples form you end would help:) My guess is you are trying to insert a NULL value into the PK field instead of just not inserting anything at all. An alternative is to use the DEFAULT keyword. See below for example. create table test_table(id_fld serial primary key, char_fld varchar); test=> \d test_table Table "public.test_table" Column | Type | Modifiers ----------+-------------------+------------------------------------------------------------- id_fld | integer | not null default nextval('test_table_id_fld_seq'::regclass) char_fld | character varying | Indexes: "test_table_pkey" PRIMARY KEY, btree (id_fld) test=> INSERT INTO test_table (id_fld , char_fld) VALUES (NULL, 't'); ERROR: null value in column "id_fld" violates not-null constraint test=> INSERT INTO test_table (char_fld) VALUES ('t'); INSERT 0 1 test=> INSERT INTO test_table (id_fld , char_fld) VALUES (DEFAULT, 't'); INSERT 0 1 > > > -- Adrian Klaver adrian.klaver@gmail.com
My table is like this
Column | Type | Modifiers
----------+-------------------+-------------------------------------------------------------
id_fld | integer | not null default nextval('test_table_id_fld_seq'::regclass)
----------+-------------------+-------------------------------------------------------------
id_fld | integer | not null default nextval('test_table_id_fld_seq'::regclass)
im using Yii Framework, How i can Put VALUES (DEFAULT) if i put "DEFAULT" is like a string
2013/10/31 Adrian Klaver <adrian.klaver@gmail.com>
Some actual examples form you end would help:)On 10/31/2013 07:31 AM, Yostin Vargas wrote:
I have a table with only one Field ID type Serial Autonumeric and is a
PK, i want insert a new record but it show me error Not null violation,
but if i put a value the first INSERT work correctly but the next
Insert it Show me error Unique violation,
So i try adding a new field in this table and put a value null to this
field, and the ID Autonumeric work .
Exist a way to do it only with a field, i'm working with PHP???
My guess is you are trying to insert a NULL value into the PK field instead of just not inserting anything at all. An alternative is to use the DEFAULT keyword. See below for example.
create table test_table(id_fld serial primary key, char_fld varchar);
test=> \d test_table
Table "public.test_table"
Column | Type | Modifiers
----------+-------------------+-------------------------------------------------------------
id_fld | integer | not null default nextval('test_table_id_fld_seq'::regclass)
char_fld | character varying |
Indexes:
"test_table_pkey" PRIMARY KEY, btree (id_fld)
test=> INSERT INTO test_table (id_fld , char_fld) VALUES (NULL, 't');
ERROR: null value in column "id_fld" violates not-null constraint
test=> INSERT INTO test_table (char_fld) VALUES ('t');
INSERT 0 1
test=> INSERT INTO test_table (id_fld , char_fld) VALUES (DEFAULT, 't');
INSERT 0 1
--
Adrian Klaver
adrian.klaver@gmail.com
On 10/31/2013 07:55 AM, Yostin Vargas wrote: > My table is like this > > Column | Type | Modifiers > ----------+-------------------__+-----------------------------__------------------------------__-- > id_fld | integer | not null default > nextval('test_table_id_fld___seq'::regclass) So you have a single field table, what is the purpose? > > > im using Yii Framework, How i can Put VALUES (DEFAULT) if i put > "DEFAULT" is like a string That would seem to be a question for the Yii mailing list/forum. -- Adrian Klaver adrian.klaver@gmail.com
Yes i have a single field because is related to another table that contains, the name
it's for to do multilanguage
Example
Table1
Column | Type | Modifiers
----------+-------------------+-------------------------------------------------------------
id | integer | not null default nextval('test_table_id_fld_seq'::regclass)
----------+-------------------+-------------------------------------------------------------
id | integer | not null default nextval('test_table_id_fld_seq'::regclass)
Table2
Column | Type | related
----------+-------------------+-------------------------------------------------------------
id_table1 | integer | FK of Table1.id
Column | Type | related
----------+-------------------+-------------------------------------------------------------
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
name | varchar
I solve it doin it " $model->id=new CDbExpression('DEFAULT'); "
Thanks for your Helps
2013/10/31 Adrian Klaver <adrian.klaver@gmail.com>
On 10/31/2013 07:55 AM, Yostin Vargas wrote:My table is like this----------+-------------------__+-----------------------------__------------------------------__--
Column | Type | Modifiersnextval('test_table_id_fld___seq'::regclass)
id_fld | integer | not null default
So you have a single field table, what is the purpose?That would seem to be a question for the Yii mailing list/forum.
im using Yii Framework, How i can Put VALUES (DEFAULT) if i put
"DEFAULT" is like a string
--
Adrian Klaver
adrian.klaver@gmail.com
On 10/31/2013 08:23 AM, Yostin Vargas wrote: > Yes i have a single field because is related to another table that > contains, the name > > it's for to do multilanguage > > Example > > Table1 > Column | Type | Modifiers > ----------+-------------------__+-----------------------------__------------------------------__-- > id | integer | not null default > nextval('test_table_id_fld___seq'::regclass) > > > Table2 > Column | Type | related > ----------+-------------------__+-----------------------------__------------------------------__-- > id_table1 | integer | FK of Table1.id > id_lang | integer | FK of lang.id <http://lang.id> > name | varchar > I may be having one of my dumb moments, but what does the above accomplish that including the serial column in Table2 does not? > > I solve it doin it " $model->id=new CDbExpression('DEFAULT'); " Great. > > Thanks for your Helps -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver-3 wrote >> Table1 >> Column | Type | Modifiers >> ----------+-------------------__+-----------------------------__------------------------------__-- >> id | integer | not null default >> nextval('test_table_id_fld___seq'::regclass) >> >> >> Table2 >> Column | Type | related >> ----------+-------------------__+-----------------------------__------------------------------__-- >> id_table1 | integer | FK of Table1.id >> id_lang | integer | FK of lang.id >> <http://lang.id> >> name | varchar >> The PK for table 2 is composite: the serial key from table 1 + the language id. The table 1 id has to be able to repeat since the same "entity" needs multiple translations. Using a serial on table 2 is also possible but a separate issue and probably not worth adding since you need a unique index on (id_table1, id_lang) regardless. The question is why isn't there some kind of identifier on table 1 that gives you some idea of what the id/table record is for? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Table-with-Field-Serial-Problem-tp5776516p5776546.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
yes i can put other field for identifier , but i think that whit the name of the table i can know it
2013/10/31 David Johnston <polobo@yahoo.com>
Adrian Klaver-3 wrote>> Table1The PK for table 2 is composite: the serial key from table 1 + the language
>> Column | Type | Modifiers
>> ----------+-------------------__+-----------------------------__------------------------------__--
>> id | integer | not null default
>> nextval('test_table_id_fld___seq'::regclass)
>>
>>
>> Table2
>> Column | Type | related
>> ----------+-------------------__+-----------------------------__------------------------------__--
>> id_table1 | integer | FK of Table1.id
>> id_lang | integer | FK of lang.id
>> <http://lang.id>
>> name | varchar
>>
id. The table 1 id has to be able to repeat since the same "entity" needs
multiple translations. Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.
The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Table-with-Field-Serial-Problem-tp5776516p5776546.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 10/31/2013 09:32 AM, David Johnston wrote: > Adrian Klaver-3 wrote >>> Table1 >>> Column | Type | Modifiers >>> ----------+-------------------__+-----------------------------__------------------------------__-- >>> id | integer | not null default >>> nextval('test_table_id_fld___seq'::regclass) >>> >>> >>> Table2 >>> Column | Type | related >>> ----------+-------------------__+-----------------------------__------------------------------__-- >>> id_table1 | integer | FK of Table1.id >>> id_lang | integer | FK of lang.id >>> <http://lang.id> >>> name | varchar >>> > > The PK for table 2 is composite: the serial key from table 1 + the language > id. The table 1 id has to be able to repeat since the same "entity" needs > multiple translations. Using a serial on table 2 is also possible but a > separate issue and probably not worth adding since you need a unique index > on (id_table1, id_lang) regardless. > > The question is why isn't there some kind of identifier on table 1 that > gives you some idea of what the id/table record is for? Exactly the id_table1 FK has no context, it is just a number generator, so why make it separate? If want to just generate numbers why not just use the sequence directly? > > David J. > > -- Adrian Klaver adrian.klaver@gmail.com
i really dont need a number generator, only a unique PK. but i want that this PK be generate automatically
for example i have a Category calling Computer in English but i have the same Category in Spanish (Computadora) i assigned the ID->1 for both
So if i put the Pk ID in the table2 number i have error for unique violation when i want INSERT another name in a diferent language for the same category
For that reason i declare ID in the table2 like a FK from ID in the table1
2013/10/31 Adrian Klaver <adrian.klaver@gmail.com>
On 10/31/2013 09:32 AM, David Johnston wrote:Adrian Klaver-3 wroteTable1<http://lang.id>
Column | Type | Modifiers
----------+-------------------__+-----------------------------__------------------------------__--
id | integer | not null default
nextval('test_table_id_fld___seq'::regclass)
Table2
Column | Type | related
----------+-------------------__+-----------------------------__------------------------------__--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
name | varchar
The PK for table 2 is composite: the serial key from table 1 + the language
id. The table 1 id has to be able to repeat since the same "entity" needs
multiple translations. Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.
The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?
Exactly the id_table1 FK has no context, it is just a number generator, so why make it separate? If want to just generate numbers why not just use the sequence directly?
David J.
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 10/31/2013 11:12 AM, Yostin Vargas wrote: > i really dont need a number generator, only a unique PK. but i want that > this PK be generate automatically > > for example i have a Category calling Computer in English but i have the > same Category in Spanish (Computadora) i assigned the ID->1 for both So table1 is the category table: id serial category varchar or Why not just make your PK a natural one (category, language)? In the end whatever works for you, works. I am just asking because I could not follow the logic and I needed guidance. > > So if i put the Pk ID in the table2 number i have error for unique > violation when i want INSERT another name in a diferent language for > the same category > > For that reason i declare ID in the table2 like a FK from ID in the table1 > > -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Oct 31, 2013 at 5:13 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: >> Table1 >> Column | Type | Modifiers >> >> ----------+-------------------__+-----------------------------__------------------------------__-- >> >> id | integer | not null default >> nextval('test_table_id_fld___seq'::regclass) >> >> >> Table2 >> Column | Type | related >> >> ----------+-------------------__+-----------------------------__------------------------------__-- >> >> id_table1 | integer | FK of Table1.id >> id_lang | integer | FK of lang.id <http://lang.id> >> name | varchar >> > > I may be having one of my dumb moments, but what does the above accomplish > that including the serial column in Table2 does not? The default constraint puzzles me a bit, but you can have duplicate values in table2 and check they are in t1. Imagine something like this. You store message ids and translations. When a new message is needed you insert it into t1, put this id wherever it's needed, and comunicate the id to the translators, which then can insert the translations in t2 at their pace. It has it uses. Francisco Olarte.
On 11/02/2013 04:58 AM, Francisco Olarte wrote: > On Thu, Oct 31, 2013 at 5:13 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: >>> Table1 >>> Column | Type | Modifiers >>> >>> ----------+-------------------__+-----------------------------__------------------------------__-- >>> >>> id | integer | not null default >>> nextval('test_table_id_fld___seq'::regclass) >>> >>> >>> Table2 >>> Column | Type | related >>> >>> ----------+-------------------__+-----------------------------__------------------------------__-- >>> >>> id_table1 | integer | FK of Table1.id >>> id_lang | integer | FK of lang.id <http://lang.id> >>> name | varchar >>> >> >> I may be having one of my dumb moments, but what does the above accomplish >> that including the serial column in Table2 does not? > > The default constraint puzzles me a bit, but you can have duplicate > values in table2 and check they are in t1. Imagine something like > this. You store message ids and translations. When a new message is > needed you insert it into t1, put this id wherever it's needed, and > comunicate the id to the translators, which then can insert the > translations in t2 at their pace. It has it uses. I understand the need to generate uniqueness, what I am not understanding is this method. Table1 is just a series of numbers, so were is the context that tells you what the numbers mean? To me it boils down to; if you just want to generate numbers use a sequence directly, if the numbers have meaning, supply context. Probably have spent too much time on this already, just one of those things that puzzle:) > > Francisco Olarte. > -- Adrian Klaver adrian.klaver@gmail.com