Re: Recursive Arrays 101 - Mailing list pgsql-general

On 27/10/15 10:26, David Blomstrom wrote:
> Here's what it looks like now:
>
> CREATE TABLE public.gz_life_mammals
> (
>   id integer NOT NULL,
>   taxon text NOT NULL,
>   parent text NOT NULL,
>   slug text,
>   namecommon text,
>   plural text,
>   extinct smallint NOT NULL,
>   rank smallint NOT NULL,
>   key smallint NOT NULL,
>   CONSTRAINT "Primary Key" PRIMARY KEY (id),
>   CONSTRAINT "Unique Key" UNIQUE (taxon)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE public.gz_life_mammals
>   OWNER TO postgres;
>
> * * * * *
>
> I don't even have a clue what OIDS=FALSE means; I haven't read up on
> it yet. It's just there by default. I haven't figured out how to
> change the NULL value for any columns, other than toggle back and
> forth between NULL and NOT NULL.
>
> To assign a user, would I just ask it to associate a table with my
> username? Can I do that with pgAdmin3?
>
> Thanks.
Hi David,

Constructing SQL in an editor and executing the SQL script using psql is
often a lot easier than using pgadmin3, and gives you far more control!
I use both, but more often use psql.

 From the postgres user and using psql, you can create a user & database
like:

CREATE ROLE gavin
LOGIN
CREATEDB;

CREATE DATABASE gavin
OWNER gavin;


Obviously, you can create a database with a different name for the same
user.  Just that the above means that if you call up psql from a
terminal of that user, you don't need to explicitly tell it what
database to use.

I created an SQL script create_table.sql (usually better to have a more
descriptive name!) in an editor:

CREATE TABLE public.gz_life_mammals
(
   id            int PRIMARY KEY,
   taxon         text UNIQUE NOT NULL,
   parent        text NOT NULL,
   slug          text,
   name_common   text,
   plural        text,
   extinct       smallint NOT NULL,
   rank          smallint NOT NULL,
   key           smallint NOT NULL
);

Here is a session where I create the table (I created the terminal in
the same directory as the SQL script, you can also simply cd to the
relevant directory before executing psql):
$ psql
psql (9.4.4)
Type "help" for help.

gavin=> \i create_table.sql
CREATE TABLE
gavin=> \q
$

You might be able to do all the above using pgadmin3...


Cheers,
Gavin