datatype questions
I am trying to convert a table into PostgresSQL from MySQL, and had a few
questions:
CREATE TABLE forum (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name varchar(30),
email varchar(100),
topic varchar(50),
body blob,
host varchar(50),
thread int(11) DEFAULT '0' NOT NULL,
datestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
KEY datestamp (datestamp),
PRIMARY KEY (id),
KEY thread (thread)
);
1. Where in the documentation can you look to find what "modifiers" are
supported by postgres after the declaration of the field. such as NOT
NULL, PRIMARY KEY, KEY, DEFAULT, UNIQUE, etc. I call them modifiers but
what are they really called?
2. Does postgres support anything even like a "blob" data type?
3. Does Postgres support something like "auto_increment".
Once again, sorry if these questions are obvious. If I knew where to look
in the documentation for what can be in a field declaration, I would have
looked there.
In regards to the part that looks like this:
KEY datestamp (datestamp),
PRIMARY KEY (id),
KEY thread (thread)
Can you declare like that in your Postgres tables, or do you have to do
like:
CREATE INDEX on forum idx1 (datestamp);
CREATE INDEX on forum idx2 (id);
CREATE INDEX on forum idx3 (thread);
If so, then how would you say "primary"? I know some databases such as
mSQL did away with "PRIMARY KEY", and you just make indices instead, such
as above, none of which are "primary".
Thank you for your help.
Brian
/-------------------------- signal@shreve.net -----------------------------\
| Brian Feeny | USR TC Hubs | ShreveNet Inc. (318)222-2638 |
| Network Administrator | Perl, Linux | Web hosting, online stores, |
| ShreveNet Inc. | USR Pilot | Dial-Up 14.4-56k, ISDN & LANs |
| 89 CRX DX w/MPFI, lots of |-=*:Quake:*=-| http://www.shreve.net/ |
| mods/Homepage coming soon |LordSignal/SN| Quake server: 208.206.76.47 |
\-------------------------- 318-222-2638 x109 -----------------------------/
On Mon, 22 Jun 1998, Brian wrote:
I am trying to convert a table into PostgresSQL from MySQL, and had a few
questions:
[snip]
2. Does postgres support anything even like a "blob" data type?
yes and no. I posted an experimental blob type (actually its lo) just over
a week ago, and it should be in the src/contrib/lo directory of the CVS
source. This handles (in part) orphaning of large objects, which is a
problem with ODBC & JDBC.
Other than that, in postgres, we use oid as the type, which then refers to
a large object.
3. Does Postgres support something like "auto_increment".
We have sequences which provide this facility.
--
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
************ Someday I may rebuild this signature completely ;-) ************
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
On Mon, 22 Jun 1998, Brian wrote:
I am trying to convert a table into PostgresSQL from MySQL, and had a few
questions:CREATE TABLE forum (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name varchar(30),
email varchar(100),
topic varchar(50),
body blob,
host varchar(50),
thread int(11) DEFAULT '0' NOT NULL,
datestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
KEY datestamp (datestamp),
PRIMARY KEY (id),
KEY thread (thread)
);1. Where in the documentation can you look to find what "modifiers" are
supported by postgres after the declaration of the field. such as NOT
NULL, PRIMARY KEY, KEY, DEFAULT, UNIQUE, etc. I call them modifiers but
what are they really called?
Please, check PostgreSQL manual pages.
Also, there are HTMLized version of mans on the Web:
http://www.postgresql.org/docs/man/
What do you exactly need: create_table(l) , create_sequence(l) and
create_index(l) pages.
Aleksey.
Ok, you're porting phorum to postgres. I've done the work before.
Check the download page to get the sources.
And this is the sql for the table you want to create:
CREATE SEQUENCE forum_id_s INCREMENT 1 START 1;
CREATE TABLE forum (
id int4 DEFAULT nextval('forum_id_s') NOT NULL PRIMARY KEY,
name varchar(30),
email varchar(100),
topic varchar(50),
body text,
host varchar(50),
thread int4 DEFAULT 0 NOT NULL,
datestamp datetime DEFAULT 'now()' NOT NULL
);
On Mon, 22 Jun 1998, Brian wrote:
I am trying to convert a table into PostgresSQL from MySQL, and had a few
questions:CREATE TABLE forum (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name varchar(30),
email varchar(100),
topic varchar(50),
body blob,
host varchar(50),
thread int(11) DEFAULT '0' NOT NULL,
datestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
KEY datestamp (datestamp),
PRIMARY KEY (id),
KEY thread (thread)
);1. Where in the documentation can you look to find what "modifiers" are
supported by postgres after the declaration of the field. such as NOT
NULL, PRIMARY KEY, KEY, DEFAULT, UNIQUE, etc. I call them modifiers but
what are they really called?Please, check PostgreSQL manual pages.
Also, there are HTMLized version of mans on the Web:
http://www.postgresql.org/docs/man/
What do you exactly need: create_table(l) , create_sequence(l) and
create_index(l) pages.Aleksey.
Antonio Garcia Mari
Mallorca (Spain)
On Wed, 24 Jun 1998, Antonio Garcia Mari wrote:
Ok, you're porting phorum to postgres. I've done the work before.
well I was going to but...............
since you have already done the work :)
Check the download page to get the sources.
Brian Moon caught wind what I was going to do, and told me it would be
wise to holdoff since a database independent version is going to be out
soon.
Thanks for the help.
And this is the sql for the table you want to create:
CREATE SEQUENCE forum_id_s INCREMENT 1 START 1;
CREATE TABLE forum (
id int4 DEFAULT nextval('forum_id_s') NOT NULL PRIMARY KEY,
name varchar(30),
email varchar(100),
topic varchar(50),
body text,
host varchar(50),
thread int4 DEFAULT 0 NOT NULL,
datestamp datetime DEFAULT 'now()' NOT NULL
);On Mon, 22 Jun 1998, Brian wrote:
I am trying to convert a table into PostgresSQL from MySQL, and had a few
questions:CREATE TABLE forum (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name varchar(30),
email varchar(100),
topic varchar(50),
body blob,
host varchar(50),
thread int(11) DEFAULT '0' NOT NULL,
datestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
KEY datestamp (datestamp),
PRIMARY KEY (id),
KEY thread (thread)
);1. Where in the documentation can you look to find what "modifiers" are
supported by postgres after the declaration of the field. such as NOT
NULL, PRIMARY KEY, KEY, DEFAULT, UNIQUE, etc. I call them modifiers but
what are they really called?Please, check PostgreSQL manual pages.
Also, there are HTMLized version of mans on the Web:
http://www.postgresql.org/docs/man/
What do you exactly need: create_table(l) , create_sequence(l) and
create_index(l) pages.Aleksey.
Antonio Garcia Mari
Mallorca (Spain)
/-------------------------- signal@shreve.net -----------------------------\
| Brian Feeny | USR TC Hubs | ShreveNet Inc. (318)222-2638 |
| Network Administrator | Perl, Linux | Web hosting, online stores, |
| ShreveNet Inc. | USR Pilot | Dial-Up 14.4-56k, ISDN & LANs |
| 89 CRX DX w/MPFI, lots of |-=*:Quake:*=-| http://www.shreve.net/ |
| mods/Homepage coming soon |LordSignal/SN| Quake server: 208.206.76.47 |
\-------------------------- 318-222-2638 x109 -----------------------------/
Import Notes
Reply to msg id not found: 199806241005.FAA02280@mercury.shreve.net | Resolved by subject fallback
Please, check PostgreSQL manual pages.
Also, there are HTMLized version of mans on the Web:
http://www.postgresql.org/docs/man/
What do you exactly need: create_table(l) , create_sequence(l) and
create_index(l) pages.
Thanks, that's just what I needed, is to know which man pages to look
in........>I won't even tell you how long I searched for "table.1", which
is referenced by many pages, but non-existant :)
Brian
Aleksey.
/-------------------------- signal@shreve.net -----------------------------\
| Brian Feeny | USR TC Hubs | ShreveNet Inc. (318)222-2638 |
| Network Administrator | Perl, Linux | Web hosting, online stores, |
| ShreveNet Inc. | USR Pilot | Dial-Up 14.4-56k, ISDN & LANs |
| 89 CRX DX w/MPFI, lots of |-=*:Quake:*=-| http://www.shreve.net/ |
| mods/Homepage coming soon |LordSignal/SN| Quake server: 208.206.76.47 |
\-------------------------- 318-222-2638 x109 -----------------------------/
Please, check PostgreSQL manual pages.
Also, there are HTMLized version of mans on the Web:
http://www.postgresql.org/docs/man/
What do you exactly need: create_table(l) , create_sequence(l) and
create_index(l) pages.Thanks, that's just what I needed, is to know which man pages to look
in........>I won't even tell you how long I searched for "table.1", which
is referenced by many pages, but non-existant :)
We are fixing those now. They underscores were missing.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)