[SQL] Composite primary keys
I tried to throw some invalid SQL to PostgreSQL and found its reaction confusing: $ psql test psql (8.4beta2) Type "help" for help. test=# CREATE TABLE t1 ( test(# id serial NOT NULL, test(# name text NOT NULL, test(# PRIMARY KEY (id) test(# ); CREATE TABLE test=# CREATE TABLE t2 ( test(# id int NOT NULL REFERENCES t1, test(# language char(3) NULL, test(# txt text NOT NULL, test(# PRIMARY KEY (id, language) test(# ); CREATE TABLE Here's my first gripe: PostgreSQL accepts this silently instead of complaining. test=# INSERT INTO t1 (id, name) VALUES (1, 'text 1'); INSERT 0 1 test=# INSERT INTO t2 (id, language, txt) VALUES (2, NULL, 'text 1 no language'); ERROR: null value in column "language" violates not-null constraint And here's my second gripe: although PostgreSQL correctly rejects the INSERT it just has ignored my NULL specification. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Composite primary keys
Harald Fuchs writes: > I tried to throw some invalid SQL to PostgreSQL and found its reaction > confusing: > test(# language char(3) NULL, This is documented as being a no-op specification. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Composite primary keys
Howdy! When you say that pg accepts "this" silently instead of complaining what are you referring to exactly? First Insert? Why wouldn't it work after all ? What will happen is that when you try to insert a new record without specifying the id column you'll get an error informing that primary key constraint is being violated. But IMHO the first INSERT is legal SQL Best, Oliveiros - Original Message - From: "Harald Fuchs" To: Sent: Tuesday, June 23, 2009 4:14 PM Subject: [SQL] Composite primary keys I tried to throw some invalid SQL to PostgreSQL and found its reaction confusing: $ psql test psql (8.4beta2) Type "help" for help. test=# CREATE TABLE t1 ( test(# id serial NOT NULL, test(# name text NOT NULL, test(# PRIMARY KEY (id) test(# ); CREATE TABLE test=# CREATE TABLE t2 ( test(# id int NOT NULL REFERENCES t1, test(# language char(3) NULL, test(# txt text NOT NULL, test(# PRIMARY KEY (id, language) test(# ); CREATE TABLE Here's my first gripe: PostgreSQL accepts this silently instead of complaining. test=# INSERT INTO t1 (id, name) VALUES (1, 'text 1'); INSERT 0 1 test=# INSERT INTO t2 (id, language, txt) VALUES (2, NULL, 'text 1 no language'); ERROR: null value in column "language" violates not-null constraint And here's my second gripe: although PostgreSQL correctly rejects the INSERT it just has ignored my NULL specification. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Composite primary keys
On Tue, Jun 23, 2009 at 05:14:36PM +0200, Harald Fuchs wrote: > test=# CREATE TABLE t2 ( > test(# id int NOT NULL REFERENCES t1, > test(# language char(3) NULL, > test(# txt text NOT NULL, > test(# PRIMARY KEY (id, language) > test(# ); > CREATE TABLE > test=# INSERT INTO t2 (id, language, txt) VALUES (2, NULL, 'text 1 no > language'); > ERROR: null value in column "language" violates not-null constraint Primary keys are NOT NULL and UNIQUE. You can't have null values in a primary key. - Josh / eggyknap signature.asc Description: Digital signature
Re: [SQL] Composite primary keys
Joshua Tolley writes: > Primary keys are NOT NULL and UNIQUE. You can't have null values in a primary > key. On reflection I think the OP's beef is that we complain about this: regression=# create table t (f1 int null not null); ERROR: conflicting NULL/NOT NULL declarations for column "f1" of table "t" but not this: regression=# create table t (f1 int null primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE even though the implied NOT NULL is really a conflict. I think we could fix that case if we cared to. However, since the NULL clause is forgotten about after parsing, there isn't anything we could do to raise a complaint about doing it in two steps: regression=# create table t (f1 int null); CREATE TABLE regression=# alter table t add primary key(f1); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_pkey" for table "t" ALTER TABLE (barring remembering the NULL clause in the catalogs, which seems entirely silly). So I'm not sure how interesting it is to complain about the single-command case. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Client-side compression
Not sure if this belongs here or on the admin or performance list. Apologies if so. (And this may be a second posting as the first was from an un-registered account. Further apologies) My assumption is that any de/compression done by postgres would be server-side. We're considering minimizing bandwidth utilization by using client-side compression on a column value that will typically be multi-megabyte in size. We would use ALTER TABLE SET STORAGE EXTERNAL to prevent the server from un-necessary compression. Is this generally worthwhile? I haven't found any thread on the subject of client-side compress so any pointer more than welcome. Is there a great penalty for a query which delves into the value, given that the server will not be aware it's compressed? I assume we're pretty much on our own to prevent such actions (i.e. the app can never query against this column via sql). -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
