Re: case sensitivity for tables, columns, and constraint names

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

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

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

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

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

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

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

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

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)

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

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.
:)