0

I am an intern at a company and started learning about psql, I am quite happy with my code so far to get the lines I want, but now I want to get a list (table?) with that Query over 5000 times with a variable that could be a Smallserial ? More information about the database, I need to join two tables, two see if one necessity is given and then I just need the first and last date of a log from a certain thing_id :

    (select key, value, table2.created_at 
from public.table1 inner join public.table2
on (table1.id = table2.thing_id)
where access_token is not null 
and (key = 'thing2')
and thing_id = 0000
order by key, table2.created_at asc
Limit 1)

UNION ALL

(select key, value, table2.created_at 
from public.table1 inner join public.table2
on (table1.id = table2.thing_id)
where access_token is not null 
and (key = 'thing2')
and thing_id = 0000
order by key, table2.created_at desc
Limit 1)

Now I would like thing_id = 0000 to change between 1-6000, is there a way to perform that with a script maybe? I would prefer to do it in pgAdmin4, but am open to suggestions.

I would really like to have that in one table, so that I can calculate the difference between those two lines for each individual thing_id.

Thank you if you want to help me.

Improve this question
4
  • and thing_id between 1 and 6000? Commented Nov 3, 2017 at 9:25
  • No because then it would say, "Transaction ID not found in the session." Commented Nov 3, 2017 at 9:39
  • That is not a valid Postgres error message. Where does that error message come from? Do you want to return rows even if that specific thing_id does not exist in the database? Commented Nov 3, 2017 at 9:44
  • Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. Commented Nov 3, 2017 at 9:45

3 Answers 3

Reset to default
1

a psql simple example:

-bash-4.2$ for varname in $(seq 1 5); do psql -c "select $varname as result_${varname}"; done
 result_1
----------
        1
(1 row)

 result_2
----------
        2
(1 row)

 result_3
----------
        3
(1 row)

 result_4
----------
        4
(1 row)

 result_5
----------
        5
(1 row)

also youmight want to read on https://www.postgresql.org/docs/current/static/app-psql.html#app-psql-interpolation

also for pgadmin https://www.pgadmin.org/docs/pgadmin3/1.22/pgscript.html specific options

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

2 Comments

Thank you for the reading options that you listed. Unfortunately I am unable to understand your psql code though.
I'm showing how you can pass absh variable to sql query to script 5 executions - from 1 to 5
1

I think something like this:

select key, value
from (select key, value, table2.created_at,
             row_number() over (partition by key order by t2.created_at asc) as seqnum_asc,
             row_number() over (partition by key order by t2.created_at desc) as seqnum_desc
      from public.table1 t1 inner join
           public.table2 t2
           on t1.id = t2.thing_id
      where access_token is not null and key = 'thing2' and
            thing_id between 0000 and 6000
     ) t
where seqnum_asc = 1 or seqnum_desc = 1;

Notes:

  • The use of 0000 is suspicious. If this is a string, you should enclose it in single quotes. If it is a number, then 0 suffices.
  • When writing queries with more than one table, qualify all the column names. This will help you write and debug your own queries.
  • Give your tables aliases that are abbreviations of the table names.
Improve this answer

6 Comments

t2.created_at asc) as seqnum_desc - did you mean t2.created_at desc?
Thank you very much for the help and the tips, I am unfortunately getting "ERROR: Syntaxerror at »asc« LINE 3" ? Do I need to flip asc and desc?
I think its missing an order by, should be (partition by key order by t2.created_at asc)
Now the code worked, but it still only gave me two lines of data. Whereas i was hopeing for 2 lines for each of the 6000 thing_id. I might not be able to express myself properly since I lack the knowledge of sql. But you gave me a lot to work with again, maybe I can figure it out. Thank you two very much.
Without seeing data its hard to advise, but the partition by clause in that function is determining the 'grouping', so right now its grouping only on key, you probably need to add thingid to the clause - (partition by key,thingid etc..) or depending on what is in key, just use (partition by thingid etc.)
|
0

if thing_id consists only of numbers:

select key, value, table2.created_at 
from public.table1 inner join public.table2
on (table1.id = table2.thing_id)
where access_token is not null 
and (key = 'thing2')
and thing_id::integer between 1 and 6000
order by key, table2.created_at asc
Improve this answer

1 Comment

Unfortunately this does not give me the first and last log, for each thing_id. But all logs available,from different dates. And using the Union, it says "Transaction ID not found"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.