How to create an empty anonymous table in Postgres?

Question

In Postgres if I want to create an "anonymous table" (i.e. a temporary query based on data not in the database) I can use VALUES, for example:

select * from (values (1, 'Hello world'), (100, 'Another row')) as foo (mycol1, mycol2);

But how can I create an anonymous table with no rows? (This is for a code generator, so the question isn't quite as odd as it sounds!). The following does not work

select * from (values  ) as foo (mycol1, mycol2);

because I get

ERROR:  syntax error at or near ")"
LINE 1: select * from (values  ) as foo (mycol1, mycol2);
                               ^

I know a work around

select * from (values (NULL, NULL)) as foo (mycol1, mycol2) where mycol1 is not NULL;

but is there a better or "more official" way?

(I would also be interested to know if it is possible to create a table with no columns!)

What do you want to do with "anonymous table"? The ability to create such table looks like a bug. — Ihor Romanchenko
– Ihor Romanchenko, Commented Sep 18, 2013 at 15:49
@Denis, what I'm asking is whether there's a nicer way to do: select * from (values (NULL, NULL)) as foo (mycol1, mycol2) where mycol1 is not NULL. — Tom Ellis
– Tom Ellis, Commented Sep 18, 2013 at 15:55
@IgorRomanchenko, by anonymous table I mean a temporary query based on data not in the database, such as the one returned from my first select statement. — Tom Ellis
– Tom Ellis, Commented Sep 18, 2013 at 15:56

roman · Accepted Answer · 2013-09-18 16:52:33Z

6

I think you can do something like this:

select null::text as a, null::int as b
limit 0

answered Sep 18, 2013 at 16:52

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Ellis Over a year ago

I like it! My preferred answer so far. Syntactically very clean.

Quassnoi · Accepted Answer · 2013-09-18 16:01:00Z

1

SELECT  *
FROM    generate_series(0, -1)

answered Sep 18, 2013 at 16:01

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

3 Comments

Tom Ellis Over a year ago

That certainly gives me a query returning zero rows! But can I make it have an arbitary number of columns with arbitrary types?

Ihor Romanchenko Over a year ago

@TomEllis Use SELECT null::number, null::varchar ... FROM generate_series(0, -1)

Tom Ellis Over a year ago

Ah yes, that works. And to get column names I can do SELECT null::int as foo, null::varchar as bar FROM generate_series(0, -1)

Collectives™ on Stack Overflow

How to create an empty anonymous table in Postgres?

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related