Обсуждение: 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. http://www.at4.net/phorum 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)
> > 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)