Thread: list of data types

On Thu, 1 Feb 2001, Derek wrote:
> I've downloaded and printed off al the pgsql documentation from the greatbridge website,
> but can't find a comprehensive list of data types for pgsql columns.
>
> Can anyone point me in the right direction?
>
Try chapter 3 "Datatypes" from the users guide. You can read it from
http://postgresql.org/users-lounge/docs/7.0/user/datatype.htm. For
a quick list type \dT in psql.

- Einar

Im trying to do an outerjoin of two tables. The second one might be
empty. Normally I would use a query like:

CREATE TABLE a ( id INTEGER );
CREATE TABLE b ( id INTEGER );

SELECT * FROM a,b
WHERE a.id=b.id
UNION ALL
SELECT * FROM a,b
WHERE a.id NOT IN (b.id)
;

If the seconf table is empty the result is null, because the cartesian
product of table and null is null. What I want is to include a condition
that if b has no rows then just add null for the value of b ie.
SELECT *,NULL FROM a; How can I implement this?

- Einar Karttunen

> SELECT *,NULL FROM a; How can I implement this?

Try 7.1beta.  It supports all the joins.  You'd be able to just do:

select * from a left join b using (id);

--
------------------------------------------------
-- Robert B. Easter
-- reaster@comptechnews.com
-- http://www.comptechnews.com/~reaster/
-- Linux Support: http://www.comptechserv.com/
------------------------------------------------

> Im trying to do an outerjoin of two tables. The second one might be
> empty. Normally I would use a query like:
>
> CREATE TABLE a ( id INTEGER );
> CREATE TABLE b ( id INTEGER );
>
> SELECT * FROM a,b
> WHERE a.id=b.id
> UNION ALL
> SELECT * FROM a,b
> WHERE a.id NOT IN (b.id)
> ;
>
> If the seconf table is empty the result is null, because the cartesian
> product of table and null is null. What I want is to include a condition
> that if b has no rows then just add null for the value of b ie.
> SELECT *,NULL FROM a; How can I implement this?

There probably are a couple of ways of doing. One is for both queries
to add an "AS" field which return a constant. The constants would be
different for each. This way you can look for those values to know if you
have rows from B or not.

test=# create table t1 (f1 integer);
CREATE
test=# create table t2 (f1 integer);
CREATE

test=# insert into t1 values (1);
INSERT 21565 1

test=# insert into t2 values (2);
INSERT 21566 1

test=# select f1, 't1' as f2 from t1
test-# union
test-# select f1, 't2' as f2 from t2;
 f1 | f2
----+----
  1 | t1
  2 | t2
(2 rows)


Does that help?