Closed Suor closed 9 years ago
@Suor: Sorry for the delay responding.
Do you mean selecting from a set of literal values, like SELECT * FROM ('test1', 'test2', 'test3')
? I haven't been able to find anything like that in the SQL92 spec or the sqlite docs. What database are you using?
Or are you referring to a subquery, like this: SELECT * FROM (SELECT * FROM sqlite_master)
?
I am using PostgreSQL. That's the kind of query I was trying to make:
INSERT INTO link (job_id, url, source_id, internal)
SELECT job_id::int as job_id, url::text as url,
source_id::int as source_id, internal::boolean as internal
FROM (VALUES
($1, $3, $2, $4),
($1, $5, $2, $6),
...
) as tmp (job_id, url, source_id, internal)
WHERE NOT EXISTS
(SELECT 1 FROM link l where l.job_id = tmp.job_id::int and l.url = tmp.url);
It should also work without INSERT
. So both selecting from values and using a subquery.
@Suor: I see. It looks like this postgres functionality is documented here: http://www.postgresql.org/docs/9.3/static/queries-values.html.
This isn't supported by SQLite and I'm pretty sure it's not in the SQL-92 standard, which is the base of functionality that I'm committed to supporting in SQLBricks. You're welcome to add this functionality to a postgres-specific extension for SQLBricks. If you find that you need anything added or changed in the core SQLBricks library in order to implement this functionality in an extension, I would be happy to make whatever changes are necessary so that dialect-specific extensions can implement this kind of thing.
@Suor: Thank you for bringing this to my attention & documenting this, I didn't know you could use a VALUES command in a SELECT statement in postgres.
There is currently no way to construct such query.