Thread: datatype questions

datatype questions

From
Brian
Date:
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 -----------------------------/



Re: [GENERAL] datatype questions

From
Peter Mount
Date:
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


Re: [GENERAL] datatype questions

From
Aleksey Dashevsky
Date:

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.


Re: [GENERAL] datatype questions

From
"Antonio Garcia Mari"
Date:
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)


Re: [GENERAL] datatype questions

From
Brian
Date:
>
> 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 -----------------------------/



Re: [GENERAL] datatype questions

From
Bruce Momjian
Date:
>
> >
> > 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)