Table with Field Serial - Problem

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

My table is like this

Column | Type | Modifiers
----------+-------------------**+-----------------------------**
------------------------------**--
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>

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
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 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&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
&lt;http://lang.id&amp;gt;
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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>

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
&lt;http://lang.id&amp;gt;
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

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general