Lists: | pgsql-sql |
---|
From: | "Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | case sensitivity for tables, columns, and constraint names |
Date: | 2007-12-11 17:05:25 |
Message-ID: | 863ECFDF04C8804ABDF3559D87F157C638E5D9@MAGPTCPEXC02.na.mag-ias.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
I see that I can create a table with a mixed case name as long as the name is wrapped in quotes. Is there a setting to allow upper case and mixed case names for database tables, fields, etc, without having to wrap each in quotes?
Thanks,
SteveE
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: case sensitivity for tables, columns, and constraint names |
Date: | 2007-12-11 17:24:12 |
Message-ID: | 20071211172412.GA14009@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
am Tue, dem 11.12.2007, um 11:05:25 -0600 mailte Ertel, Steve folgendes:
> 
> I see that I can create a table with a mixed case name as long as the name is
> wrapped in quotes. Is there a setting to allow upper case and mixed case names
> for database tables, fields, etc, without having to wrap each in quotes?
No.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From: | Andrew Sullivan <ajs(at)crankycanuck(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: case sensitivity for tables, columns, and constraint names |
Date: | 2007-12-11 17:30:37 |
Message-ID: | 20071211173037.GL27409@crankycanuck.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
On Tue, Dec 11, 2007 at 11:05:25AM -0600, Ertel, Steve wrote:
> is wrapped in quotes. Is there a setting to allow upper case and mixed
> case names for database tables, fields, etc, without having to wrap each
> in quotes?
No, sorry. The always-one-case rule for unquoted identifiers is ANSI
conformant. As it happens, the ANSI rule also makes everything ALLCAPS, but
PostgreSQL doesn't go that way (for historical reasons).
A
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: case sensitivity for tables, columns, and constraint names |
Date: | 2007-12-11 17:35:06 |
Message-ID: | 475ECA4A.5060300@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Ertel, Steve wrote:
> I see that I can create a table with a mixed case name as long as the
> name is wrapped in quotes. Is there a setting to allow upper case
> and mixed case names for database tables, fields, etc, without having
> to wrap each in quotes?
No, SQL defines identifiers as case-insensitive.
PG is unusual in that left alone it folds names to lower-case rather
than upper, but all SQL databases (afaik) are case-insensitive by default.
--
Richard Huxton
Archonet Ltd
From: | PostgreSQL Admin <postgres(at)productivitymedia(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Foreign Key for multi PK or design question |
Date: | 2007-12-11 18:20:52 |
Message-ID: | 475ED504.7080006@productivitymedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
I have a table in which people will have a number of questions to
answer. I want those pk to be placed in my user table. So if a user
answers three question I want those 3 pk's in the user table (fk).
What should I be doing?
Thanks in advance,
J
From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | PostgreSQL Admin <postgres(at)productivitymedia(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Foreign Key for multi PK or design question |
Date: | 2007-12-11 18:35:22 |
Message-ID: | 7B8F0792-3CDE-4C9C-B4E3-50E550D769C5@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
On Dec 11, 2007, at 12:20 PM, PostgreSQL Admin wrote:
> I have a table in which people will have a number of questions to
> answer. I want those pk to be placed in my user table. So if a user
> answers three question I want those 3 pk's in the user table (fk).
> What should I be doing?
You're going to have to give a more concrete example of what it is
you're trying to do, i.e what those questions are, table structures,
etc.
Erik Jones
Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Foreign Key for multi PK or design question |
Date: | 2007-12-11 18:53:30 |
Message-ID: | 20071211185330.GA16159@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
am Tue, dem 11.12.2007, um 13:20:52 -0500 mailte PostgreSQL Admin folgendes:
> I have a table in which people will have a number of questions to
> answer. I want those pk to be placed in my user table. So if a user
> answers three question I want those 3 pk's in the user table (fk).
> What should I be doing?
Explain your problem a little bit more. We need your table-design, for
instance. Nobody can help you without more informations. Maybe you are
searching for current_user, but i'm not sure.
Btw.:
Please don't hijack other threads, this problems was a topic on this¹
list today. Your email contains a References: - Header, and you are
using Thunderbird. I think, you can understand me what i mean.
If yo want to create a new topic, create a *new* mail and don't answer
to an existing mail by changing the subject.
¹ it was the [General]-List, sorry
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From: | PostgreSQL Admin <postgres(at)productivitymedia(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Foreign Key for multi PK or design question |
Date: | 2007-12-11 19:12:38 |
Message-ID: | 475EE126.5030302@productivitymedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
This is my layout so far:
CREATE TABLE users (
id serial NOT NULL,
--question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED##
);
CREATE TABLE questions (
id serial NOT NULL,
questions varchar(450) NOT NULL
);
CREATE TABLE answers (
id serial NOT NULL,
question_id int REFERENCES questions(id) ON DELETE CASCADE,
user_id int REFERENCES users(id) ON DELETE CASCADE,
answer varchar(450) NOT NULL,
created timestamptz NOT NULL
);
Originally I wanted to have a foreign key that would be the pk of the
question table. So if the user answered Q2, 5 and 6 - the user.fk would
store values 2,5,6 - but I have passed most of logic to the answer table.
Does this look correct? or most efficient?
J
From: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
---|---|
To: | PostgreSQL Admin <postgres(at)productivitymedia(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Foreign Key for multi PK or design question |
Date: | 2007-12-11 19:42:46 |
Message-ID: | 20071211194246.GK10710@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
PostgreSQL Admin wrote:
> This is my layout so far:
>
> CREATE TABLE users (
> id serial NOT NULL,
> --question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED##
> );
>
> CREATE TABLE questions (
> id serial NOT NULL,
> questions varchar(450) NOT NULL
> );
>
> CREATE TABLE answers (
> id serial NOT NULL,
> question_id int REFERENCES questions(id) ON DELETE CASCADE,
> user_id int REFERENCES users(id) ON DELETE CASCADE,
> answer varchar(450) NOT NULL,
> created timestamptz NOT NULL
> );
>
> Originally I wanted to have a foreign key that would be the pk of the
> question table. So if the user answered Q2, 5 and 6 - the user.fk would
> store values 2,5,6 - but I have passed most of logic to the answer table.
That would have made no sense.
> Does this look correct? or most efficient?
Yeah it seems sane, however you have forgotten to add NOT NULL to the FK
fields.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"No me acuerdo, pero no es cierto. No es cierto, y si fuera cierto,
no me acuerdo." (Augusto Pinochet a una corte de justicia)
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Foreign Key for multi PK or design question |
Date: | 2007-12-11 19:48:13 |
Message-ID: | 20071211194813.GA17301@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
am Tue, dem 11.12.2007, um 14:12:38 -0500 mailte PostgreSQL Admin folgendes:
> This is my layout so far:
> [ table-layout]
> ...
> Does this look correct?
Yes, why not? Do you have problems? Which? I can't see problems, it's a
normalized design IMHO.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From: | PostgreSQL Admin <postgres(at)productivitymedia(dot)com> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Foreign Key for multi PK or design question |
Date: | 2007-12-11 20:04:35 |
Message-ID: | 475EED53.9040001@productivitymedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
No problems with the design - I was not thinking with the DB hat on at
first. I have been working on clustering for a while... just adjusting.
Thanks everyone.
:)