Re: [SQL] ERROR: null value in column "id" violates not-null constraint
Hello.
Note, that you may use SERIAL data type and PostgreSQL will implicitly
create sequence for you column, for example,
CREATE table test (
id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly create
'test_id_seq'
dat text
);
Regards,
Dmitiy Igrishin
2009/10/11 Robert Paulsen
> On Saturday 10 October 2009 4:12 pm, Tom Lane wrote:
> > Robert Paulsen writes:
> > > I do have a question, though, I fixed things as indicated above:
> > > id integer DEFAULT nextval('vault_id_seq') NOT NULL,
> > > Dump gave back
> > > id integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,
> > > That "text" seems odd. Should I change it?
> >
> > That's all there is in 8.0 ...
> >
> > regards, tom lane
>
> Thanks!
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Re: [SQL] ERROR: null value in column "id" violates not-null constraint
On Sunday 11 October 2009 3:32 am, Dmitriy Igrishin wrote:
> Hello.
> Note, that you may use SERIAL data type and PostgreSQL will implicitly
> create sequence for you column, for example,
> CREATE table test (
> id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly
> create 'test_id_seq'
> dat text
> );
>
> Regards,
> Dmitiy Igrishin
>
I believe that's how I started, not knowing any other way, but the pg_dump
utility spits things out in all the gory details!
Somewhere along the line the default value for the id field was lost. I at
first suspected it happened in the dump/restore cycle when I restored the
data back into 8.0 after dumping it with 8.2 but I reran that scenario and
something else happened: It would NOT restore back into 8.0 at all, so that
must not be what I actually did to get into the "lost default" situation.
Below is what 8.2 dumps out. 8.0 refuses imported that. I suppose if I had
originally edited the 8.2 dump data to "fix" this I might have gotten into
the mess I was in but I sure don't remember doing that.
8.2 dump data:
id integer DEFAULT nextval(('auth_id_seq'::text)::regclass) NOT NULL,
What 8.0 is happy with:
id integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,
Bob
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ERROR: null value in column "id" violates not-null constraint
Hello!
Please, read pg_dump(1) manual page. You will find this text in it:
"It is not guaranteed that pg_dump's output can be loaded into a server of
an older major version -- not even if the dump was taken from a server
of that version. Loading a dump file into an older server may require
manual editing of the dump file to remove syntax not understood by the
older server."
Regards,
Dmitry Igrishin
2009/10/11 Robert Paulsen
> On Sunday 11 October 2009 3:32 am, Dmitriy Igrishin wrote:
> > Hello.
> > Note, that you may use SERIAL data type and PostgreSQL will implicitly
> > create sequence for you column, for example,
> > CREATE table test (
> > id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly
> > create 'test_id_seq'
> > dat text
> > );
> >
> > Regards,
> > Dmitiy Igrishin
> >
>
> I believe that's how I started, not knowing any other way, but the pg_dump
> utility spits things out in all the gory details!
>
> Somewhere along the line the default value for the id field was lost. I at
> first suspected it happened in the dump/restore cycle when I restored the
> data back into 8.0 after dumping it with 8.2 but I reran that scenario and
> something else happened: It would NOT restore back into 8.0 at all, so that
> must not be what I actually did to get into the "lost default" situation.
>
> Below is what 8.2 dumps out. 8.0 refuses imported that. I suppose if I had
> originally edited the 8.2 dump data to "fix" this I might have gotten into
> the mess I was in but I sure don't remember doing that.
>
> 8.2 dump data:
> id integer DEFAULT nextval(('auth_id_seq'::text)::regclass) NOT NULL,
>
> What 8.0 is happy with:
> id integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,
>
> Bob
>
Re: [SQL] ERROR: null value in column "id" violates not-null constraint
On Sunday 11 October 2009 8:22 am, Dmitriy Igrishin wrote: > Hello! > > Please, read pg_dump(1) manual page. You will find this text in it: > "It is not guaranteed that pg_dump's output can be loaded into a server of > an older major version -- not even if the dump was taken from a server > of that version. Loading a dump file into an older server may require > manual editing of the dump file to remove syntax not understood by the > older server." > Yes, I understand that. I tried it only to see if that was the mistake I made in the first place which, if that is indeed what happened, was completely inadvertent. I have sever different postgress servers set up and I had temporarily moved that database (using dump/restore) while experimenting with the system that normally hosts it. The results of my trial restore to 8.0 from an 8.2 dump were *negative* in that it did NOT recreate the situation I had stumbled into. As a matter of fact the restore failed completely instead of just dropping the DEFAULT attribute. There must have been more to the original screw-up, perhaps some editing on my part but, as I said, I don't remember doing that. Also, the temporary setup (on 8.2) exhibited the same "missing default" error as the original so apparently the situation was there, unnoticed, since before moving the database from 8.0 to 8.2. After fixing up the original 8.0 database (by editing the dump data as discussed earlier in this thread) and doing a dump/restore to re-establish the temporary 8.2 setup the missing default did NOT happen on 8.2 so however my original problem came about is still a mystery to me. Bob -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] maintaining nested views
Quite often i find myself reluctant to nest views, because of the maintenance issues this usually involves. As you know, you can't easily alter the "lower level" views, because the higher level ones depend on them. So, to add a field to a lower level view one has to: drop view vw_lowlevel CASCADE; create view vw_lowlevel as .; create view vw_highlevel as (it was); This can be quite cumbersome if there are many high level views and i don't want them to be altered (or at least not all of them). I'm just wondering if maybe someone has come up with a generic solution for this? Max -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
