0

How would i write a query so that i can generate myself a column with some specific numbers? I would prefer not to be forced to create a table and import from excel, nor insert values (as i have many numbers to put into that column, and i'll have to put lots of parantheses around those values) See picture for reference. Now imagine i have 1000 numbers. I would like to have some query where i could just copy-paste over those numbers. I tried:

  • select (231,356,...) as companyid ... obviously this did not provide the desired result.
  • i succeded with a temp table and insert into .. but i had to go like VALUES(231),(356),... ... which is nasty

enter image description here

santee
  • 29
  • 6
  • The number of items and parentheses doesn't matter. Use a text editor of your choise to perform a find-and-replace: `$([\n]+)^` -> `($1),` and use `values` clause – astentx Oct 06 '22 at 08:17
  • My numbers are not random. Those numbers have a meaning. I want to have an SQL statement that would provide me the output exactly as in the picture – santee Oct 06 '22 at 08:19
  • How do you relate this companyid with the correct row for the rest of your data? – Lennart - Slava Ukraini Oct 06 '22 at 08:20
  • If those numbers are in Excel, it should be easy to make Excel generate the `values (231), (356) ...` clause –  Oct 06 '22 at 08:23
  • I will left join this "column" with a table. My main goal is: I receive 1000 companyids, some are present in a table, some are not. I want to keep all 1000 ids. As for information I will add sutff in SELECT to populate with data for the ids found in the table AND NULL values for those which are not. If I go with SELECT * FROM table WHERE companyid (list_of_1000) ... this statement will only return info for the ids it can find in the table. But i also need to know what ids where not in that table (and NULL values are just fine) – santee Oct 06 '22 at 08:23

1 Answers1

1

For a list of numbers, from some other source, you can use UNNEST():

SELECT  UNNEST('{1,2,4,6,54,3,900}'::int[]) AS id;

Just replace "1,2,4,6,54,3,900" with your input, and use the comma to separate the values.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135