pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSome questions
8+ messages / 8 participants
[nested] [flat]
* Some questions
@ 1998-11-13 18:09 Vladimir Litovka <pgsqll@barnet.kharkov.ua>
0 siblings, 1 reply; 8+ messages in thread
From: Vladimir Litovka @ 1998-11-13 18:09 UTC (permalink / raw)
To: pgsql-sql
Hello!
There is table of character types in manual, where the 'text' type suggested
as the best choice. 'text' is variable length type and I think, that
fixed length's types (such as char(n)) will be faster, than variable
length's ones? Am I wrong?
The second question is more complex :) It about CREATE TABLE statement. Is
there difference between following statements?
create table aaa (i int2 UNIQUE);
and
create table aaa (i int2, CONSTRAINT aaa_i_key UNIQUE(i));
First case uses 'column constraint' and second - 'table constraint'. Manual
says: "A column constraint is an integrity constraint defined as part of a
column definition, and logically becomes a table constraint as soon as it
is created". Am I understand correctly - it is never mind where to define
(any) constraints - all of them does the same?
And last: what does it mean the following statement in the CREATE TABLE?
...
len INTERVAL HOUR TO MINUTE
...
Thank you :)
---
Vladimir Litovka <doka@webest.com>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Some questions
@ 1998-11-15 12:19 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Vladimir Litovka <pgsqll@barnet.kharkov.ua>
0 siblings, 0 replies; 8+ messages in thread
From: Herouth Maoz @ 1998-11-15 12:19 UTC (permalink / raw)
To: pgsql-sql
At 20:09 +0200 on 13/11/98, Vladimir Litovka wrote:
>
> There is table of character types in manual, where the 'text' type suggested
> as the best choice. 'text' is variable length type and I think, that
> fixed length's types (such as char(n)) will be faster, than variable
> length's ones? Am I wrong?
Basically, yes. But it depends also on the order of the field. Every field
after the first variable-length field will take longer to access,
regardless of whether in itself it is variable or not.
> The second question is more complex :) It about CREATE TABLE statement. Is
> there difference between following statements?
>
> create table aaa (i int2 UNIQUE);
> and
> create table aaa (i int2, CONSTRAINT aaa_i_key UNIQUE(i));
>
> First case uses 'column constraint' and second - 'table constraint'. Manual
> says: "A column constraint is an integrity constraint defined as part of a
> column definition, and logically becomes a table constraint as soon as it
> is created". Am I understand correctly - it is never mind where to define
> (any) constraints - all of them does the same?
Not exactly. A column constraint is a private case of a table constraint,
which is limited only to one column. You can say: "I want this column to be
unique, divisable by 10, and positive".
But a table constraint allows you to define a condition on more than one
column in the same table. For example, you can say "I want column A to be a
multiple of column B". One of the constraints I needed once said "Either
column A or column B may be null, but not both of them together".
So, of course, you may add the column constraints to the table constraints,
because they *are* table constraints. However, it is more elegant to put
them together with the column definition. This lets whoever reads the table
definition see immediately all the information there is to know about the
specific column - that it has type integer, default value 13, it may not be
null, is unique, and so forth.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 8+ messages in thread
* some questions
@ 2002-05-15 04:49 Ang Tun Chek <atchek@eware.com.my>
0 siblings, 3 replies; 8+ messages in thread
From: Ang Tun Chek @ 2002-05-15 04:49 UTC (permalink / raw)
To: pgsql-sql
hi
i am new to postresql and i need some help for some sql command
what is the sql command in postresql equal to "describe table"?
what is the sql command to list all tables in the database?
thank you
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: some questions
@ 2002-05-15 09:22 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Ang Tun Chek <atchek@eware.com.my>
2 siblings, 0 replies; 8+ messages in thread
From: Christopher Kings-Lynne @ 2002-05-15 09:22 UTC (permalink / raw)
To: Ang Tun Chek <atchek@eware.com.my>; pgsql-sql
> hi
> i am new to postresql and i need some help for some sql command
>
> what is the sql command in postresql equal to "describe table"?
>
> what is the sql command to list all tables in the database?
>
> thank you
OK, if you're using psql, you can go '\dt' to see all the tables or '\d
tablename' to see table info, but if you want to query them yourself, you'll
need to query the system catalogs:
http://www.au.postgresql.org/users-lounge/docs/7.2/postgres/catalogs.html
eg. SELECT * FROM pg_tables;
Chris
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: some questions
@ 2002-05-15 09:23 Adam Witney <awitney@sghms.ac.uk>
parent: Ang Tun Chek <atchek@eware.com.my>
2 siblings, 0 replies; 8+ messages in thread
From: Adam Witney @ 2002-05-15 09:23 UTC (permalink / raw)
To: Ang Tun Chek <atchek@eware.com.my>; pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: some questions
@ 2002-05-15 14:49 Roberto Mello <rmello@cc.usu.edu>
parent: Ang Tun Chek <atchek@eware.com.my>
2 siblings, 0 replies; 8+ messages in thread
From: Roberto Mello @ 2002-05-15 14:49 UTC (permalink / raw)
To: Ang Tun Chek <atchek@eware.com.my>; +Cc: pgsql-sql
On Wed, May 15, 2002 at 12:49:31PM +0800, Ang Tun Chek wrote:
> hi
> i am new to postresql and i need some help for some sql command
>
> what is the sql command in postresql equal to "describe table"?
>
> what is the sql command to list all tables in the database?
Following up on what has already been said, try also "\?" in psql. Also
read the documentation for psql and the User's Guide.
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Ex astra, scientia.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Some Questions
@ 2003-06-12 12:18 Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ludwig Lim @ 2003-06-12 12:18 UTC (permalink / raw)
To: pgsql-sql
Hi:
I would like to ask the following questions:
a) Are foreign key constraint triggers guaranteed to
execute first before any ordinary "BEFORE
INSERT/UPDATE/DELETE" trigger is executed? (This is
assuming that the foreign keys are declared as "NOT
DEFERRABLE")
b) Is "varchar" (without upper limit) the same as
"text"?
I do notice that when I create a view :
create view v_test as (select 'test'::varchar(10)
union select 'test1'::varchar(10));
a "\d v_test" on the psql prompt would always say
the only only column the view is of type "character
varying". Does a union of a varchar(n) column and a
another varchar(n) column would automatically cast it
to "character varying" (w/o upper limit) even if the
upper limit of the 2 varchar columns are the same?
thanks in advance,
ludwig lim
__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Some Questions
@ 2003-06-12 14:33 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2003-06-12 14:33 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
On Thu, 12 Jun 2003, Ludwig Lim wrote:
> I would like to ask the following questions:
> a) Are foreign key constraint triggers guaranteed to
> execute first before any ordinary "BEFORE
> INSERT/UPDATE/DELETE" trigger is executed? (This is
> assuming that the foreign keys are declared as "NOT
> DEFERRABLE")
No. Before triggers are going to get run before the action
occurs, the constraint triggers are in general going to run
after the action occurs (before triggers might change
information about the new row). It'll currently run in
the after triggers where it false in name order so you
can place after triggers before or after the constraint
trigger.
> b) Is "varchar" (without upper limit) the same as
> "text"?
Not quite, but it's close (there might still be issues
with mismatching set returning function returns and probably
some other similar cases).
> I do notice that when I create a view :
> create view v_test as (select 'test'::varchar(10)
> union select 'test1'::varchar(10));
>
> a "\d v_test" on the psql prompt would always say
> the only only column the view is of type "character
> varying". Does a union of a varchar(n) column and a
> another varchar(n) column would automatically cast it
> to "character varying" (w/o upper limit) even if the
> upper limit of the 2 varchar columns are the same?
As a side note, in 7.4, create view reports the type as
character varying and a create table as reports it as
character varying(10).
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-06-12 14:33 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-13 18:09 Some questions Vladimir Litovka <pgsqll@barnet.kharkov.ua>
1998-11-15 12:19 ` Herouth Maoz <herouth@oumail.openu.ac.il>
2002-05-15 04:49 some questions Ang Tun Chek <atchek@eware.com.my>
2002-05-15 09:22 ` Re: some questions Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-05-15 09:23 ` Re: some questions Adam Witney <awitney@sghms.ac.uk>
2002-05-15 14:49 ` Re: some questions Roberto Mello <rmello@cc.usu.edu>
2003-06-12 12:18 Some Questions Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-12 14:33 ` Re: Some Questions Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox