[SQL] Question re: relational technique
Here is a sample table: item item_id int namechar attrib1 char attrib2 char attrib3 char One problem with the above is that the list of attributes is fixed. I am looking for a way to assign new, previously undefined, attributes to items without changing the table structure. Is it ever appropriate to do the following? item item_id int namechar details item_id int attribute_name char attribute_value char If this is a reasonable technique are their guidelines for its use? When is it approptiate? When not? Thanks, Bob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question re: relational technique
On Sunday 12 March 2006 11:29, chester c young wrote: > --- Robert Paulsen <[EMAIL PROTECTED]> wrote: > > One problem with the above is that the list of attributes is fixed. I > > am looking for a way to assign new, previously undefined, attributes > > to > > > items without changing the table structure. Is it ever appropriate to > > do > > > the following? > > ... > > There are two ways for extending tables, one static and one dynamic. > > Your scheme is dynamic. You will have problems with typing and > performance. What you really want is to be able to list more > attributes, similar to attributes attached to a tag in html. > > If you want a base table that has, under different specified > conditions, extra attributes, there are better techniques. IMHO the > best solution is, for each condition, create a table containing the > primary table's id plus the extra attributes; then join that to the > base table So, to be sure I understand, something like ... item item_id int namechar cond_one cond_one_id int cond_one_descr char item_id int attribute_a char attribute_b int cond_two cond_two_id int cond_two_descr char item_id int attribute_c bool attribute_d date etc... This still requires me to modify the overall database structure but not the original item table. As my reward :) I get to use any type I choose for each new attribute. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Question re: relational technique
On Monday 13 March 2006 03:03, Richard Huxton wrote: > Robert Paulsen wrote: > > This still requires me to modify the overall database structure but not > > the original item table. As my reward :) I get to use any type I choose > > for each new attribute. > > The whole point of the database structure is to accurately reflect the > requirements of your data. If you don't want your change your structure > to keep track of the real world, why bother to structure it in the first > place? Just stick it all in text documents and let htdig free-text > search against it. Requirements change and differ from one application of the datbase to another. The database structure is maintained by others and is used by several diverse locations. It is an effort to incorporate and coordinate changes. The database already uses the name-value technique in one place, probably for this very reason. I was suspicious of the technique so posted my original question. The answer given is a resonable compromise. I can have my own table whose structure I control, even though the fields in the table "really" belong in another table. Bob ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] ERROR: null value in column "id" violates not-null constraint
I have a database with a sequence field as a primary key in a table and can no
longer insert data into it as it gets the subject error message.
This database has been in use for well over two years without any problems
using postgresql-server-8.0.13-1.1.
Suddenly, when I attempt to add a new record to the table I get the subject
error message referencing the primary key field. I am using PHP to submit the
query as follows:
php output ==
Warning: pg_query() [function.pg-query]: Query failed: ERROR: null value in
column "id" violates not-null constraint
in /srv/www/htdocs/pwvault/functions.php on line 42
Query failed: INSERT INTO vault (service, category, userid, passwd, url,
notes) VALUES ('aaa', 'bbb', 'ccc', 'ddd', 'eee', 'fff')
==
I tried changing the query as follows but get the same failure:
INSERT INTO vault (id, service, category, userid, passwd, url, notes) VALUES
(DEFAULT, 'aaa', 'bbb', 'ccc', 'ddd', 'eee', 'fff')
So why isn't pgsql creating the new sequence value for me? Here is some info
from the dump command:
== dump data =
CREATE TABLE vault (
id integer NOT NULL,
archived boolean DEFAULT false,
service character varying(256) NOT NULL,
category character varying(16),
userid character varying(256) NOT NULL,
passwd character varying(256) NOT NULL,
url character varying(4096),
notes text
);
CREATE SEQUENCE vault_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.vault_id_seq OWNER TO robert;
SELECT pg_catalog.setval('vault_id_seq', 342, true);
ALTER TABLE ONLY vault
ADD CONSTRAINT vault_pkey PRIMARY KEY (id);
ALTER INDEX public.vault_pkey OWNER TO robert;
POSSIBLE REASON FOR THE PROBLEM:
I dumped the data, restored it into postgresql-8.2.13-0.1. dumped it from
there and restored it back to postgresql-server-8.0.13-1.1. I now get the
same failure from both 8.0 and 8.2.
--
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 Saturday 10 October 2009 1:46 pm, Tom Lane wrote:
> Robert Paulsen writes:
> > I have a database with a sequence field as a primary key in a table and
> > can no longer insert data into it as it gets the subject error message.
>
> Does the table actually have a default for id anymore? (Try looking
> at it with psql's \d command.)
pwvault=# \d vault
Table "public.vault"
Column | Type | Modifiers
--+-+---
id | integer | not null
archived | boolean | default false
service | character varying(256) | not null
category | character varying(16) |
userid | character varying(256) | not null
passwd | character varying(256) | not null
url | character varying(4096) |
notes| text|
Indexes:
"vault_pkey" PRIMARY KEY, btree (id)
So no default for id. What should it be?
Something like one of this?
default nextval('vault_id_seq')
pwvault=# \d vault_id_seq
Sequence "public.vault_id_seq"
Column | Type
---+-
sequence_name | name
last_value| bigint
increment_by | bigint
max_value | bigint
min_value | bigint
cache_value | bigint
log_cnt | bigint
is_cycled | boolean
is_called | boolean
--
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 Saturday 10 October 2009 2:00 pm, Robert Paulsen wrote:
>
> So no default for id. What should it be?
>
> Something like one of this?
>
> default nextval('vault_id_seq')
>
Should have tried that before posting last message -- it worked. 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 Saturday 10 October 2009 3:16 pm, Tom Lane wrote:
> Robert Paulsen writes:
> > On Saturday 10 October 2009 2:00 pm, Robert Paulsen wrote:
> >> So no default for id. What should it be?
> >> default nextval('vault_id_seq')
> >
> > Should have tried that before posting last message -- it worked. Thanks!
>
> So the next question is just what happened and whether you're missing
> anything else from the original database state ...
>
> regards, tom lane
Well, there was one other table with a sequence as a primary key and that was
lost also. Otherwise nothing else was wrong. I went through a dump/restore
cycle after fixing things and it all worked.
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?
--
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 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
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
