Re: Recursive Arrays 101

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