Skip to main content
Database Administrators

Return to Question

deleted 1 character in body
Source Link
pietrop
pietrop
  • 756
  • 1
  • 7
  • 16

I have many tables of the same length (number of rows) that wherewere previously ordered by some columns as they were being written on disk.

I have many tables of the same length (number of rows) that where previously ordered by some columns as they were being written on disk.

I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk.

added 926 characters in body
Source Link
pietrop
pietrop
  • 756
  • 1
  • 7
  • 16
SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3
        ...

I'm using PostgreSQL 9.4.5.

Data Example

Input tables:

t1.id | t1.value
----------------
    1 |       a
    2 |       a
    3 |       b
    4 |       a
    5 |       c
    6 |       a

t2.id | t2.value
----------------
    1 |       g
    2 |       g
    3 |       h
    4 |       g
    5 |       o
    6 |       l

t3.id | t3.value
----------------
    1 |       e
    2 |       e
    3 |       e
    4 |       e
    5 |       q
    6 |       e

Expected result:

t1.id | t1.value | t2.value | t3.value
--------------------------------------
    1 |       a  |        g |       e
    2 |       a  |        g |       e
    3 |       b  |        h |       e
    4 |       a  |        g |       e
    5 |       c  |        o |       q
    6 |       a  |        l |       e

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3
        ...

I'm using PostgreSQL 9.4.5.

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3 ...

I'm using PostgreSQL 9.4.5.

Data Example

Input tables:

t1.id | t1.value
----------------
    1 |       a
    2 |       a
    3 |       b
    4 |       a
    5 |       c
    6 |       a

t2.id | t2.value
----------------
    1 |       g
    2 |       g
    3 |       h
    4 |       g
    5 |       o
    6 |       l

t3.id | t3.value
----------------
    1 |       e
    2 |       e
    3 |       e
    4 |       e
    5 |       q
    6 |       e

Expected result:

t1.id | t1.value | t2.value | t3.value
--------------------------------------
    1 |       a  |        g |       e
    2 |       a  |        g |       e
    3 |       b  |        h |       e
    4 |       a  |        g |       e
    5 |       c  |        o |       q
    6 |       a  |        l |       e
Source Link
pietrop
pietrop
  • 756
  • 1
  • 7
  • 16

PostgreSQL - Simple column binding

I have many tables of the same length (number of rows) that where previously ordered by some columns as they were being written on disk.

My SELECT is of the form:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 LEFT JOIN t2 ON t1.id=t2.id
        LEFT JOIN t3 ON t1.id=t3.id
        ...

I know by how these tables were populated that this result corresponds exactly to simple column binding, so that ON t1.id=t2.id and ON t1.id=t3.id can be omitted. The problem is, as far as I know, RDBMSs can operate only projections and row binding. Column binding isn't a typical operation.

So the question is the following: is it possible to bind columns "as they are" without specifying any join criteria? For example, the following query, which is syntactically wrong, should explain what I mean:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3
        ...

I'm using PostgreSQL 9.4.5.