1

I am using AWS RDS postgres instance version 10.3.

Completly stuck with the problem.

I have a table like:

create table social_message(
  social_profile_id int8 not null,
  body text,
  subject text,
  conference_id int8 not null,
  created_at timestamp not null default now(),
  sent_at timestamp default now(),
  contact_ids bigint[] not null default '{}'
);

Problem is with equality check with contact_ids column.

SQL query like this shows problem:

 select 
   contact_ids, 
   '{23673159}'::int8[],
   contact_ids = '{23673159}'::int8[],
   contact_ids @> '{23673159}'::int8[] and contact_ids <@ '{23673159}'::int8[]
 from social_message

Which returns (skipping not interested rows):

contact_ids |int8       |?column? |?column? |
------------|-----------|---------|---------|
{}          |{23673159} |false    |false    |
{23673161}  |{23673159} |false    |false    |
{23673159}  |{23673159} |false    |true     |

Question is how it is possible that = operator does not work? I mean that if A @> B and A <@ B is true, this should be A = B - but it is not!

I am using doing inserts with scala slick. It is doing such prepared statements:

insert into "social_message" ("social_profile_id","contact_ids","body","subject","conference_id","created_at","sent_at")  values (?,?,?,?,?,?,?)

Sample data for whole table:

social_profile_id |body   |subject |conference_id |created_at          |sent_at             |contact_ids |
------------------|-------|--------|--------------|--------------------|--------------------|------------|
42717             |sample |        |3313          |2018-08-14 10:45:18 |2018-08-14 10:45:18 |{}          |
42718             |sample |        |3313          |2018-08-14 10:52:48 |2018-08-14 10:52:48 |{}          |
42718             |sample |        |3313          |2018-08-14 11:07:08 |2018-08-14 11:07:08 |{}          |
42719             |sample |        |3313          |2018-08-14 11:09:06 |                    |{23673161}  |
42719             |sample |        |3313          |2018-08-14 11:09:29 |                    |{23673159}  |
42718             |sample |        |3313          |2018-08-14 12:06:32 |2018-08-14 12:06:32 |{}          |
42721             |sample |        |3313          |2018-08-14 12:15:36 |                    |{23673170}  |
42721             |sample |        |3313          |2018-08-14 12:16:29 |                    |{23673169}  |
42718             |sample |        |3313          |2018-08-14 12:43:13 |2018-08-14 12:43:13 |{}          |
42722             |sample |        |3313          |2018-08-14 12:45:56 |                    |{23673176}  |
42722             |sample |        |3313          |2018-08-14 12:47:24 |                    |{23673175}  |
42723             |sample |        |3313          |2018-08-14 13:26:28 |                    |{23673178}  |
42720             |sample |        |3313          |2018-08-14 11:11:35 |2018-08-14 17:29:29 |{23673165}  |
42724             |sample |        |3313          |2018-08-14 15:09:12 |2018-08-14 15:09:12 |{}          |
42725             |sample |        |3313          |2018-08-14 15:14:41 |2018-08-14 15:14:44 |{23673182}  |
42726             |sample |        |3313          |2018-08-14 15:17:17 |2018-08-14 15:17:18 |{23673185}  |
42728             |sample |        |3313          |2018-08-14 15:19:19 |2018-08-14 15:19:20 |{23673188}  |

I localized problem to jdbc driver. When i do manual insert with psql select works like a charm. But when insert happened within scala code it does not selected.

It is still not clear how it is possible to do.

Thank you

6
  • 1
    None of your contact ids have more than one element. why are you using arrays? Aug 14, 2018 at 15:53
  • The point of the CREATE TABLE, INSERT statements and desired result is so we can see what you expect. It's not clear why "= operator does not work" or what "does not work" means in that context, and it's not clear what you want to happen when it works? Aug 14, 2018 at 15:55
  • What about contact_ids = ARRAY[23673159] ? (or ARRAY[23673159]::int8[]). Of course as soon as you have multiple values you need to remember that arrays are not sets, so the order of elements count. Have a look at the related: stackoverflow.com/questions/1647385/… Aug 14, 2018 at 16:02
  • 2
    I cannot reproduce the problem. See: dbfiddle.uk/… It seems very odd. Did you mess with the = operator in your DB? I added a query to the fiddle showing possibly relevant operators. Do you see the same in your DB? Which point release of pg 10? Aug 14, 2018 at 18:56
  • @ErwinBrandstetter i did not found anything in pg_operator (comparing to dbfiddle). I added version information on top. Aug 15, 2018 at 6:38

0

Your Answer

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

Browse other questions tagged or ask your own question.